排序字段中有null值，会影响排序的速度吗？

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:dm8（安全版）
【操作系统】：麒麟
【CPU】:飞腾
【问题描述】*：同一条语句，不加order by 毫秒完成。加上order by 要两秒多。排序字段是timestamp，发现有null值。 null值会影响查询速度吗？请大佬帮忙看看

回答 0

暂无回答

阿白

捞一捞

发布于 2022/04/28 16:48

阿白

捞一捞

发布于 2022/04/29 13:07

阿白

捞一捞，怎么看这个执行计划呢

发布于 2022/05/05 09:07

Da A Joey

收集一下表和索引的统计信息：
DBMS_STATS.GATHER_TABLE_STATS(用户名,表名,‘’,100,TRUE,‘FOR ALL COLUMNS SIZE AUTO’);
看下执行计划有没有变化

发布于 2022/05/05 18:59

阿白

有排序的（order by cratetime desc），大概2s多
1   #NSET2: [247, 10, 1092] 
2     #PRJT2: [247, 10, 1092]; exp_num(18), is_atom(FALSE) 
3       #SORT3: [247, 10, 1092]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4         #HASH2 INNER JOIN: [186, 174916, 1092]; LKEY_UNIQUE KEY_NUM(1); KEY(OFFICE.ID=A.OFFICE_ID) KEY_NULL_EQU(0)
5           #CSCN2: [1, 5314, 144]; INDEX33555486(SYS_OFFICE as OFFICE)
6           #SLCT2: [121, 174916, 948]; (exp_cast(A.DEL_FLAG) = 0 AND exp_cast(A.AGE) <= 3)
7             #HASH2 INNER JOIN: [121, 174916, 948]; LKEY_UNIQUE KEY_NUM(1); KEY(INSTITUTION.ID=A.INSTITUTION_ID) KEY_NULL_EQU(0)
8               #SLCT2: [121, 174916, 948]; (exp_cast(A.DEL_FLAG) = 0 AND exp_cast(A.AGE) <= 3)
9                 #NEST LOOP INDEX JOIN2: [121, 174916, 948] 
10                  #ACTRL: [121, 174916, 948];
11                    #BLKUP2: [1, 1325, 192]; INDEX28244711763077924(INSTITUTION)
12                      #SSEK2: [1, 1325, 192]; scan_type(ASC), INDEX28244711763077924(ST_INSTITUTION as INSTITUTION), scan_range['0af425237e0b48c3ac1aa984146b5c72','0af425237e0b48c3ac1aa984146b5c72']
13                  #BLKUP2: [45, 132, 0]; INDEX28244778888313448(A)
14                    #SSEK2: [45, 132, 0]; scan_type(ASC), INDEX28244778888313448(TABLE_TEST as A), scan_range[INSTITUTION.ID,INSTITUTION.ID]
15              #CSCN2: [196, 754255, 756]; INDEX33555583(TABLE_TEST as A)



无排序，毫秒完成
1   #NSET2: [186, 10, 1092] 
2     #PRJT2: [186, 10, 1092]; exp_num(18), is_atom(FALSE) 
3       #TOPN2: [186, 10, 1092]; top_num(10), top_off(20)
4         #HASH2 INNER JOIN: [186, 174916, 1092]; LKEY_UNIQUE KEY_NUM(1); KEY(OFFICE.ID=A.OFFICE_ID) KEY_NULL_EQU(0)
5           #CSCN2: [1, 5314, 144]; INDEX33555486(SYS_OFFICE as OFFICE)
6           #SLCT2: [121, 174916, 948]; (exp_cast(A.DEL_FLAG) = 0 AND exp_cast(A.AGE) <= 3)
7             #HASH2 INNER JOIN: [121, 174916, 948]; LKEY_UNIQUE KEY_NUM(1); KEY(INSTITUTION.ID=A.INSTITUTION_ID) KEY_NULL_EQU(0)
8               #SLCT2: [121, 174916, 948]; (exp_cast(A.DEL_FLAG) = 0 AND exp_cast(A.AGE) <= 3)
9                 #NEST LOOP INDEX JOIN2: [121, 174916, 948] 
10                  #ACTRL: [121, 174916, 948];
11                    #BLKUP2: [1, 1325, 192]; INDEX28244711763077924(INSTITUTION)
12                      #SSEK2: [1, 1325, 192]; scan_type(ASC), INDEX28244711763077924(ST_INSTITUTION as INSTITUTION), scan_range['0af425237e0b48c3ac1aa984146b5c72','0af425237e0b48c3ac1aa984146b5c72']
13                  #BLKUP2: [45, 132, 0]; INDEX28244778888313448(A)
14                    #SSEK2: [45, 132, 0]; scan_type(ASC), INDEX28244778888313448(TABLE_TEST as A), scan_range[INSTITUTION.ID,INSTITUTION.ID]
15              #CSCN2: [196, 754255, 756]; INDEX33555583(TABLE_TEST as A)

发布于 2022/05/06 14:36

yudazzle

在sekect 后面加/*+TOP_ORDER_OPT_FLAG(3) */ 试试

发布于 2022/05/10 11:24