[20230512]优化的困惑19.txt

来源：这里教程网时间：2026-03-03 18:45:29 作者：

[20230512]优化的困惑19.txt --//在优化生产系统一条sql语句遇到的情况. 1.环境: SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试例子建立: $ cat ee1.txt @cs lis @sl all select TEST_DATE from lis_test where pat_id= '1111' or identity_id= '2222'; @cs sys $ cat ee2.txt @cs lis @sl all select TEST_DATE from lis_test where pat_id= '1111' or pat_name= '2222'; @cs sys --//不同之处在于前者identity_id= '2222'后者pat_name= '2222'. 3.问题提出: SYS@192.168.100.235:1521/orcl> @ ee1.txt alter session set current_schema=lis Session altered. alter session set statistics_level = all; Session altered. no rows selected alter session set current_schema=sys Session altered. SYS@192.168.100.235:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID ggqfz9201njzj, child number 0 ------------------------------------- select TEST_DATE from lis_test where pat_id= '1111' or identity_id= '2222' Plan hash value: 958283288 ------------------------------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers| -------------------------------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | | 8 (100)| | 0|00:00:00.01| 7| | 1| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 1| 1| 53 | 8 (13)|00:00:01| 0|00:00:00.01| 7| | 2| BITMAP CONVERSION TO ROWIDS | | 1| | | | | 0|00:00:00.01| 7| | 3| BITMAP OR | | 1| | | | | 0|00:00:00.01| 7| | 4| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3| |*5| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 1| | | 3 (0)|00:00:01| 0|00:00:00.01| 3| | 6| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 4| | 7| SORT ORDER BY | | 1| | | | | 0|00:00:00.01| 4| |*8| INDEX RANGE SCAN |IX_LIS_TEST_IDENTITY_ID| 1| | | 4 (0)|00:00:01| 0|00:00:00.01| 4| -------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / LIS_TEST@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("PAT_ID"=U'1111') 8 - access("IDENTITY_ID"=U'2222') filter("IDENTITY_ID"=U'2222') --//执行计划没有怎么特殊的地方,但是细节很重要,不理解为什么id=7出现一次SORT ORDER BY呢? SYS@192.168.100.235:1521/orcl> @ ee2.txt alter session set current_schema=lis Session altered. alter session set statistics_level = all; Session altered. TEST_DATE ------------------- 2022-03-25 00:00:00 --//有点意外竟然生产系统数据库存在pat_name= '2222'的情况. alter session set current_schema=sys Session altered. SYS@192.168.100.235:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 613tuxrh1j1xz, child number 0 ------------------------------------- select TEST_DATE from lis_test where pat_id= '1111' or pat_name= '2222' Plan hash value: 408580782 ----------------------------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers| ----------------------------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | | 6 (100)| | 1|00:00:00.01| 7| | 1| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 1| 1| 33 | 6 (0)|00:00:01| 1|00:00:00.01| 7| | 2| BITMAP CONVERSION TO ROWIDS | | 1| | | | | 1|00:00:00.01| 6| | 3| BITMAP OR | | 1| | | | | 1|00:00:00.01| 6| | 4| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 1|00:00:00.01| 3| |*5| INDEX RANGE SCAN |IX_LIS_TEST_PAT_NAME| 1| | | 3 (0)|00:00:01| 1|00:00:00.01| 3| | 6| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3| |*7| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 1| | | 3 (0)|00:00:01| 0|00:00:00.01| 3| ----------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / LIS_TEST@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("PAT_NAME"=U'2222') 7 - access("PAT_ID"=U'1111') --//这个执行计划就没有sort order by操作.为什么呢? --//查看索引的一些细节. SYS@192.168.100.235:1521/orcl> @ ind2 lis.lis_test Display indexes where table or index name matches lis.lis_test... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC ----------- ----------- ------------------------------ ---- ------------------------------ ---- LIS LIS_TEST IX_LIS_TEST_BARCODE 1 BARCODE IX_LIS_TEST_IDENTITY_ID 1 IDENTITY_ID 2 SYS_NC00142$ IX_LIS_TEST_ORDER_TIME 1 ORDER_TIME IX_LIS_TEST_PAT_BARCODE 1 PAT_BARCODE IX_LIS_TEST_PAT_ID 1 PAT_ID IX_LIS_TEST_PAT_NAME 1 PAT_NAME IX_LIS_TEST_PHONE_NO 1 PHONE_NO I_LIS_TEST_AUDIT_TIME 1 AUDIT_TIME I_LIS_TEST_ORIGINAL_BARCODE 1 ORIGINAL_BARCODE I_LIS_TEST_TEST_DATE_INST_ID_X 1 TEST_DATE 2 INST_ID 3 TEST_NO PK_LIS_TEST 1 ID INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT ----------- ----------- ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ --------- LIS LIS_TEST IX_LIS_TEST_BARCODE NORMAL NO VALID NO N 3 60211 9530368 9859167 9652034 2022-11-01 09:17:08 1 VISIBLE LIS_TEST IX_LIS_TEST_IDENTITY_ID FBI NORMAL NO VALID NO N 4 71817 852492 9859185 7530972 2022-11-01 09:18:05 1 VISIBLE LIS_TEST IX_LIS_TEST_ORDER_TIME NORMAL NO VALID NO N 3 27784 3517952 9859083 9577769 2022-11-01 09:14:49 1 VISIBLE LIS_TEST IX_LIS_TEST_PAT_BARCODE NORMAL NO VALID NO N 3 51019 1774080 9551801 9381020 2022-11-01 09:15:41 1 VISIBLE LIS_TEST IX_LIS_TEST_PAT_ID NORMAL NO VALID NO N 3 50518 1586560 9552995 9382441 2022-11-01 09:16:16 1 VISIBLE LIS_TEST IX_LIS_TEST_PAT_NAME NORMAL NO VALID NO N 3 32214 745792 9780497 9616062 2022-11-01 09:17:29 1 VISIBLE LIS_TEST IX_LIS_TEST_PHONE_NO NORMAL NO VALID NO N 3 2070 45832 333127 320998 2022-11-01 09:15:11 1 VISIBLE LIS_TEST I_LIS_TEST_AUDIT_TIME NORMAL NO VALID NO N 3 25473 6009344 9584155 8979755 2022-11-01 09:14:56 1 VISIBLE LIS_TEST I_LIS_TEST_ORIGINAL_BARCODE NORMAL NO VALID NO N 1 0 0 0 0 2022-11-01 09:14:41 1 VISIBLE LIS_TEST I_LIS_TEST_TEST_DATE_INST_ID_X NORMAL NO VALID NO N 3 21084 9708295 9859083 8940127 2022-11-01 09:14:41 1 VISIBLE LIS_TEST PK_LIS_TEST NORMAL YES VALID NO N 3 19981 9859155 9859155 9026718 2022-11-01 09:16:34 1 VISIBLE --//IX_LIS_TEST_IDENTITY_ID 是一个函数索引. SYS@192.168.100.235:1521/orcl> @ ddl lis.IX_LIS_TEST_IDENTITY_ID C300 ------------------------------------------------------------------------------------- CREATE INDEX "LIS"."IX_LIS_TEST_IDENTITY_ID" ON "LIS"."LIS_TEST" ("IDENTITY_ID", 0) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LIS_MAX_DATA" ; --//可以看出开发为了使用索引查询IDENTITY_ID是null的情况在加入常量字段0,这样保证IDENTITY_ID=null也在索引中.实际上的情况是 --//画蛇添足!!,根本没有.因为null值太多了. SYS@192.168.100.235:1521/orcl> @ desczz lis.lis_test IDENTITY_ID,pat_id,pat_name eXtended describe of lis.lis_test DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- ----- -------------- ------------ -------------- ---------- --------- ----------- ----------- -------------------------- LIS LIS_TEST 8486 2022-12-12 10:03:00 9 PAT_ID NVARCHAR2(36) 1586560 .00000000100 306100 1 M10019 质控样本7 8675 2022-12-12 10:03:00 12 PAT_NAME NVARCHAR2(100) 745792 .00000000100 78681 1 AAAAA ｖｃ＋ 6290 2022-12-12 10:03:00 120 IDENTITY_ID NVARCHAR2(36) 848256 .00000000100 2745499 1 港澳台通行证号：HYYYYYYYY --//IDENTITY_ID is null 的值很多. --//搞不懂为什么生产库会出现pat_id='质控样本7',pat_name='ｖｃ＋'的情况. --//我自己本身还是无法理解为什么这样的方式会出现一个sort order by的情况. $ cat ee3.txt @cs lis @sl all select TEST_DATE,PAT_NAME from lis_test where pat_id= '1111' or TEST_DATE='2022-10-13 10:00:00'; @cs sys --//执行如上ee3.txt,再次出现1次sort,使用如下查询执行计划.(第2参数加入projection) SYS@192.168.100.235:1521/orcl> @ dpc '' projection PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID gjaykccnjuupu, child number 0 ------------------------------------- select TEST_DATE,PAT_NAME from lis_test where pat_id= '1111' or TEST_DATE='2022-10-13 10:00:00' Plan hash value: 124482500 --------------------------------------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers| --------------------------------------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | |5170 (100)| | 0|00:00:00.01| 6| | 1| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 1| 15271| 492K|5170 (1)|00:00:01| 0|00:00:00.01| 6| | 2| BITMAP CONVERSION TO ROWIDS | | 1| | | | | 0|00:00:00.01| 6| | 3| BITMAP OR | | 1| | | | | 0|00:00:00.01| 6| | 4| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3| | 5| SORT ORDER BY | | 1| | | | | 0|00:00:00.01| 3| |*6| INDEX RANGE SCAN |I_LIS_TEST_TEST_DATE_INST_ID_X| 1| | | 35 (0)|00:00:01| 0|00:00:00.01| 3| | 7| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3| |*8| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 1| | | 3 (0)|00:00:01| 0|00:00:00.01| 3| --------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / LIS_TEST@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("TEST_DATE"=TO_DATE(' 2022-10-13 10:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter("TEST_DATE"=TO_DATE(' 2022-10-13 10:00:00', 'syyyy-mm-dd hh24:mi:ss')) 8 - access("PAT_ID"=U'1111') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "TEST_DATE"[DATE,7], "PAT_NAME"[NVARCHAR2,100] 2 - "LIS_TEST".ROWID[ROWID,10] 3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496] 4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496] 5 - (#keys=1) "LIS_TEST".ROWID[ROWID,10] 6 - "LIS_TEST".ROWID[ROWID,10] 7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496] 8 - "LIS_TEST".ROWID[ROWID,10] 45 rows selected. --//仔细看Column Projection Information (identified by operation id):部分就很容易明白了为什么出现sort order by操作,id=6 --//仅仅取出rowid.对于I_LIS_TEST_TEST_DATE_INST_ID_X索引来讲,索引的存储顺序是TEST_DATE,INST_ID,TEST_NO,ID,rowid. --//按照INDEX RANGE SCAN取出rowid的顺序可能并没有排序,因为id=4操作BITMAP CONVERSION FROM ROWIDS,需要rowid是排序的,这样增加一步排序 --//rowid的操作,就出现上述的情况. --//使用IX_LIS_TEST_IDENTITY_ID索引的情况类似,索引的存储顺序顺是IDENTITY_ID,0,rowid,oracle并没有智能,知道INDEX RANGE --//SCAN取出rowid已经排序的,出现一个sort order by排序操作就很正常了. --//实际上先入为主的观念是以为IX_LIS_TEST_IDENTITY_ID仅仅包含1个IDENTITY_ID字段,看到是函数索引时,第2个值为0,自己也没有转过这个弯, --//实际上仅仅需要理解BITMAP CONVERSION FROM ROWIDS之前的rowid要排序的这步操作,上述情况很容易理解. --//顺便建立索引IDENTITY_ID索引. CREATE INDEX LIS.I_LIS_TEST_IDENTITY_ID ON LIS.LIS_TEST (IDENTITY_ID) LOGGING NOPARALLEL ONLINE; ALTER INDEX LIS.IX_LIS_TEST_IDENTITY_ID INVISIBLE; --//观察一段来决定是否删除LIS.IX_LIS_TEST_IDENTITY_ID索引. SYS@192.168.100.235:1521/orcl> @ ee1.txt alter session set current_schema=lis Session altered. alter session set statistics_level = all; Session altered. no rows selected alter session set current_schema=sys Session altered. Plan hash value: 2203104331 -------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 | 7 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 1 | 53 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | 0 |00:00:00.01 | 7 | | 3 | BITMAP OR | | 1 | | | | | 0 |00:00:00.01 | 7 | | 4 | BITMAP CONVERSION FROM ROWIDS | | 1 | | | | | 0 |00:00:00.01 | 3 | |* 5 | INDEX RANGE SCAN | IX_LIS_TEST_PAT_ID | 1 | | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 | | 6 | BITMAP CONVERSION FROM ROWIDS | | 1 | | | | | 0 |00:00:00.01 | 4 | |* 7 | INDEX RANGE SCAN | I_LIS_TEST_IDENTITY_ID | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | -------------------------------------------------------------------------------------------------------------------------------------------------------- --//使用新的I_LIS_TEST_IDENTITY_ID索引,这样的情况就不会出现sort order by了.

编辑推荐：

返回列表

[20230512]优化的困惑19.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推