obce_5

cd /root/t-oceanbase-antman
vi 3ecs-3ob.yaml

user:
username: root
password: Ct1Wc9Jd3P
oceanbase:
servers:
- name: ob1
ip: 172.16.1.27
- name: ob2
ip: 172.16.1.28
- name: ob3
ip: 172.16.1.29

obproxy:
depends:
- oceanbase
servers:
- name: obproxy1
ip: 172.16.1.27
- name: obproxy2
ip: 172.16.1.28
- name: obproxy3
ip: 172.16.1.29
global:
rs_list: 172.16.1.27:2881;172.16.1.28:2881;172.16.1.29:2881

开始部署
obd cluster deploy obce_test -c 3ecs-3ob.yaml

启动 obce_test 集群
obd cluster start obce_test
#销毁
obd cluster stop obce_test
obd cluster destroy obce_test

obclient -uroot@sys -h127.0.0.1 -P2881
#新建资源规格
create resource unit u1_ora max_cpu=1,min_cpu=1,max_memory='2G',min_memory='2G',max_iops=128,max_disk_size='10G',max_session_num=100;

#新建资源池
create resource pool pool_ora unit='u1_ora',unit_num=1,zone_list=('zone1','zone2','zone3');
#新建 oracle 租户
CREATE TENANT IF NOT EXISTS ob_pay charset='utf8mb4', replica_num=1,zone_list=('zone1','zone2','zone3'), resource_pool_list=('pool_ora') SET ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';

obclient -usys@ob_pay -h127.0.0.1 -P2881
#新建 tpcc 用户，授予 dba 权限
create user tpcc identified by obce_test;
grant dba to tpcc;

obclient -utpcc@ob_pay -h127.0.0.1 -P2881 -p

create table chap41 (c1 int primary key, c2 int, c3 varchar(3));

insert into chap41 select level, mod(level,10) ,'001' from dual connect by level
<=10000;
commit;

insert into chap41 values (99999,999,'999');
commit;

explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= '999' \G

SELECT * FROM oceanbase.gv$tenant where tenant_name='ob_pay';

+-----------+-------------+-------------------+--------------+----------------+------+-----------+---------------------------------------------+
| tenant_id | tenant_name | zone_list | primary_zone | collation_type | info | read_only | locality |
+-----------+-------------+-------------------+--------------+----------------+------+-----------+---------------------------------------------+
| 1001 | ob_pay | zone1;zone2;zone3 | RANDOM | 0 | | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
+-----------+-------------+-------------------+--------------+----------------+------+-----------+---------------------------------------------+
1 row in set (0.004 sec)

 

select table_id, table_name from __all_virtual_table where tenant_id=1001 and table_name= 'CHAP41';

+------------------+------------+
| table_id | table_name |
+------------------+------------+
| 1100611139453777 | CHAP41 |
+------------------+------------+
1 row in set (0.003 sec)

select row_count,data_size from __all_virtual_meta_table where table_id=1100611139453777;

+-----------+-----------+
| row_count | data_size |
+-----------+-----------+
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
+-----------+-----------+
3 rows in set (0.007 sec)

alter system major freeze;
#等待 1-2 分钟
SELECT * FROM __all_zone WHERE name='merge_status'; --确认 merge_status 为‘IDLE‘状态

select row_count,data_size from __all_virtual_meta_table where table_id=1100611139453777;

+-----------+-----------+
| row_count | data_size |
+-----------+-----------+
| 10001 | 11868 |
| 10001 | 11868 |
| 10001 | 11868 |
+-----------+-----------+
3 rows in set (0.008 sec)

explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= '999' \G

思考： l 哪个系统表中的统计信息发生了变化？
l 哪个查询的选择率发生了变化？
提示：sys 租户中存放统计信息的数据字典表在合并后开始记录统计信息， 比较前后两者
的 EST. ROWS 字段值，每个查询都发生了变化，说明优化器使用新的统计信息进行执行计
划的估算

create index c2_c3 on chap41(c2,c3);
explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= '999' \G

#手动统计字段 c3 的统计信息， 不包含直方图（histogram），再次比较以下 SQL 执行计
划的输出; 统计前先检查现有表的统计信息

登陆 oracle 租户的 sys 用户

obclient -h127.0.0.1 -P2883 -usys@ob_pay#obce_test

