说明 | 处理 | 语句 | 计划 | 执行时间(秒) | 执行比 |
A0 【原语句和计划和执行时间】 | null | select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5; | 1 #NSET2: [217, 5, 2376]
2 #PRJT2: [217, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [217, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #SLCT2: [38, 2500, 2376]; T_TEST_1.COL_14 > '1' 5 #CSCN2: [38, 50000, 2376]; INDEX33559006(T_TEST_1) | 0.078 | 9.75 |
A1 【常规优化 1 ,给col_14添加索引】 | create index idx_t_test_1_1 on t_test_1(col_14); | select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5; | 1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1) 5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max] | 0.324 | 40.5 |
B1 【有人会说,这里优化的不对,要把col_end也带上】 | create index idx_t_test_1_2 on t_test_1(col_14,col_end); | select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5; | 1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1) 5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max] | 0.324 | 40.5 |
B2 【col_end desc 呢】 | create index idx_t_test_1_3 on t_test_1(col_14,col_end desc); | select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5; | 1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1) 5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max] | 0.324 | 40.5 |
B2 【在B2的基础上调整顺序】 | create index idx_t_test_1_4 on t_test_1(col_end desc,col_14); | select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5; | 1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1) 5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max] | 0.324 | 40.5 |
B3 | 我们看看有什么相关的参数 select * from A8 where para_name like '%TOP%' 我们把这个看起来像是有关系的参数,修改为1,看下计划和执行时间。 | select/*+TOP_ORDER_OPT_FLAG(1)*/ * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5; | 1 #NSET2: [0, 5, 2376]
2 #PRJT2: [0, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [0, 5, 2376]; top_num(5), top_off(0) 4 #SLCT2: [0, 100, 2376]; T_TEST_1.COL_14 > '1' 5 #BLKUP2: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1) 6 #SSCN: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1) | 0.008 | 1 |
B4 |
| select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5; | 1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1) 5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max] | 0.324 | 40.5 |
B5 |
| select/*+TOP_ORDER_OPT_FLAG(1)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5; | 1 #NSET2: [0, 5, 2376]
2 #PRJT2: [0, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [0, 5, 2376]; top_num(5), top_off(0) 4 #SLCT2: [0, 100, 2376]; T_TEST_1.COL_14 = '1' 5 #BLKUP2: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1) 6 #SSCN: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1) | 0.008 | 1 |
B6 |
| select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5; | 1 #NSET2: [5, 5, 2376]
2 #PRJT2: [5, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [5, 5, 2376]; top_num(5), top_off(0) 4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_3(T_TEST_1) 5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_3(T_TEST_1), scan_range[('1',min),('1',max)) |
| 0 |
B7 | update t_test_1 set col_14='1' where rownum<=30000 ;commit; | select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5; | 1 #NSET2: [5, 5, 2376]
2 #PRJT2: [5, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [5, 5, 2376]; top_num(5), top_off(0) 4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_3(T_TEST_1) 5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_3(T_TEST_1), scan_range[('1',min),('1',max)) | 0.008 | 1 |
B8 |
| select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4) no_index(t_test_1,IDX_T_TEST_1_3)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5; | 1 #NSET2: [95, 5, 2376]
2 #PRJT2: [95, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [95, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_1(T_TEST_1) 5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range['1','1'] | 0.186 | 23.25 |
B9 |
| select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4) no_index(t_test_1,IDX_T_TEST_1_3) no_index(t_test_1,IDX_T_TEST_1_1)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5; | 1 #NSET2: [95, 5, 2376]
2 #PRJT2: [95, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [95, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0) 4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_2(T_TEST_1) 5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_2(T_TEST_1), scan_range[('1',min),('1',max)) | 0.343 | 42.875 |