TOP优化

TOP 关键字用于提取查询结果集的前 N 条数据，是分页查询场景中的常用语法。当包含 TOP 的 SQL 语句缺乏过滤条件，或过滤条件的筛选效果不佳时，往往会导致语句执行效率低下，此时需针对性进行优化。以下将分别介绍 TOP + 无排序与 TOP + 排序两种场景下的具体优化方案。

top无排序

数据准备：
DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;
CREATE TABLE T1 AS SELECT LEVEL C1,LEVEL C2,trunc(dbms_random.value(1,4)) C3 FROM dual CONNECT BY LEVEL<=10000000;
CREATE TABLE T2 AS SELECT LEVEL C1,LEVEL C2,trunc(dbms_random.value(1,4)) C3 FROM dual CONNECT BY LEVEL<=10000000;
#C3列随机取1、2、3，模拟过滤性差的数据。

场景一：无过滤条件

两张大表关联默认会走hash连接，整个hash连接处理完后才能返还前N条数据，执行效率比较低，可以考虑禁用 HASH JOIN 方式来优化 TOP 查询。
select top 10 * from t1,t2 where t1.c1=t2.c1;--执行时间1.6s

1   #NSET2: [4303, 10, 76] 
2     #PRJT2: [4303, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #TOPN2: [4303, 10, 76]; top_num(10)
4         #HASH2 INNER JOIN: [4303, 10000000, 76];  KEY_NUM(1); KEY(T1.C1=T2.C1) KEY_NULL_EQU(0)
5           #CSCN2: [1126, 10000000, 38]; INDEX33555773(T1); btr_scan(1)
6           #CSCN2: [1126, 10000000, 38]; INDEX33555774(T2); btr_scan(1)

优化方法1：直接禁用hash连接
禁用hash后计划走的是嵌套循环连接，用一张表的数据去循环拼接另外一张表的数据，只需找到满足条件前N行数据即可快速返还结果。
select/*+ENABLE_HASH_JOIN(0)*/ top 10 * from t1,t2 where t1.c1=t2.c1;--执行时间0ms

1   #NSET2: [6366990, 10, 76] 
2     #PRJT2: [6366990, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #TOPN2: [6366990, 10, 76]; top_num(10)
4         #SLCT2: [6366990, 10000000, 76]; T1.C1 = T2.C1
5           #NEST LOOP INNER JOIN2: [6366990, 10000000, 76] 
6             #CSCN2: [1126, 10000000, 38]; INDEX33555773(T1); btr_scan(1)
7             #CSCN2: [1126, 10000000, 38]; INDEX33555774(T2); btr_scan(1)

优化方法2：加索引走索引连接
t2表加C1列索引，此时计划会走索引连接，执行逻辑和镶嵌内连接差不多，只不过不用全表扫右孩子，只需要扫描索引，进而达到优化top查询的目的。
create index idx_t2_c1 on t2(c1);
select top 10 * from t1,t2 where t1.c1=t2.c1;--执行时间0ms

2     #PRJT2: [1, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #TOPN2: [1, 10, 76]; top_num(10)
4         #NEST LOOP INDEX JOIN2: [1, 10000000, 76] 
5           #CSCN2: [1126, 10000000, 38]; INDEX33555773(T1); btr_scan(1)
6           #BLKUP2: [671878, 1, 4]; IDX_T2_C1(T2)
7             #SSEK2: [671878, 1, 4]; scan_type(ASC), IDX_T2_C1(T2), scan_range[T1.C1,T1.C1], is_global(0)

场景2：有过滤条件

对于过滤性好的情况，直接在过滤列和被驱动表连接列加索引即可，下面讨论条件过滤性不好的情况。
删除前期建立索引：drop index idx_t2_c2;
此时仍然走的是hash连接，需要去掉hash优化top查询。
select top 10 * from t1,t2 where t1.c1=t2.c1 and t2.c3=3;--执行时间1.6s