select NUM_ROWS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN from all_tab_statistics where
table_name='CHAP41';
+----------+--------------+-----------+-------------+
| NUM_ROWS | EMPTY_BLOCKS | AVG_SPACE | AVG_ROW_LEN |
+----------+--------------+-----------+-------------+
| NULL | NULL | NULL | NULL |
+----------+--------------+-----------+-------------+
1 row in set (0.253 sec)

select LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS,LAST_ANALYZED from
all_tab_col_statistics where table_name='CHAP41';

select ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE,scope from
all_tab_histograms where table_name='CHAP41';

结论：用户 schema 中，目前没有统计信息

说明：本实验环节需要使用 DBMS 包，因集群是 OBD 部署未预装 DBMS，需要学员手动安装，
请先执行以下步骤：1）dbms 的文件在 root 目录 dbms_stats 里，请先将文件中所有 sql 文件移动
至 /home/admin/oceanbase/admin 文件夹下；2）直连 observer，使用 root@sys 登陆，执行
升级程序

obclient -uroot@sys -h127.0.0.1 -P2881
use oceanbase;

alter system set enable_upgrade_mode =true;
set ob_compatibility_mode='oracle';
set ob_query_timeout = 300000000;
DELIMITER /
CREATE OR REPLACE PACKAGE "__DBMS_UPGRADE" IS
PROCEDURE UPGRADE(package_name VARCHAR2);
PROCEDURE UPGRADE_ALL;
END;
/
CREATE OR REPLACE PACKAGE BODY "__DBMS_UPGRADE" IS
PROCEDURE UPGRADE(package_name VARCHAR2);
PRAGMA INTERFACE(c, UPGRADE_SINGLE);
PROCEDURE UPGRADE_ALL;
PRAGMA INTERFACE(c, UPGRADE_ALL);
END;
/

DELIMITER ;
CALL "__DBMS_UPGRADE".UPGRADE('DBMS_STATS');
set ob_compatibility_mode='mysql';
alter system set enable_upgrade_mode = false;

使用之前的 tpcc 会话
call dbms_stats.gather_table_stats('tpcc','chap41', method_opt=>'for columns c3 size auto');

登陆 oracle 租户的 sys 用户
select NUM_ROWS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN from all_tab_statistics where table_name='CHAP41';

select LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS,LAST_ANALYZED from all_tab_col_statistics where table_name='CHAP41';

select ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE,scope from all_tab_histograms where table_name='CHAP41';

检查数据字典中的统计信息 –使用上面步骤的 SQL 命令
explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= '999' \G

#SQL调优

步骤 1 请先安装 obloader 的 jdk 环境，安装文件 jdk-8u333-linux-x64.rpm 已预先放置在
/root/t-oceanbase-antman 目录下
rpm -ivh jdk-8u333-linux-x64.rpm
n 设置环境变量，JAVA_HOME
export JAVA_HOME=/usr/java/default

cd /root/ob-loader-dumper-3.1.0-SNAPSHOT/bin/
./obloader -h 172.16.1.15 -P 2883 -c obce_test -utpcc -pobce_test -t ob_pay -D TPCC --table TABLE_SMALL --thread 2 --ddl --csv --external-data -f /root/table --truncate-table --skip-header

./obloader -h 172.16.1.15 -P 2883 -c obce_test -utpcc -pobce_test -t ob_pay -D TPCC --table TABLE_BIG --thread 2 --ddl --csv --external-data -f /root/table --truncate-table --skip-header

obclient -h172.16.1.15 -P 2883 -utpcc@ob_pay -pobce_test
source /root/obce_sqltune/run1.sql

观察到第一条查询语句执行速度很快，但是第二条查询语句在 1 分钟后，超时退出执行。
为什么？

查找超时 sql 所使用的执行计划
从 gv$sql_audit 中找到该条 sql 执行时 SVR_IP，SVR_PORT，PLAN_ID 等信息，IS_HIT_PLAN=0
表示该条 sql 执行重用了第一条 sql 的执行计划

select * from gv$sql_audit where query_sql like 'select%rownum%table_big%table_small%' order by request_time \G

#从 gv$plan_cache_plan_explain 中查找该执行计划
select * from gv$plan_cache_plan_explain where ip='172.16.1.16' and port='2882' and plan_id=70 ;
select * from gv$plan_cache_plan_explain where svr_ip='172.16.1.53' and svr_port='2882' and plan_id=100 \G; ;

