[20250714]使用or_expand提示遇到的疑惑.txt

来源：这里教程网时间：2026-03-03 22:20:58 作者：

[20250714]使用or_expand提示遇到的疑惑.txt --//生产系统遇到的问题，使用or_expand提示，通过例子说明问题。 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.建立测试环境： --//drop table t1 purge ; create table t1 as select * from all_objects; create index i_t1_object_id on t1(object_id); --//create index i_t1_data_object_id on t1(data_object_id); --//create index i_t1_object_name on t1(object_name); --//分析表略。 --//当前仅仅建立object_id字段索引。 --//建立测试执行sql语句： $ cat g1.txt set term off variable v_id number ; variable v_did number ; variable v_name varchar2(32) ; exec :v_id := 76191; exec :v_did := 76191; exec :v_name := 'DEPT'; set term on SELECT /*+ &&1 */ t1.object_id ,t1.object_name ,t1.object_type FROM t1 WHERE ( ( :v_did = '' OR :v_did IS NULL) OR data_object_id = :v_did) and ( ( :v_id = '' OR :v_id IS NULL) OR object_id = :v_id) and ( ( :v_name = '' OR :v_name IS NULL) OR object_name = :v_name) ; --//注：主要模拟开发的写法，另外注意查询条件有索引的条件写在中间。 3.测试： SCOTT@book01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book01p> @ g1.txt '' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE --//执行计划如下，在没有任何提示的情况下，执行计划选择全表扫描。 Plan hash value: 3617692013 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 414 (100)| | 1 |00:00:00.01 | 1486 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 477 | 414 (1)| 00:00:01 | 1 |00:00:00.01 | 1486 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / "T1"@"SEL$1" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(((:V_ID IS NULL OR "OBJECT_ID"=:V_ID) AND (:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID) AND (:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME))) --//加入提示OR_EXPAND(@"SEL$1") SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1")' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE --//执行计划如下： Plan hash value: 3861657155 ------------------------------------------------------------------------------------------------------------------------------------- | Id |Operation |Name |Starts|E-Rows |E-Bytes|Cost (%CPU)|E-Time |A-Rows| A-Time |Buffers| ------------------------------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | | 416 (100)| | 1|00:00:00.01| 4| | 1| VIEW |VW_ORE_BA8ECEFB| 1| 177 | 16284 | 416 (1)|00:00:01| 1|00:00:00.01| 4| | 2| UNION-ALL | | 1| | | | | 1|00:00:00.01| 4| |* 3| FILTER | | 1| | | | | 0|00:00:00.01| 0| |* 4| TABLE ACCESS FULL |T1 | 0| 176 | 9328 | 414 (1)|00:00:01| 0|00:00:00.01| 0| |* 5| FILTER | | 1| | | | | 1|00:00:00.01| 4| |* 6| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 1| 1 | 53 | 2 (0)|00:00:01| 1|00:00:00.01| 4| |* 7| INDEX RANGE SCAN |I_T1_OBJECT_ID | 1| 1 | | 1 (0)|00:00:01| 1|00:00:00.01| 3| ------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$2A13AF86 / "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB" 2 - SET$2A13AF86 3 - SET$2A13AF86_1 4 - SET$2A13AF86_1 / "T1"@"SET$2A13AF86_1" 5 - SET$2A13AF86_2 6 - SET$2A13AF86_2 / "T1"@"SET$2A13AF86_2" 7 - SET$2A13AF86_2 / "T1"@"SET$2A13AF86_2" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$2A13AF86_2") OUTLINE_LEAF(@"SET$2A13AF86_1") OUTLINE_LEAF(@"SET$2A13AF86") OUTLINE_LEAF(@"SEL$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$9162BF3C" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") FULL(@"SET$2A13AF86_1" "T1"@"SET$2A13AF86_1") INDEX_RS_ASC(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:V_ID IS NULL) 4 - filter(((:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID) AND (:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME))) 5 - filter(LNNVL(:V_ID IS NULL)) 6 - filter(((:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID) AND (:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME))) 7 - access("OBJECT_ID"=:V_ID) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 5 - SET$2A13AF86_2 - OR_EXPAND(@"SEL$1") --//注意实际上outline里面记录的是OR_EXPAND(@"SEL$1" (1) (2))。 --//但是当我加入提示OR_EXPAND(@"SEL$1" (1) (2))时会出现什么情况呢。 SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" (1) (2))' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE --//执行计划如下： COTT@book01p> @ dpc '' outline '' ... Plan hash value: 3617692013 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 414 (100)| | 1 |00:00:00.01 | 1486 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 477 | 414 (1)| 00:00:01 | 1 |00:00:00.01 | 1486 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / "T1"@"SEL$1" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(((:V_ID IS NULL OR "OBJECT_ID"=:V_ID) AND (:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID) AND (:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME))) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 U - OR_EXPAND(@"SEL$1" (1) (2)) --//执行计划反而时选择全表扫描。 --//可以抽取前面的outline，写成如下： $ cat g2.txt set term off variable v_id number ; variable v_did number ; variable v_name varchar2(32) ; exec :v_id := 76191; exec :v_did := 76191; exec :v_name := 'DEPT'; set term on SELECT /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$2A13AF86_2") OUTLINE_LEAF(@"SET$2A13AF86_1") OUTLINE_LEAF(@"SET$2A13AF86") OUTLINE_LEAF(@"SEL$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$9162BF3C" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") FULL(@"SET$2A13AF86_1" "T1"@"SET$2A13AF86_1") INDEX_RS_ASC(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2") END_OUTLINE_DATA */ t1.object_id ,t1.object_name ,t1.object_type FROM t1 WHERE ( ( :v_did = '' OR :v_did IS NULL) OR data_object_id = :v_did) and ( ( :v_id = '' OR :v_id IS NULL) OR object_id = :v_id) and ( ( :v_name = '' OR :v_name IS NULL) OR object_name = :v_name) ; --//执行发现执行计划还是选择全表扫描，结果不再贴出。 4.分析： --//做10053分析： SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" )' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE SCOTT@book01p> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 1827272953 az74updqfmy7t 0 129273 3861657155 6ce9f8f9 2025-07-14 09:43:16 16777219 SCOTT@book01p> @ 10053x az74updqfmy7t 0 /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3990_aaz74updqfmy7t.trc COTT@book01p> @ 10053y '' TRCLINE ------------------------------------------------------------------------------------------------------------------------ Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "OBJECT_ID","VW_ORE_BA8ECEFB"."ITEM_2" "OBJECT_NAME","VW_ORE_BA8ECEFB"."ITEM_3" "OBJEC T_TYPE" FROM ( (SELECT "T1"."OBJECT_ID" "ITEM_1","T1"."OBJECT_NAME" "ITEM_2","T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"." T1" "T1" WHERE (:B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2) AND :B3 IS NULL AND (:B4 IS NULL OR "T1"."OBJECT_NAME"=:B5)) U NION ALL (SELECT "T1"."OBJECT_ID" "ITEM_1","T1"."OBJECT_NAME" "ITEM_2","T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"."T1" "T 1" WHERE (:B6 IS NULL OR "T1"."DATA_OBJECT_ID"=:B7) AND "T1"."OBJECT_ID"=:B8 AND (:B9 IS NULL OR "T1"."OBJECT_NAME"=:B10 ) AND LNNVL(:B11 IS NULL))) "VW_ORE_BA8ECEFB" --//格式化如下： /* Formatted on 2025-07-14 09:47:56 (QP5 v5.277) */ SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "OBJECT_ID" ,"VW_ORE_BA8ECEFB"."ITEM_2" "OBJECT_NAME" ,"VW_ORE_BA8ECEFB"."ITEM_3" "OBJECT_TYPE" FROM ( (SELECT "T1"."OBJECT_ID" "ITEM_1", "T1"."OBJECT_NAME" "ITEM_2", "T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE ( :B1 IS NULL OR "T1"."DATA_OBJECT_ID" = :B2) AND :B3 IS NULL AND ( :B4 IS NULL OR "T1"."OBJECT_NAME" = :B5)) UNION ALL (SELECT "T1"."OBJECT_ID" "ITEM_1", "T1"."OBJECT_NAME" "ITEM_2", "T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE ( :B6 IS NULL OR "T1"."DATA_OBJECT_ID" = :B7) AND "T1"."OBJECT_ID" = :B8 AND ( :B9 IS NULL OR "T1"."OBJECT_NAME" = :B10) AND LNNVL ( :B11 IS NULL))) "VW_ORE_BA8ECEFB" --//查看跟踪文件内容： ORE: Predicate list P1 : (:B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2) AND (:B3 IS NULL OR "T1"."OBJECT_ID"=:B4) AND (:B5 IS NULL OR "T1"."OBJECT_NAME"=:B6) P2 : :B1 IS NULL P3 : "T1"."OBJECT_ID"=:B1 P4 : :B1 IS NULL OR "T1"."OBJECT_NAME"=:B2 DNF Matrix (Before sorting OR branches) P1 P2 P3 P4 CNJ (#1) : 1 1 0 1 CNJ (#2) : 1 0 1 1 ORE: Predicate list P1 : (:B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2) AND (:B3 IS NULL OR "T1"."OBJECT_ID"=:B4) AND (:B5 IS NULL OR "T1"."OBJECT_NAME"=:B6) P2 : :B1 IS NULL P3 : "T1"."OBJECT_ID"=:B1 P4 : :B1 IS NULL OR "T1"."OBJECT_NAME"=:B2 DNF Matrix (After OR branch sorting) P1 P2 P3 P4 CNJ (#1) : 1 1 0 1 CNJ (#2) : 1 0 1 1 --//oracle仅仅拆分2种情况，对应中间条件( ( :v_id = '' OR :v_id IS NULL) OR object_id = :v_id)。 --//实际上oracle将条件改写为(:v_id IS NULL OR object_id = :v_id). --//注意不要被里面:b1,:b2之类绑定变量名字迷惑，非常容易误解。oracle重新命名的绑定变量名字，每行都是从B1开始。 --//另外oracle在上面的情况拆分存在问题，我的理解应该这样拆分。 P1 : :B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2 P2 : :B1 IS NULL P3 : "T1"."OBJECT_ID"=:B1 P4 : :B1 IS NULL OR "T1"."OBJECT_NAME"=:B2 --//再来看看加入OR_EXPAND(@"SEL$1" (1) (2))的情况。 SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" (1) (2))' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE SCOTT@book01p> @ hashz HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- ---------- ------------------- ----------- 1999013600 bs80ktjvkd1r0 0 34528 772686e0 2025-07-14 10:01:33 16777218 SCOTT@book01p> @ 10053x bs80ktjvkd1r0 0 /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3990_abs80ktjvkd1r0.trc SCOTT@book01p> @ 10053y '' TRCLINE ------------------------------------------------------------------------------------------------------------------------ Final query after transformations:******* UNPARSED QUERY IS ******* SELECT /*+ (1) (2)) */ "T1"."OBJECT_ID" "OBJECT_ID","T1"."OBJECT_NAME" "OBJECT_NAME","T1"."OBJECT_TYPE" "OBJECT_TYPE" F ROM "SCOTT"."T1" "T1" WHERE (:B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2) AND (:B3 IS NULL OR "T1"."OBJECT_ID"=:B4) AND (:B 5 IS NULL OR "T1"."OBJECT_NAME"=:B6) --//转换后注解部分丢失OR_EXPAND。里面的绑定变量被替换为:Bn之类的变量名，注意这里转换后，在执行计划的Peeked Binds --//(identified by position):部分看不见实际的绑定变量值。可以查看前面的执行计划。 --//查看跟踪文件内容： ORE: Predicate list P1 : :B1 IS NULL P2 : "T1"."DATA_OBJECT_ID"=:B1 P3 : :B1 IS NULL P4 : "T1"."OBJECT_ID"=:B1 P5 : :B1 IS NULL P6 : "T1"."OBJECT_NAME"=:B1 DNF Matrix (Before sorting OR branches) P1 P2 P3 P4 P5 P6 CNJ (#1) : 1 0 1 0 1 0 CNJ (#2) : 1 0 1 0 0 1 CNJ (#3) : 1 0 0 1 1 0 CNJ (#4) : 1 0 0 1 0 1 CNJ (#5) : 0 1 1 0 1 0 CNJ (#6) : 0 1 1 0 0 1 CNJ (#7) : 0 1 0 1 1 0 CNJ (#8) : 0 1 0 1 0 1 ORE: Predicate list P1 : :B1 IS NULL P2 : "T1"."DATA_OBJECT_ID"=:B1 P3 : :B1 IS NULL P4 : "T1"."OBJECT_ID"=:B1 P5 : :B1 IS NULL P6 : "T1"."OBJECT_NAME"=:B1 DNF Matrix (After OR branch sorting) P1 P2 P3 P4 P5 P6 CNJ (#1) : 1 0 1 0 1 0 CNJ (#2) : 1 0 1 0 0 1 CNJ (#3) : 1 0 0 1 1 0 CNJ (#4) : 1 0 0 1 0 1 CNJ (#5) : 0 1 1 0 1 0 CNJ (#6) : 0 1 1 0 0 1 CNJ (#7) : 0 1 0 1 1 0 CNJ (#8) : 0 1 0 1 0 1 --//oracle仅仅拆分8种情况，在这样的情况下提示变成无效,选择全表扫描。 --//也就是在这样的情况直接使用OR_EXPAND(@"SEL$1" (1) (2)))提示无效。 --//而使用提示 OR_EXPAND(@"SEL$1" (1) (2) (3) (4) (5) (6) (7) (8) )有效。执行计划不再贴出。 --//oracle在处理这类or的优化时存在一些问题，后面的参数仅仅表示拆分的数量。 5.继续： --//如果写成如下： $ cat g1.txt set term off variable v_id number ; variable v_did number ; variable v_name varchar2(32) ; exec :v_id := 76191; exec :v_did := 76191; exec :v_name := 'DEPT'; set term on SELECT /*+ &&1 */ t1.object_id ,t1.object_name ,t1.object_type FROM t1 WHERE -- ( ( :v_did = '' OR :v_did IS NULL) OR data_object_id = :v_did) --and ( ( :v_id = '' OR :v_id IS NULL) OR object_id = :v_id) ( ( :v_id = '' OR :v_id IS NULL) OR object_id = :v_id) and ( ( :v_did = '' OR :v_did IS NULL) OR data_object_id = :v_did) and ( ( :v_name = '' OR :v_name IS NULL) OR object_name = :v_name) ; --//原来写在中间的查询条件放在前面。 SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" )' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE SCOTT@book01p> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2072084780 5kftshxxs309c 0 98604 3861657155 7b81812c 2025-07-14 15:59:20 16777218 --//执行计划如下： Plan hash value: 3861657155 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 416 (100)| | 1 |00:00:00.01 | 4 | | 1 | VIEW | VW_ORE_BA8ECEFB | 1 | 177 | 16284 | 416 (1)| 00:00:01 | 1 |00:00:00.01 | 4 | | 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 4 | TABLE ACCESS FULL | T1 | 0 | 176 | 9328 | 414 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 5 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 53 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 7 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | ---------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$2A13AF86 / "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB" 2 - SET$2A13AF86 3 - SET$2A13AF86_1 4 - SET$2A13AF86_1 / "T1"@"SET$2A13AF86_1" 5 - SET$2A13AF86_2 6 - SET$2A13AF86_2 / "T1"@"SET$2A13AF86_2" 7 - SET$2A13AF86_2 / "T1"@"SET$2A13AF86_2" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$2A13AF86_2") OUTLINE_LEAF(@"SET$2A13AF86_1") OUTLINE_LEAF(@"SET$2A13AF86") OUTLINE_LEAF(@"SEL$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~ OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$9162BF3C" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") FULL(@"SET$2A13AF86_1" "T1"@"SET$2A13AF86_1") INDEX_RS_ASC(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:V_ID IS NULL) 4 - filter(((:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID) AND (:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME))) 5 - filter(LNNVL(:V_ID IS NULL)) 6 - filter(((:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID) AND (:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME))) 7 - access("OBJECT_ID"=:V_ID) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 5 - SET$2A13AF86_2 - OR_EXPAND(@"SEL$1" ) --//注意看下划线，OR_EXPAND(@"SEL$1" (1) (2))。如果再次执行采用OR_EXPAND(@"SEL$1" (1) (2))提示,执行计划还是选择全表扫描。 SCOTT@book01p> @ 10053x 5kftshxxs309c 0 /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3640_a5kftshxxs309c.trc SCOTT@book01p> @ 10053y '' TRCLINE ------------------------------------------------------------------------------------------------------------------------ Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "OBJECT_ID","VW_ORE_BA8ECEFB"."ITEM_2" "OBJECT_NAME","VW_ORE_BA8ECEFB"."ITEM_3" "OBJEC T_TYPE" FROM ( (SELECT "T1"."OBJECT_ID" "ITEM_1","T1"."OBJECT_NAME" "ITEM_2","T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"." T1" "T1" WHERE :B1 IS NULL AND (:B2 IS NULL OR "T1"."DATA_OBJECT_ID"=:B3) AND (:B4 IS NULL OR "T1"."OBJECT_NAME"=:B5)) U NION ALL (SELECT "T1"."OBJECT_ID" "ITEM_1","T1"."OBJECT_NAME" "ITEM_2","T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"."T1" "T 1" WHERE "T1"."OBJECT_ID"=:B6 AND (:B7 IS NULL OR "T1"."DATA_OBJECT_ID"=:B8) AND (:B9 IS NULL OR "T1"."OBJECT_NAME"=:B10 ) AND LNNVL(:B11 IS NULL))) "VW_ORE_BA8ECEFB" --//格式化如下： SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "OBJECT_ID" ,"VW_ORE_BA8ECEFB"."ITEM_2" "OBJECT_NAME" ,"VW_ORE_BA8ECEFB"."ITEM_3" "OBJECT_TYPE" FROM ( (SELECT "T1"."OBJECT_ID" "ITEM_1", "T1"."OBJECT_NAME" "ITEM_2", "T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE :B1 IS NULL AND ( :B2 IS NULL OR "T1"."DATA_OBJECT_ID" = :B3) AND ( :B4 IS NULL OR "T1"."OBJECT_NAME" = :B5)) UNION ALL (SELECT "T1"."OBJECT_ID" "ITEM_1", "T1"."OBJECT_NAME" "ITEM_2", "T1"."OBJECT_TYPE" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE "T1"."OBJECT_ID" = :B6 AND ( :B7 IS NULL OR "T1"."DATA_OBJECT_ID" = :B8) AND ( :B9 IS NULL OR "T1"."OBJECT_NAME" = :B10) AND LNNVL ( :B11 IS NULL))) "VW_ORE_BA8ECEFB" --//查看跟踪文件内容： ORE: Predicate list P1 : :B1 IS NULL P2 : "T1"."OBJECT_ID"=:B1 P3 : (:B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2) AND (:B3 IS NULL OR "T1"."OBJECT_NAME"=:B4) P4 : :B1 IS NULL OR "T1"."OBJECT_NAME"=:B2 DNF Matrix (Before sorting OR branches) P1 P2 P3 P4 CNJ (#1) : 1 0 1 1 CNJ (#2) : 0 1 1 1 ORE: Predicate list P1 : :B1 IS NULL P2 : "T1"."OBJECT_ID"=:B1 P3 : (:B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2) AND (:B3 IS NULL OR "T1"."OBJECT_NAME"=:B4) P4 : :B1 IS NULL OR "T1"."OBJECT_NAME"=:B2 DNF Matrix (After OR branch sorting) P1 P2 P3 P4 CNJ (#1) : 1 0 1 1 CNJ (#2) : 0 1 1 1 --//拆分确实不好理解为什么这样操作。 6.小结： --//使用or_expand提示优化要做一些尝试，如果建立data_object_id索引，object_name索引，可能会发生执行计划的变化。 --//另外无法使用sql profile交换的方式控制执行计划，除非修改里面的提示OR_EXPAND(@"SEL$1" )。 --//使用sql patch的方式情况也是类似，像前面的情况如果OR_EXPAND(@"SEL$1" (1) (2) )打补丁，执行计划选择全表扫描。 --//如果建立如下索引： create index i_t1_data_object_id on t1(data_object_id); SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" )' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE --//执行计划如下： Plan hash value: 546662753 -------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 420 (100)| | 1 |00:00:00.01 | 4 | | 1 | VIEW | VW_ORE_BA8ECEFB | 1 | 3512 | 315K| 420 (1)| 00:00:01 | 1 |00:00:00.01 | 4 | | 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 4 | TABLE ACCESS FULL | T1 | 0 | 3509 | 174K| 414 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 5 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 53 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 7 | INDEX RANGE SCAN | I_T1_DATA_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 8 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 53 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 10 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 11 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 53 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 13 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | -------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$171C43EC / "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB" 2 - SET$171C43EC 3 - SET$171C43EC_1 4 - SET$171C43EC_1 / "T1"@"SET$171C43EC_1" 5 - SET$171C43EC_2 6 - SET$171C43EC_2 / "T1"@"SET$171C43EC_2" 7 - SET$171C43EC_2 / "T1"@"SET$171C43EC_2" 8 - SET$171C43EC_3 9 - SET$171C43EC_3 / "T1"@"SET$171C43EC_3" 10 - SET$171C43EC_3 / "T1"@"SET$171C43EC_3" 11 - SET$171C43EC_4 12 - SET$171C43EC_4 / "T1"@"SET$171C43EC_4" 13 - SET$171C43EC_4 / "T1"@"SET$171C43EC_4" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$171C43EC_4") OUTLINE_LEAF(@"SET$171C43EC_3") OUTLINE_LEAF(@"SET$171C43EC_2") OUTLINE_LEAF(@"SET$171C43EC_1") OUTLINE_LEAF(@"SET$171C43EC") OUTLINE_LEAF(@"SEL$47D9A6EC") OR_EXPAND(@"SEL$1" (1) (2) (3) (4)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$47D9A6EC" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") FULL(@"SET$171C43EC_1" "T1"@"SET$171C43EC_1") INDEX_RS_ASC(@"SET$171C43EC_2" "T1"@"SET$171C43EC_2" ("T1"."DATA_OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_2" "T1"@"SET$171C43EC_2") INDEX_RS_ASC(@"SET$171C43EC_3" "T1"@"SET$171C43EC_3" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_3" "T1"@"SET$171C43EC_3") INDEX_RS_ASC(@"SET$171C43EC_4" "T1"@"SET$171C43EC_4" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_4" "T1"@"SET$171C43EC_4") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter((:V_DID IS NULL AND :V_ID IS NULL)) 4 - filter((:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME)) 5 - filter((LNNVL(:V_DID IS NULL) AND :V_ID IS NULL)) 6 - filter((:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME)) 7 - access("DATA_OBJECT_ID"=:V_DID) 8 - filter((LNNVL(:V_ID IS NULL) AND :V_DID IS NULL)) 9 - filter((:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME)) 10 - access("OBJECT_ID"=:V_ID) 11 - filter((LNNVL(:V_DID IS NULL) AND LNNVL(:V_ID IS NULL))) 12 - filter(("DATA_OBJECT_ID"=:V_DID AND (:V_NAME IS NULL OR "OBJECT_NAME"=:V_NAME))) 13 - access("OBJECT_ID"=:V_ID) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 11 - SET$171C43EC_4 - OR_EXPAND(@"SEL$1" ) --//而执行使用提示： SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" (1) (2) (3) (4) )' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE --//执行计划如下： Plan hash value: 523270132 -------------------------------------------------------------------------------------------------------------------------------------------------- | Id |Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1 | | | 831 (100)| | 1 |00:00:00.01 | 4 | | 1| VIEW | VW_ORE_BA8ECEFB | 1 | 3512 | 315K| 831 (1)| 00:00:01 | 1 |00:00:00.01 | 4 | | 2| UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 3| FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 4| TABLE ACCESS FULL | T1 | 0 | 3509 | 181K| 414 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 5| FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 6| TABLE ACCESS FULL | T1 | 0 | 1 | 53 | 414 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 7| FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 8| TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 53 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 9| INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 10| FILTER | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 11| TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 53 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 12| INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | -------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$171C43EC / "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB" 2 - SET$171C43EC 3 - SET$171C43EC_1 4 - SET$171C43EC_1 / "T1"@"SET$171C43EC_1" 5 - SET$171C43EC_2 6 - SET$171C43EC_2 / "T1"@"SET$171C43EC_2" 7 - SET$171C43EC_3 8 - SET$171C43EC_3 / "T1"@"SET$171C43EC_3" 9 - SET$171C43EC_3 / "T1"@"SET$171C43EC_3" 10 - SET$171C43EC_4 11 - SET$171C43EC_4 / "T1"@"SET$171C43EC_4" 12 - SET$171C43EC_4 / "T1"@"SET$171C43EC_4" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$171C43EC_4") OUTLINE_LEAF(@"SET$171C43EC_3") OUTLINE_LEAF(@"SET$171C43EC_2") OUTLINE_LEAF(@"SET$171C43EC_1") OUTLINE_LEAF(@"SET$171C43EC") OUTLINE_LEAF(@"SEL$47D9A6EC") OR_EXPAND(@"SEL$1" (1) (2) (3) (4)) OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$47D9A6EC" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") FULL(@"SET$171C43EC_1" "T1"@"SET$171C43EC_1") FULL(@"SET$171C43EC_2" "T1"@"SET$171C43EC_2") INDEX_RS_ASC(@"SET$171C43EC_3" "T1"@"SET$171C43EC_3" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_3" "T1"@"SET$171C43EC_3") INDEX_RS_ASC(@"SET$171C43EC_4" "T1"@"SET$171C43EC_4" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_4" "T1"@"SET$171C43EC_4") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter((:V_NAME IS NULL AND :V_ID IS NULL)) 4 - filter((:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID)) 5 - filter((LNNVL(:V_NAME IS NULL) AND :V_ID IS NULL)) 6 - filter(("OBJECT_NAME"=:V_NAME AND (:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID))) 7 - filter((LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL)) 8 - filter((:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID)) 9 - access("OBJECT_ID"=:V_ID) 10 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_ID IS NULL))) 11 - filter(("OBJECT_NAME"=:V_NAME AND (:V_DID IS NULL OR "DATA_OBJECT_ID"=:V_DID))) 12 - access("OBJECT_ID"=:V_ID) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 10 - SET$171C43EC_4 - OR_EXPAND(@"SEL$1" (1) (2) (3) (4) ) --//前后对比，可以发现实际上2个执行计划完全不同，前面会使用I_T1_DATA_OBJECT_ID索引，而后者根本不会。 --//虽然outline记录的提示都是OR_EXPAND(@"SEL$1" (1) (2) (3) (4) )。 --//有点无法理解的是这样情况下使用OR_EXPAND(@"SEL$1" (1) (2) (3) (4) (5) (6) (7) (8) )提示，执行计划又选择全表扫描。 --//查看10053跟踪文件： ORE: Predicate list P1 : :B1 IS NULL P2 : "T1"."OBJECT_ID"=:B1 P3 : (:B1 IS NULL OR "T1"."DATA_OBJECT_ID"=:B2) AND (:B3 IS NULL OR "T1"."OBJECT_NAME"=:B4) P4 : :B1 IS NULL P5 : "T1"."OBJECT_NAME"=:B1 DNF Matrix (After OR branch sorting) P1 P2 P3 P4 P5 CNJ (#1) : 1 0 1 1 0 CNJ (#2) : 1 0 1 0 1 CNJ (#3) : 0 1 1 1 0 CNJ (#4) : 0 1 1 0 1 --//总之，在优化使用提示or_expand提示时注意这些细节，即使使用sql patch也可能在不小心导致执行计划发生变化，无法很好的控制 --//执行计划。 --//最后建议开发还是少使用这些所谓的技巧，少写这类风格的sql语句，在我看来这类sql语句根本不允许出现在生产系统中。 --//你可以再尝试建立索引： --//create index i_t1_object_name on t1(object_name); SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" )' OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 76191 DEPT TABLE Plan hash value: 2061367055 ---------------------------------------------------------------------------------------------------------------------------------------- | Id |Operation |Name |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time |A-Rows| A-Time |Buffers| ---------------------------------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | | 430 (100)| | 1|00:00:00.01| 4| | 1| VIEW |VW_ORE_BA8ECEFB | 1| 70174| 6304K| 430 (1)|00:00:01| 1|00:00:00.01| 4| | 2| UNION-ALL | | 1| | | | | 1|00:00:00.01| 4| |* 3| FILTER | | 1| | | | | 0|00:00:00.01| 0| | 4| TABLE ACCESS FULL |T1 | 0| 70167| 3494K| 414 (1)|00:00:01| 0|00:00:00.01| 0| |* 5| FILTER | | 1| | | | | 0|00:00:00.01| 0| | 6| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 0| 1| 51 | 4 (0)|00:00:01| 0|00:00:00.01| 0| |* 7| INDEX RANGE SCAN |I_T1_OBJECT_NAME | 0| 1| | 3 (0)|00:00:01| 0|00:00:00.01| 0| |* 8| FILTER | | 1| | | | | 0|00:00:00.01| 0| | 9| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 0| 1| 53 | 2 (0)|00:00:01| 0|00:00:00.01| 0| |* 10| INDEX RANGE SCAN |I_T1_DATA_OBJECT_ID| 0| 1| | 1 (0)|00:00:01| 0|00:00:00.01| 0| |* 11| FILTER | | 1| | | | | 0|00:00:00.01| 0| |* 12| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 0| 1| 53 | 2 (0)|00:00:01| 0|00:00:00.01| 0| |* 13| INDEX RANGE SCAN |I_T1_DATA_OBJECT_ID| 0| 1| | 1 (0)|00:00:01| 0|00:00:00.01| 0| |* 14| FILTER | | 1| | | | | 0|00:00:00.01| 0| | 15| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 0| 1| 53 | 2 (0)|00:00:01| 0|00:00:00.01| 0| |* 16| INDEX RANGE SCAN |I_T1_OBJECT_ID | 0| 1| | 1 (0)|00:00:01| 0|00:00:00.01| 0| |* 17| FILTER | | 1| | | | | 0|00:00:00.01| 0| |* 18| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 0| 1| 53 | 2 (0)|00:00:01| 0|00:00:00.01| 0| |* 19| INDEX RANGE SCAN |I_T1_OBJECT_ID | 0| 1| | 1 (0)|00:00:01| 0|00:00:00.01| 0| |* 20| FILTER | | 1| | | | | 0|00:00:00.01| 0| |* 21| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 0| 1| 53 | 2 (0)|00:00:01| 0|00:00:00.01| 0| |* 22| INDEX RANGE SCAN |I_T1_OBJECT_ID | 0| 1| | 1 (0)|00:00:01| 0|00:00:00.01| 0| |* 23| FILTER | | 1| | | | | 1|00:00:00.01| 4| |* 24| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 1| 1| 53 | 2 (0)|00:00:01| 1|00:00:00.01| 4| |* 25| INDEX RANGE SCAN |I_T1_OBJECT_ID | 1| 1| | 1 (0)|00:00:01| 1|00:00:00.01| 3| ---------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$B2C621EA / "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB" 2 - SET$B2C621EA 3 - SET$B2C621EA_1 4 - SET$B2C621EA_1 / "T1"@"SET$B2C621EA_1" 5 - SET$B2C621EA_2 6 - SET$B2C621EA_2 / "T1"@"SET$B2C621EA_2" 7 - SET$B2C621EA_2 / "T1"@"SET$B2C621EA_2" 8 - SET$B2C621EA_3 9 - SET$B2C621EA_3 / "T1"@"SET$B2C621EA_3" 10 - SET$B2C621EA_3 / "T1"@"SET$B2C621EA_3" 11 - SET$B2C621EA_4 12 - SET$B2C621EA_4 / "T1"@"SET$B2C621EA_4" 13 - SET$B2C621EA_4 / "T1"@"SET$B2C621EA_4" 14 - SET$B2C621EA_5 15 - SET$B2C621EA_5 / "T1"@"SET$B2C621EA_5" 16 - SET$B2C621EA_5 / "T1"@"SET$B2C621EA_5" 17 - SET$B2C621EA_6 18 - SET$B2C621EA_6 / "T1"@"SET$B2C621EA_6" 19 - SET$B2C621EA_6 / "T1"@"SET$B2C621EA_6" 20 - SET$B2C621EA_7 21 - SET$B2C621EA_7 / "T1"@"SET$B2C621EA_7" 22 - SET$B2C621EA_7 / "T1"@"SET$B2C621EA_7" 23 - SET$B2C621EA_8 24 - SET$B2C621EA_8 / "T1"@"SET$B2C621EA_8" 25 - SET$B2C621EA_8 / "T1"@"SET$B2C621EA_8" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$B2C621EA_8") OUTLINE_LEAF(@"SET$B2C621EA_7") OUTLINE_LEAF(@"SET$B2C621EA_6") OUTLINE_LEAF(@"SET$B2C621EA_5") OUTLINE_LEAF(@"SET$B2C621EA_4") OUTLINE_LEAF(@"SET$B2C621EA_3") OUTLINE_LEAF(@"SET$B2C621EA_2") OUTLINE_LEAF(@"SET$B2C621EA_1") OUTLINE_LEAF(@"SET$B2C621EA") OUTLINE_LEAF(@"SEL$DC109C8D") OR_EXPAND(@"SEL$1" (1) (2) (3) (4) (5) (6) (7) (8)) OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$DC109C8D" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") FULL(@"SET$B2C621EA_1" "T1"@"SET$B2C621EA_1") INDEX_RS_ASC(@"SET$B2C621EA_2" "T1"@"SET$B2C621EA_2" ("T1"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$B2C621EA_2" "T1"@"SET$B2C621EA_2") INDEX_RS_ASC(@"SET$B2C621EA_3" "T1"@"SET$B2C621EA_3" ("T1"."DATA_OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$B2C621EA_3" "T1"@"SET$B2C621EA_3") INDEX_RS_ASC(@"SET$B2C621EA_4" "T1"@"SET$B2C621EA_4" ("T1"."DATA_OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$B2C621EA_4" "T1"@"SET$B2C621EA_4") INDEX_RS_ASC(@"SET$B2C621EA_5" "T1"@"SET$B2C621EA_5" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$B2C621EA_5" "T1"@"SET$B2C621EA_5") INDEX_RS_ASC(@"SET$B2C621EA_6" "T1"@"SET$B2C621EA_6" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$B2C621EA_6" "T1"@"SET$B2C621EA_6") INDEX_RS_ASC(@"SET$B2C621EA_7" "T1"@"SET$B2C621EA_7" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$B2C621EA_7" "T1"@"SET$B2C621EA_7") INDEX_RS_ASC(@"SET$B2C621EA_8" "T1"@"SET$B2C621EA_8" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$B2C621EA_8" "T1"@"SET$B2C621EA_8") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (NUMBER, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter((:V_NAME IS NULL AND :V_DID IS NULL AND :V_ID IS NULL)) 5 - filter((LNNVL(:V_NAME IS NULL) AND :V_DID IS NULL AND :V_ID IS NULL)) 7 - access("OBJECT_NAME"=:V_NAME) 8 - filter((LNNVL(:V_DID IS NULL) AND :V_NAME IS NULL AND :V_ID IS NULL)) 10 - access("DATA_OBJECT_ID"=:V_DID) 11 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_DID IS NULL) AND :V_ID IS NULL)) 12 - filter("OBJECT_NAME"=:V_NAME) 13 - access("DATA_OBJECT_ID"=:V_DID) 14 - filter((LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL AND :V_DID IS NULL)) 16 - access("OBJECT_ID"=:V_ID) 17 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_ID IS NULL) AND :V_DID IS NULL)) 18 - filter("OBJECT_NAME"=:V_NAME) 19 - access("OBJECT_ID"=:V_ID) 20 - filter((LNNVL(:V_DID IS NULL) AND LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL)) 21 - filter("DATA_OBJECT_ID"=:V_DID) 22 - access("OBJECT_ID"=:V_ID) 23 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_DID IS NULL) AND LNNVL(:V_ID IS NULL))) 24 - filter(("OBJECT_NAME"=:V_NAME AND "DATA_OBJECT_ID"=:V_DID)) 25 - access("OBJECT_ID"=:V_ID) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 23 - SET$B2C621EA_8 - OR_EXPAND(@"SEL$1" ) --//直接使用OR_EXPAND(@"SEL$1" (1) (2) (3) (4) (5) (6) (7) (8))，执行计划变成全表扫描。 --//不再展开分析。 7.附上测试使用的脚本： $ cat 10053x.sql set term off execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1'); set term on set head off @ t set head on define 1=&trc $ cat tpt-oracle-master/t.sql SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File'; $ cat 10053y.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. ------------------------------------------------------------------------------------------------------------ -- -- File name: 10053y.sql -- Purpose: display Final query after transformations -- -- Author: lfree -- -- Usage: -- @ 10053y <trc_file> -- --------------------------------------------------------------------------------------------------------- SET TERM OFF COLUMN trc_file NEW_VALUE v_trc_file --DEFINE trc_file = &1 SELECT NVL('&1','&TRC') trc_file FROM DUAL ; SELECT SUBSTR ('&v_trc_file', INSTR ('&v_trc_file', '/', -1) + 1) trc_file FROM DUAL; SET TERM ON DEFINE trc_file = &v_trc_file --DEFINE trc_file = &1 --COL trace_filename FOR A45 --COL adr_home FOR A45 --SELECT trace_filename, to_char(change_time, 'dd-mm-yyyy hh24:mi:ss') AS change_time, to_char(modify_time, 'dd-mm-yyyy hh24:mi:ss') AS modify_time, adr_home, con_id --FROM gv$diag_trace_file --WHERE lower(trace_filename) LIKE lower('%&v_trc_file%') --ORDER BY modify_time; column trcline format a120 SELECT trcline FROM gv$diag_trace_file_contents MATCH_RECOGNIZE ( PARTITION BY trace_filename ORDER BY line_number MEASURES payload AS trcline ALL ROWS PER MATCH PATTERN (a | b nc * | c | f n) DEFINE a AS (payload LIKE 'qksptfSQM_GetTxt(): Anonymous Block%') ,b AS (payload LIKE 'qksptfSQM_GetTxt(): Macro Text%') ,nc AS (payload NOT LIKE 'qksptfSQM_Template(): Template Text%') ,c AS (payload LIKE 'qksptfSQM_Template(): Template Text%') ,f AS (payload LIKE 'Final query after%') ) x WHERE trace_filename = '&v_trc_file';

编辑推荐：

返回列表

[20250714]使用or_expand提示遇到的疑惑.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推