1   #NSET2: [3488, 10, 76] 
2     #PRJT2: [3488, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #TOPN2: [3488, 10, 76]; top_num(10)
4         #HASH2 INNER JOIN: [3488, 3331195, 76];  KEY_NUM(1); KEY(T2.C1=T1.C1) KEY_NULL_EQU(0)
5           #SLCT2: [1222, 3331195, 38]; T2.C3 = var1 SLCT_PUSHDOWN(TRUE)
6             #CSCN2: [1222, 10000000, 38]; INDEX33555774(T2) NEED_SLCT(TRUE); btr_scan(1)
7           #CSCN2: [1126, 10000000, 38]; INDEX33555773(T1); btr_scan(1)

优化方法：加索引走索引连接
可以在t1表C1列加索引进行优化，优化后走索引连接，执行效率大幅提升。
create index idx_t1_c1 on t1(c1);
select top 10 * from t1,t2 where t1.c1=t2.c1 and t2.c3=3;--执行时间0ms

1   #NSET2: [1, 10, 76] 
2     #PRJT2: [1, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #TOPN2: [1, 10, 76]; top_num(10)
4         #HASH2 INNER JOIN: [1, 3331195, 76];  KEY_NUM(1); KEY(T2.C1=T1.C1) KEY_NULL_EQU(0)
5           #NEST LOOP INDEX JOIN2: [1, 3331195, 76] 
6             #ACTRL: [1, 3331195, 76]
7               #SLCT2: [1222, 3331195, 38]; T2.C3 = var1 SLCT_PUSHDOWN(TRUE)
8                 #CSCN2: [1222, 10000000, 38]; INDEX33555774(T2) NEED_SLCT(TRUE); btr_scan(1)
9             #BLKUP2: [223815, 1, 4]; IDX_T1_C1(T1)
10              #SSEK2: [223815, 1, 4]; scan_type(ASC), IDX_T1_C1(T1), scan_range[T2.C1,T2.C1], is_global(0)
11          #CSCN2: [1126, 10000000, 38]; INDEX33555773(T1); btr_scan(1)

top有排序

场景一：单表TOP+排序

单表TOP+排序，过滤条件过滤性好的情况下，只需对过滤条件加索引进行优化，下面讨论过滤性不好的情况。
由于过滤条件过滤性差，走全表扫描，还需要进行排序，执行效率比较低，可以考虑排序列加索引，利用TOP_ORDER_OPT_FLAG参数进行top优化。
select * from t1 where c3=2 order by c1 limit 10;--执行时间1s

1   #NSET2: [1479, 10, 50] 
2     #PRJT2: [1479, 10, 50]; exp_num(4), is_atom(FALSE) 
3       #SORT3: [1479, 10, 50]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4         #SLCT2: [1246, 3331459, 50]; T1.C3 = var1 SLCT_PUSHDOWN(TRUE)
5           #CSCN2: [1246, 10000000, 50]; INDEX33555773(T1) NEED_SLCT(TRUE); btr_scan(1)

优化方法：加索引优化，去排序
create index idx_t1_c1 on t1(c1);
select * from t1 where c3=2 order by c1 limit 10;--执行时间0.001s

1   #NSET2: [1, 10, 50] 
2     #PRJT2: [1, 10, 50]; exp_num(4), is_atom(FALSE) 
3       #TOPN2: [1, 10, 50]; top_num(10)
4         #SLCT2: [1, 300, 50]; T1.C3 = var1
5           #BLKUP2: [1, 300, 50]; IDX_T1_C1(T1)
6             #SSCN: [1, 300, 50]; IDX_T1_C1(T1); btr_scan(1); is_global(0)