*************************** 1. row ***************************
SVR_IP: 172.16.1.16
SVR_PORT: 2882
REQUEST_ID: 5017
SQL_EXEC_ID: 121728
TRACE_ID: YB42AC100110-0005F91A0CF458EF-0-0
SID: 3222014289
CLIENT_IP: 172.16.1.15
CLIENT_PORT: 4220
TENANT_ID: 1001
TENANT_NAME: ob_pay
EFFECTIVE_TENANT_ID: 1001
USER_ID: 1100611139404777
USER_NAME: TPCC
USER_GROUP: 0
USER_CLIENT_IP: 172.16.1.15
DB_ID: 1100611139404827
DB_NAME: TPCC
SQL_ID: 663BB560FC7FD324409125995F3C979A

思考：从执行计划信息中看到优化器估算该 sql 访问扫描记录数非常少，代价也非常低，与
实际情况差距较大，为什么？
提示：比较2条查询语句，区别主要在于分区的不同，第一条查询语句query1的trn_date
扫描 table_big 一天的数据（2022/12/31 日）， 第二条查询语句 query2 遍历一个月
（2022/6 月）的分区数据， Ocenabase 优化器在执行第一条语句时，发现 table_big 中
没有 2022/12/31 日的数据,生成了一个针对此语句的执行计划，然后在执行第二条类似的
查询语句时，优化复用了第一条语句执行后存放在 sql plan cache 的执行计划，造成第二
条语句没有生成一个更好的执行计划而造成性能问题。显示第一条 SQL 的执行计划， 与后
面的练习比较