这里用到了TOP_ORDER_OPT_FLAG参数，默认值包含1，当语句内含有 TOP + ORDER，且 ORDER BY 列属于索引前导列时，根据 ORDER BY 列对应的基表信息，减少估算的行数从而减少代价计算，去掉排序，进行top优化。
对比TOP_ORDER_OPT_FLAG=0的场景：未利用上索引进行top优化，执行效率低。
select/*+TOP_ORDER_OPT_FLAG(0)*/ top 10 * from t1 where c3=2 order by c1;--执行时间1s

1   #NSET2: [1479, 10, 50] 
2     #PRJT2: [1479, 10, 50]; exp_num(4), is_atom(FALSE) 
3       #SORT3: [1479, 10, 50]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4         #SLCT2: [1246, 3333922, 50]; T1.C3 = var1 SLCT_PUSHDOWN(TRUE)
5           #CSCN2: [1246, 10000000, 50]; INDEX33555786(T1) NEED_SLCT(TRUE); btr_scan(1)

场景二：top排序+表连接

删除前期建立索引：
drop INDEX IDX_T1_C1;
默认走hash连接，然后再进行排序，执行效率低。
select top 10 * from t1,t2 where t1.c1=t2.c1 order by t1.c1;--执行时间7s

1   #NSET2: [5033, 10, 76] 
2     #PRJT2: [5033, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #SORT3: [5033, 10, 76]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4         #HASH2 INNER JOIN: [4303, 10000000, 76];  KEY_NUM(1); KEY(T1.C1=T2.C1) KEY_NULL_EQU(0)
5           #CSCN2: [1126, 10000000, 38]; INDEX33555773(T1); btr_scan(1)
6           #CSCN2: [1126, 10000000, 38]; INDEX33555774(T2); btr_scan(1)

优化方法：加索引去hash连接和TOP_ORDER_OPT_FLAG TOP优化
在被驱动表连接列加索引去hash连接，并且排序列加索引利用TOP_ORDER_OPT_FLAG进行top优化。
create index idx_t1_c2 on t1(c2);
create index idx_t2_c1 on t2(c1);
select top 10 * from t1,t2 where t1.c1=t2.c1 order by t1.c2;--执行时间0.001s

1   #NSET2: [7, 10, 76] 
2     #PRJT2: [7, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #TOPN2: [7, 10, 76]; top_num(10)
4         #NEST LOOP INDEX JOIN2: [7, 300, 76] 
5           #BLKUP2: [1, 300, 38]; IDX_T1_C2(T1)
6             #SSCN: [1, 300, 38]; IDX_T1_C2(T1); btr_scan(1); is_global(0)
7           #BLKUP2: [2, 1, 4]; IDX_T2_C1(T2)
8             #SSEK2: [2, 1, 4]; scan_type(ASC), IDX_T2_C1(T2), scan_range[T1.C1,T1.C1], is_global(0)

同样对于存在过滤条件，但过滤条件过滤性不好的也有同样的效果。
select top 10 * from t1,t2 where t1.c1=t2.c1 and t2.c3=1 order by t1.c2;--执行时间0.001s

1   #NSET2: [7, 10, 76] 
2     #PRJT2: [7, 10, 76]; exp_num(6), is_atom(FALSE) 
3       #TOPN2: [7, 10, 76]; top_num(10)
4         #SLCT2: [7, 300, 76]; T2.C3 = var1
5           #NEST LOOP INDEX JOIN2: [7, 300, 76] 
6             #BLKUP2: [1, 300, 38]; IDX_T1_C2(T1)
7               #SSCN: [1, 300, 38]; IDX_T1_C2(T1); btr_scan(1); is_global(0)
8             #BLKUP2: [2, 1, 4]; IDX_T2_C1(T2)
9               #SSEK2: [2, 1, 4]; scan_type(ASC), IDX_T2_C1(T2), scan_range[T1.C1,T1.C1], is_global(0)

TOP优化

TOP优化

top无排序

场景一：无过滤条件

场景2：有过滤条件

top有排序

场景一：单表TOP+排序

场景二：top排序+表连接

作者