explain extended
select *
from ( select row_.*, rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = 'U'
and a.txn_amt != 0
and a.txn_stat = '0'
and a.txn_date>= '20220601'
and a.txn_date<= '20220630'
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = 'C'
and m.txn_stat = '1'
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;

| =========================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------------------------
|0 |COUNT | |25 |391576|
|1 | SUBPLAN SCAN |VIEW1 |25 |391576|
|2 | LIMIT | |25 |391576|
|3 | NESTED-LOOP JOIN | |25 |391575|
|4 | PX COORDINATOR | |25 |361165|
|5 | EXCHANGE OUT DISTR |:EX10000 |25 |361141|
|6 | PX PARTITION ITERATOR | |25 |361141|
|7 | TABLE SCAN |M |25 |361141|
|8 | SUBPLAN SCAN |N |1 |1234 |
|9 | MERGE GROUP BY | |1 |1234 |
|10| PX COORDINATOR | |2 |1234 |
|11| EXCHANGE OUT DISTR |:EX20001 |2 |1233 |
|12| NESTED-LOOP JOIN | |2 |1233 |
|13| EXCHANGE IN DISTR | |1 |1232 |
|14| EXCHANGE OUT DISTR (PKEY)|:EX20000 |1 |1232 |
|15| PX PARTITION ITERATOR | |1 |1232 |
|16| TABLE SCAN |A(TABLE_BIG_I1)|1 |1232 |
|17| PX PARTITION ITERATOR | |755 |292 |
|18| TABLE SCAN |B |755 |292 |
=========================================================================

7 - output([M.ACCT_NUM(0x7fe46a450350)], [M.TXN_DATE(0x7fe46a450930)], [M.CASH_FLAG(0x7fe46a450f10)], [M.TXN_STAT(0x7fe46a451200)], [M.TXN_AMT(0x7fe46a4557c0)], [M.ACCT_CODE(0x7fe46a455ab0)], [M.ACCT_OPEN_ORG(0x7fe46a455da0)], [M.TXN_DETAIL(0x7fe46a456090)]), filter([M.TXN_STAT(0x7fe46a451200) = ?(0x7fe46a45bca0)], [M.CASH_FLAG(0x7fe46a450f10) = 'C'(0x7fe46a45e680)], [M.TXN_DATE(0x7fe46a450930) <= ?(0x7fe46a45d5c0)], [M.TXN_DATE(0x7fe46a450930) >= ?(0x7fe46a45de20)], [M.TXN_AMT(0x7fe46a4557c0) != 0(0x7fe46a45f170)]),
access([M.ACCT_NUM(0x7fe46a450350)], [M.TXN_DATE(0x7fe46a450930)], [M.CASH_FLAG(0x7fe46a450f10)], [M.TXN_STAT(0x7fe46a451200)], [M.TXN_AMT(0x7fe46a4557c0)], [M.ACCT_CODE(0x7fe46a455ab0)], [M.ACCT_OPEN_ORG(0x7fe46a455da0)], [M.TXN_DETAIL(0x7fe46a456090)]), partitions(p[0-2]),
is_index_back=false, filter_before_indexback[false,false,false,false,false],
range_key([M.ACCT_NUM(0x7fe46a450350)], [M.ACCT_CODE(0x7fe46a455ab0)], [M.ACCT_OPEN_ORG(0x7fe46a455da0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true

思考：为什么单独 explain 第二条 query 依然选择了同样的执行计划？
提示：虽然查询范围有了较大变化，可以 select count（*）方式看一下实际的匹配记录
数，但在 M 表（table_big）中估算匹配记录数只有 25 条，并不足以改变执行计划选择。

步骤 3 手工收集统计信息，让优化器可以更准确的估算，查看执行计划是否有变化
call dbms_stats.gather_table_stats('tpcc','table_big', method_opt=>'for all columns size auto');

 

explain extended
select *
from ( select row_.*, rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = 'U'
and a.txn_amt != 0
and a.txn_stat = '0'
and a.txn_date>= '20220601'
and a.txn_date<= '20220630'
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = 'C'
and m.txn_stat = '1'
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;

| ===========================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
---------------------------------------------------------------------------
|0 |COUNT | |100 |3416|
|1 | SUBPLAN SCAN |VIEW1 |100 |3414|
|2 | LIMIT | |100 |3413|
|3 | PX COORDINATOR | |100 |3411|
|4 | EXCHANGE OUT DISTR |:EX10002 |100 |3292|
|5 | LIMIT | |100 |3292|
|6 | NESTED-LOOP JOIN | |100 |3291|
|7 | EXCHANGE IN DISTR | |50 |3036|
|8 | EXCHANGE OUT DISTR (PKEY) |:EX10001 |50 |3025|
|9 | SUBPLAN SCAN |N |50 |3025|
|10| MERGE GROUP BY | |50 |3025|
|11| SORT | |292 |3013|
|12| NESTED-LOOP JOIN | |292 |2613|
|13| EXCHANGE IN DISTR | |4 |1192|
|14| EXCHANGE OUT DISTR (PKEY)|:EX10000 |4 |1191|
|15| PX PARTITION ITERATOR | |4 |1191|
|16| TABLE SCAN |A(TABLE_BIG_I1)|4 |1191|
|17| PX PARTITION ITERATOR | |755 |292 |
|18| TABLE SCAN |B |755 |292 |
|19| PX PARTITION ITERATOR | |1 |4 |
|20| TABLE SCAN |M(TABLE_BIG_I1)|1 |4 |
===========================================================================

 

思考 1：为什么 M 表（table_big）上估算的匹配记录数更少了？
提示 1：join 顺序发生了变化，join 谓词提供了额外的过滤。
思考 2：观察该执行计划，考察数据重分布的情况，是否还有进一步的优化空间？
提示 2：可以使用表组来实现分区间并行 join

create tablegroup t_bs partition by hash partitions 3;
alter table table_big tablegroup t_bs;
alter table table_small tablegroup t_bs;

explain extended
select *
from ( select row_.*, rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = 'U'
and a.txn_amt != 0
and a.txn_stat = '0'
and a.txn_date>= '20220601'
and a.txn_date<= '20220630'
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = 'C'
and m.txn_stat = '1'
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;

| ===============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
---------------------------------------------------------------
|0 |COUNT | |100 |3006|
|1 | SUBPLAN SCAN |VIEW1 |100 |3004|
|2 | LIMIT | |100 |3003|
|3 | PX COORDINATOR | |100 |3001|
|4 | EXCHANGE OUT DISTR |:EX10000 |100 |2882|
|5 | LIMIT | |100 |2882|
|6 | PX PARTITION ITERATOR| |100 |2881|
|7 | LIMIT | |100 |2881|
|8 | NESTED-LOOP JOIN | |100 |2879|
|9 | SUBPLAN SCAN |N |50 |2624|
|10| MERGE GROUP BY | |50 |2624|
|11| NESTED-LOOP JOIN| |292 |2612|
|12| TABLE SCAN |A(TABLE_BIG_I1)|4 |1191|
|13| TABLE SCAN |B |755 |292 |
|14| TABLE SCAN |M(TABLE_BIG_I1)|1 |4 |
===============================================================

思考：比较两张表在没有表组和有表组时，执行计划有啥区别？
提示：使用表组以后，减少了跨机器的数据重分布。

再次执行 run1 脚本，查看两条 query 的执行时间

source /root/obce_sqltune/run1.sql

select * from gv$sql_audit where query_sql like 'select%rownum%table_big%table_small%' order by request_time \G

思考：为什么统计信息收集、表组添加以后，第二条 query 依然超时？
提示：脚本中有 flush plan cache 操作，第一条 query 执行时重选执行计划，选择的执行计划不适合第二条 query

运行 run2 脚本，查看连续执行第二条 query 的变化

source /root/obce_sqltune/run2.sql

查看 query2 和 query3 的执行计划

select * from gv$sql_audit where query_sql like 'select%rownum%table_big%table_small%' order by request_time \G

select * from gv$plan_cache_plan_explain where svr_ip='172.16.1.16' and svr_port='2882' and plan_id=122 ;
select * from gv$plan_cache_plan_explain where svr_ip='172.16.1.16' and svr_port='2882' and plan_id=124 ;

思考：为什么 query2 的执行计划不见了？
提示：OceanBase 优化器的智能反馈功能，根据实际 sql 执行时间与估算时间偏差来重新
选择执行计划（淘汰掉原来计划）

 

source /root/obce_sqltune/run3.sql

通过 gv$sql_audit 查找第二条 query 的 sql_id
select * from gv$sql_audit where query_sql like 'select%rownum%table_big%table_small%' order by request_time \G

663BB560FC7FD324409125995F3C979A
663BB560FC7FD324409125995F3C979A

从第二条 query 语句的 explain 结果中找到 outline data 信息。

Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
LEADING(@"SEL$5" ("N"@"SEL$5" "TPCC.M"@"SEL$5" ))
USE_NL(@"SEL$5" ("TPCC.M"@"SEL$5" ))
PQ_DISTRIBUTE(@"SEL$5" ("TPCC.M"@"SEL$5" ) NONE NONE)
NO_USE_NL_MATERIALIZATION(@"SEL$5" ("TPCC.M"@"SEL$5" ))
NO_USE_HASH_AGGREGATION(@"SEL$4")
LEADING(@"SEL$4" ("TPCC.A"@"SEL$4" "TPCC.B"@"SEL$4" ))
USE_NL(@"SEL$4" ("TPCC.B"@"SEL$4" ))
PQ_DISTRIBUTE(@"SEL$4" ("TPCC.B"@"SEL$4" ) NONE NONE)
NO_USE_NL_MATERIALIZATION(@"SEL$4" ("TPCC.B"@"SEL$4" ))
INDEX(@"SEL$4" "TPCC.A"@"SEL$4" "TABLE_BIG_I1")
FULL(@"SEL$4" "TPCC.B"@"SEL$4")
INDEX(@"SEL$5" "TPCC.M"@"SEL$5" "TABLE_BIG_I1")
END_OUTLINE_DATA
*/

 

create outline big_sql on '663BB560FC7FD324409125995F3C979A' USING HINT /*+LEADING(@"SEL$5" ("N"@"SEL$5" "TPCC.M"@"SEL$5" )) USE_NL(@"SEL$5" ("TPCC.M"@"SEL$5" ))*/;

select * from gv$outline \G

*************************** 1. row ***************************
TENANT_ID: 1001
DATABASE_ID: 1100611139404827
OUTLINE_ID: 1100611139404777
DATABASE_NAME: TPCC
OUTLINE_NAME: BIG_SQL
VISIBLE_SIGNATURE:
SQL_TEXT:
OUTLINE_TARGET:
OUTLINE_SQL:
SQL_ID: 663BB560FC7FD324409125995F3C979A
OUTLINE_CONTENT: /*+LEADING(@"SEL$5" ("N"@"SEL$5" "TPCC.M"@"SEL$5" )) USE_NL(@"SEL$5" ("TPCC.M"@"SEL$5" ))*/
1 row in set (0.055 sec)

 

source /root/obce_sqltune/run1.sql

select * from gv$sql_audit where query_sql like 'select%rownum%table_big%table_small%' order by request_time \G

思考：如何查看 outline 已经生效？
提示：查询 gv$plan_cache_plan_stat。

select * from gv$plan_cache_plan_stat where plan_id=167\G

结论：SQL 执行计划调优中，统计信息收集至关重要，缺乏有效统计信息优化器无法选择
最优计划；同时，使用 outline 绑定执行计划在某些场景下是快速纠正执行计划跑偏的有
效手段。

set ob_query_timeout=60000000;

explain extended
select /*+ USE_NL(a,b) */ *
from ( select row_.*, rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = 'U'
and a.txn_amt != 0
and a.txn_stat = '0'
and a.txn_date>= '20220601'
and a.txn_date<= '20220630'
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = 'C'
and m.txn_stat = '1'
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;