[20250109]19c使用or_expand提示遇到的问题2.txt

来源：这里教程网时间：2026-03-03 21:17:28 作者：

[20250109]19c使用or_expand提示遇到的问题2.txt --//上午在21c下测试使用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 ; --//drop table t2 purge ; SCOTT@book01p> create table t1 as select * from all_objects; Table created. SCOTT@book01p> create table t2 as select * from all_objects; Table created. SCOTT@book01p> create index i_t1_object_id on t1(object_id); Index created. SCOTT@book01p> create index i_t2_object_id on t2(object_id); Index created. SCOTT@book01p> create index i_t1_object_name on t1(object_name); Index created. SCOTT@book01p> create index i_t2_object_name on t2(object_name); Index created. SCOTT@book01p> create index i_t2_CREATED on t2(CREATED ); Index created. $ cat f1.txt set term off variable v_id number ; variable v_id1 number ; variable v_name varchar2(20) ; variable startdate varchar2(32) ; variable enddate varchar2(32) ; exec :v_id := 76191; exec :v_name := NULL; exec :startdate := '2024-08-16 00:00:00' exec :enddate := '2024/08/17 00:00:00' set term on SELECT /*+ &&1 */ t1.object_name ,t2.object_name FROM t1 LEFT JOIN t2 ON t1.object_id = t2.object_id WHERE t2.object_type = 'TABLE' and ( ( :v_id = '' or :v_id is null) or t2.object_id = :v_id) AND ( ( :StartDate = '' OR :StartDate IS NULL) OR t2.CREATED >= :StartDate) AND ( ( :EndDate = '' OR :EndDate IS NULL) OR t2.CREATED <= :EndDate) and ( ( :v_name = '' or :v_name is null) or t2.object_name = :v_name) ; --//做一个接近生产系统的例子,真实的生产系统语句基本类似,注意出现LEFT JOIN,谓词条件在表T2上,使用use_concat提示根本不行。 3.测试： SCOTT@book01p> @ sl all alter session set statistics_level = all; Session altered. --//如果没有提示 SCOTT@book01p> @ f1.txt '' OBJECT_NAME OBJECT_NAME ------------------------------ ------------------------------ DEPT DEPT --//执行计划如下： Plan hash value: 1264319787 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 415 (100)| | 1 |00:00:00.01 | 1486 | | 1 | NESTED LOOPS | | 1 | 1 | 100 | 415 (1)| 00:00:01 | 1 |00:00:00.01 | 1486 | | 2 | NESTED LOOPS | | 1 | 1 | 100 | 415 (1)| 00:00:01 | 1 |00:00:00.01 | 1485 | |* 3 | TABLE ACCESS FULL | T2 | 1 | 1 | 59 | 413 (1)| 00:00:01 | 1 |00:00:00.01 | 1482 | |* 4 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | ----------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 3 - SEL$683367AA / "T2"@"SEL$1" 4 - SEL$683367AA / "T1"@"SEL$1" 5 - SEL$683367AA / "T1"@"SEL$1" Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (VARCHAR2(30), CSID=852, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) 12 - (VARCHAR2(30), CSID=852, Primary=10) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("T2"."OBJECT_TYPE"='TABLE' AND (:V_NAME IS NULL OR "T2"."OBJECT_NAME"=:V_NAME) AND (:V_ID IS NULL OR "T2"."OBJECT_ID"=:V_ID) AND (:STARTDATE IS NULL OR "T2"."CREATED">=:STARTDATE) AND ("T2"."CREATED"<=:ENDDATE OR :ENDDATE IS NULL))) 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") Note ----- - this is an adaptive plan 50 rows selected. SCOTT@book01p> @ f1.txt ' or_expand(@"SEL$683367AA")' OBJECT_NAME OBJECT_NAME ------------------------------ ------------------------------ DEPT DEPT --//注：提示里面的信息是Query Block Name，可以从前面的Query Block Name / Object Alias (identified by operation id)。 --//完整的执行计划如下： SCOTT@book01p> @ dpc '' outline '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 34bja5x794zy4, child number 0 ------------------------------------- SELECT /*+ or_expand(@"SEL$683367AA") */ t1.object_name ,t2.object_name FROM t1 LEFT JOIN t2 ON t1.object_id = t2.object_id WHERE t2.object_type = 'TABLE' and ( ( :v_id = '' or :v_id is null) or t2.object_id = :v_id) AND ( ( :StartDate = '' OR :StartDate IS NULL) OR t2.CREATED >= :StartDate) AND ( ( :EndDate = '' OR :EndDate IS NULL) OR t2.CREATED <= :EndDate) and ( ( :v_name = '' or :v_name is null) or t2.object_name = :v_name) ----------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1915 (100)| | 1 |00:00:00.01 | 7 | | 1 | VIEW | VW_ORE_37E917D2 | 1 | 3809 | 491K| 1915 (1)| 00:00:01 | 1 |00:00:00.01 | 7 | | 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 7 | |* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 4 | MERGE JOIN CARTESIAN | | 0 | 1 | 100 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 6 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 7 | BUFFER SORT | | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 8 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 9 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 10 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 7 | | 11 | MERGE JOIN CARTESIAN | | 1 | 1 | 100 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | |* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 13 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | 14 | BUFFER SORT | | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | 15 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 16 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 17 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 18 | MERGE JOIN CARTESIAN | | 0 | 1 | 100 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 19 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 20 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 21 | BUFFER SORT | | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 22 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 23 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 24 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 25 | MERGE JOIN CARTESIAN | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 26 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 27 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 28 | BUFFER SORT | | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 29 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 30 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 31 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 32 | MERGE JOIN CARTESIAN | | 0 | 1 | 100 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 33 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 34 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 35 | BUFFER SORT | | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 36 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 37 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 38 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 39 | MERGE JOIN CARTESIAN | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 40 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 41 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 42 | BUFFER SORT | | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 43 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 44 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 45 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 46 | MERGE JOIN CARTESIAN | | 0 | 1 | 100 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 47 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 48 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 49 | BUFFER SORT | | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 50 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 51 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 52 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 53 | MERGE JOIN CARTESIAN | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 54 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 55 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 56 | BUFFER SORT | | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 57 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 58 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 59 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 60 | NESTED LOOPS | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 61 | NESTED LOOPS | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 62 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 63 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 64 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 65 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 66 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 67 | NESTED LOOPS | | 0 | 5 | 500 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 68 | NESTED LOOPS | | 0 | 5 | 500 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 69 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 5 | 295 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 70 | INDEX RANGE SCAN | I_T2_CREATED | 0 | 174 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 71 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 72 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 73 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 74 | NESTED LOOPS | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 75 | NESTED LOOPS | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 76 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 77 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 78 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 79 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 80 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 81 | NESTED LOOPS | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 82 | NESTED LOOPS | | 0 | 1 | 100 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 83 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 84 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 85 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 86 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 87 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 88 | NESTED LOOPS | | 0 | 1 | 92 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 89 | NESTED LOOPS | | 0 | 1 | 92 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 90 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 51 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 91 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 92 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 93 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 94 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 95 | HASH JOIN | | 0 | 1894 | 170K| 826 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 96 | TABLE ACCESS FULL | T2 | 0 | 1894 | 96594 | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | 97 | TABLE ACCESS FULL | T1 | 0 | 70066 | 2805K| 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | 98 | NESTED LOOPS | | 1 | 1898 | 222K| 1029 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | 99 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 0 | | 100 | VIEW | VW_JF_SET$74F8F1A3 | 1 | 1898 | 146K| 616 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | 101 | UNION-ALL | | 1 | | | | | 0 |00:00:00.01 | 0 | |*102 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |*103 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 216 | 12744 | 203 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |*104 | INDEX RANGE SCAN | I_T2_CREATED | 0 | 8005 | | 23 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |*105 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |*106 | TABLE ACCESS FULL | T2 | 0 | 1682 | 99238 | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |*107 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | | | | | 0 |00:00:00.01 | 0 | | 108 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 41 | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$3F225E3E / "VW_ORE_37E917D2"@"SEL$37E917D2" 2 - SET$3F225E3E 3 - SET$2A79EB6C_16 5 - SET$2A79EB6C_16 / "T1"@"SET$2A79EB6C_16" 6 - SET$2A79EB6C_16 / "T1"@"SET$2A79EB6C_16" 8 - SET$2A79EB6C_16 / "T2"@"SET$2A79EB6C_16" 9 - SET$2A79EB6C_16 / "T2"@"SET$2A79EB6C_16" 10 - SET$2A79EB6C_15 12 - SET$2A79EB6C_15 / "T2"@"SET$2A79EB6C_15" 13 - SET$2A79EB6C_15 / "T2"@"SET$2A79EB6C_15" 15 - SET$2A79EB6C_15 / "T1"@"SET$2A79EB6C_15" 16 - SET$2A79EB6C_15 / "T1"@"SET$2A79EB6C_15" 17 - SET$2A79EB6C_14 19 - SET$2A79EB6C_14 / "T1"@"SET$2A79EB6C_14" 20 - SET$2A79EB6C_14 / "T1"@"SET$2A79EB6C_14" 22 - SET$2A79EB6C_14 / "T2"@"SET$2A79EB6C_14" 23 - SET$2A79EB6C_14 / "T2"@"SET$2A79EB6C_14" 24 - SET$2A79EB6C_13 26 - SET$2A79EB6C_13 / "T2"@"SET$2A79EB6C_13" 27 - SET$2A79EB6C_13 / "T2"@"SET$2A79EB6C_13" 29 - SET$2A79EB6C_13 / "T1"@"SET$2A79EB6C_13" 30 - SET$2A79EB6C_13 / "T1"@"SET$2A79EB6C_13" 31 - SET$2A79EB6C_12 33 - SET$2A79EB6C_12 / "T1"@"SET$2A79EB6C_12" 34 - SET$2A79EB6C_12 / "T1"@"SET$2A79EB6C_12" 36 - SET$2A79EB6C_12 / "T2"@"SET$2A79EB6C_12" 37 - SET$2A79EB6C_12 / "T2"@"SET$2A79EB6C_12" 38 - SET$2A79EB6C_11 40 - SET$2A79EB6C_11 / "T2"@"SET$2A79EB6C_11" 41 - SET$2A79EB6C_11 / "T2"@"SET$2A79EB6C_11" 43 - SET$2A79EB6C_11 / "T1"@"SET$2A79EB6C_11" 44 - SET$2A79EB6C_11 / "T1"@"SET$2A79EB6C_11" 45 - SET$2A79EB6C_10 47 - SET$2A79EB6C_10 / "T1"@"SET$2A79EB6C_10" 48 - SET$2A79EB6C_10 / "T1"@"SET$2A79EB6C_10" 50 - SET$2A79EB6C_10 / "T2"@"SET$2A79EB6C_10" 51 - SET$2A79EB6C_10 / "T2"@"SET$2A79EB6C_10" 52 - SET$2A79EB6C_9 54 - SET$2A79EB6C_9 / "T2"@"SET$2A79EB6C_9" 55 - SET$2A79EB6C_9 / "T2"@"SET$2A79EB6C_9" 57 - SET$2A79EB6C_9 / "T1"@"SET$2A79EB6C_9" 58 - SET$2A79EB6C_9 / "T1"@"SET$2A79EB6C_9" 59 - SET$2A79EB6C_8 62 - SET$2A79EB6C_8 / "T2"@"SET$2A79EB6C_8" 63 - SET$2A79EB6C_8 / "T2"@"SET$2A79EB6C_8" 64 - SET$2A79EB6C_8 / "T1"@"SET$2A79EB6C_8" 65 - SET$2A79EB6C_8 / "T1"@"SET$2A79EB6C_8" 66 - SET$2A79EB6C_7 69 - SET$2A79EB6C_7 / "T2"@"SET$2A79EB6C_7" 70 - SET$2A79EB6C_7 / "T2"@"SET$2A79EB6C_7" 71 - SET$2A79EB6C_7 / "T1"@"SET$2A79EB6C_7" 72 - SET$2A79EB6C_7 / "T1"@"SET$2A79EB6C_7" 73 - SET$2A79EB6C_6 76 - SET$2A79EB6C_6 / "T2"@"SET$2A79EB6C_6" 77 - SET$2A79EB6C_6 / "T2"@"SET$2A79EB6C_6" 78 - SET$2A79EB6C_6 / "T1"@"SET$2A79EB6C_6" 79 - SET$2A79EB6C_6 / "T1"@"SET$2A79EB6C_6" 80 - SET$2A79EB6C_4 83 - SET$2A79EB6C_4 / "T2"@"SET$2A79EB6C_4" 84 - SET$2A79EB6C_4 / "T2"@"SET$2A79EB6C_4" 85 - SET$2A79EB6C_4 / "T1"@"SET$2A79EB6C_4" 86 - SET$2A79EB6C_4 / "T1"@"SET$2A79EB6C_4" 87 - SET$2A79EB6C_2 90 - SET$2A79EB6C_2 / "T2"@"SET$2A79EB6C_2" 91 - SET$2A79EB6C_2 / "T2"@"SET$2A79EB6C_2" 92 - SET$2A79EB6C_2 / "T1"@"SET$2A79EB6C_2" 93 - SET$2A79EB6C_2 / "T1"@"SET$2A79EB6C_2" 94 - SET$2A79EB6C_1 96 - SET$2A79EB6C_1 / "T2"@"SET$2A79EB6C_1" 97 - SET$2A79EB6C_1 / "T1"@"SET$2A79EB6C_1" 98 - SEL$7225F299 100 - SET$74F8F1A3 / "VW_JF_SET$74F8F1A3"@"SEL$B2151C1A" 101 - SET$74F8F1A3 102 - SEL$4350D319 103 - SEL$4350D319 / "T2"@"SET$2A79EB6C_5" 104 - SEL$4350D319 / "T2"@"SET$2A79EB6C_5" 105 - SEL$A8E4AE42 106 - SEL$A8E4AE42 / "T2"@"SET$2A79EB6C_3" 107 - SEL$7225F299 / "T1"@"SET$2A79EB6C_5" 108 - SEL$7225F299 / "T1"@"SET$2A79EB6C_5" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SET$2A79EB6C_8" "T1"@"SET$2A79EB6C_8" ("T1"."OBJECT_ID")) NLJ_BATCHING(@"SET$2A79EB6C_8" "T1"@"SET$2A79EB6C_8") USE_NL(@"SET$2A79EB6C_8" "T1"@"SET$2A79EB6C_8") INDEX(@"SET$2A79EB6C_7" "T1"@"SET$2A79EB6C_7" ("T1"."OBJECT_ID")) NLJ_BATCHING(@"SET$2A79EB6C_7" "T1"@"SET$2A79EB6C_7") USE_NL(@"SET$2A79EB6C_7" "T1"@"SET$2A79EB6C_7") INDEX(@"SET$2A79EB6C_6" "T1"@"SET$2A79EB6C_6" ("T1"."OBJECT_ID")) NLJ_BATCHING(@"SET$2A79EB6C_6" "T1"@"SET$2A79EB6C_6") USE_NL(@"SET$2A79EB6C_6" "T1"@"SET$2A79EB6C_6") INDEX(@"SET$2A79EB6C_4" "T1"@"SET$2A79EB6C_4" ("T1"."OBJECT_ID")) NLJ_BATCHING(@"SET$2A79EB6C_4" "T1"@"SET$2A79EB6C_4") USE_NL(@"SET$2A79EB6C_4" "T1"@"SET$2A79EB6C_4") INDEX(@"SET$2A79EB6C_2" "T1"@"SET$2A79EB6C_2" ("T1"."OBJECT_ID")) NLJ_BATCHING(@"SET$2A79EB6C_2" "T1"@"SET$2A79EB6C_2") USE_NL(@"SET$2A79EB6C_2" "T1"@"SET$2A79EB6C_2") FULL(@"SET$2A79EB6C_1" "T1"@"SET$2A79EB6C_1") USE_HASH(@"SET$2A79EB6C_1" "T1"@"SET$2A79EB6C_1") INDEX(@"SEL$7225F299" "T1"@"SET$2A79EB6C_5" ("T1"."OBJECT_ID")) NLJ_BATCHING(@"SEL$7225F299" "T1"@"SET$2A79EB6C_5") USE_NL(@"SEL$7225F299" "T1"@"SET$2A79EB6C_5") IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$2A79EB6C_16") OUTLINE_LEAF(@"SET$2A79EB6C_15") OUTLINE_LEAF(@"SET$2A79EB6C_14") OUTLINE_LEAF(@"SET$2A79EB6C_13") OUTLINE_LEAF(@"SET$2A79EB6C_12") OUTLINE_LEAF(@"SET$2A79EB6C_11") OUTLINE_LEAF(@"SET$2A79EB6C_10") OUTLINE_LEAF(@"SET$2A79EB6C_9") OUTLINE_LEAF(@"SET$2A79EB6C_8") OUTLINE_LEAF(@"SET$2A79EB6C_7") OUTLINE_LEAF(@"SET$2A79EB6C_6") OUTLINE_LEAF(@"SET$2A79EB6C_4") OUTLINE_LEAF(@"SET$2A79EB6C_2") OUTLINE_LEAF(@"SET$2A79EB6C_1") OUTLINE_LEAF(@"SEL$A8E4AE42") OUTLINE_LEAF(@"SEL$4350D319") OUTLINE_LEAF(@"SET$74F8F1A3") OUTLINE_LEAF(@"SEL$7225F299") OUTLINE_LEAF(@"SET$3F225E3E") FACTORIZE_JOIN(@"SET$2A79EB6C"("T1"@"SET$2A79EB6C_5" "T1"@"SET$2A79EB6C_3")) OUTLINE_LEAF(@"SEL$828D8E7E") OR_EXPAND(@"SEL$683367AA" (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) (12) (13) (14) (15) (16)) OUTLINE(@"SET$2A79EB6C") OUTLINE(@"SEL$B8B95D22") OUTLINE(@"SEL$B2151C1A") OUTLINE(@"SEL$683367AA") OUTER_JOIN_TO_INNER(@"SEL$2BFA4EE4" "T2"@"SEL$1") OUTLINE(@"SET$2A79EB6C_3") OUTLINE(@"SET$2A79EB6C_5") OUTLINE(@"SEL$2BFA4EE4") MERGE(@"SEL$8812AA4E" >"SEL$948754D7") OUTLINE(@"SEL$948754D7") ANSI_REARCH(@"SEL$2") OUTLINE(@"SEL$8812AA4E") ANSI_REARCH(@"SEL$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$828D8E7E" "VW_ORE_37E917D2"@"SEL$37E917D2") NO_ACCESS(@"SEL$7225F299" "VW_JF_SET$74F8F1A3"@"SEL$B2151C1A") LEADING(@"SEL$7225F299" "VW_JF_SET$74F8F1A3"@"SEL$B2151C1A" "T1"@"SET$2A79EB6C_5") FULL(@"SET$2A79EB6C_1" "T2"@"SET$2A79EB6C_1") LEADING(@"SET$2A79EB6C_1" "T2"@"SET$2A79EB6C_1" "T1"@"SET$2A79EB6C_1") INDEX_RS_ASC(@"SET$2A79EB6C_2" "T2"@"SET$2A79EB6C_2" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_2" "T2"@"SET$2A79EB6C_2") LEADING(@"SET$2A79EB6C_2" "T2"@"SET$2A79EB6C_2" "T1"@"SET$2A79EB6C_2") INDEX_RS_ASC(@"SET$2A79EB6C_4" "T2"@"SET$2A79EB6C_4" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_4" "T2"@"SET$2A79EB6C_4") LEADING(@"SET$2A79EB6C_4" "T2"@"SET$2A79EB6C_4" "T1"@"SET$2A79EB6C_4") INDEX_RS_ASC(@"SET$2A79EB6C_6" "T2"@"SET$2A79EB6C_6" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_6" "T2"@"SET$2A79EB6C_6") LEADING(@"SET$2A79EB6C_6" "T2"@"SET$2A79EB6C_6" "T1"@"SET$2A79EB6C_6") INDEX_RS_ASC(@"SET$2A79EB6C_7" "T2"@"SET$2A79EB6C_7" ("T2"."CREATED")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_7" "T2"@"SET$2A79EB6C_7") LEADING(@"SET$2A79EB6C_7" "T2"@"SET$2A79EB6C_7" "T1"@"SET$2A79EB6C_7") INDEX_RS_ASC(@"SET$2A79EB6C_8" "T2"@"SET$2A79EB6C_8" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_8" "T2"@"SET$2A79EB6C_8") LEADING(@"SET$2A79EB6C_8" "T2"@"SET$2A79EB6C_8" "T1"@"SET$2A79EB6C_8") INDEX_RS_ASC(@"SET$2A79EB6C_9" "T2"@"SET$2A79EB6C_9" ("T2"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_9" "T2"@"SET$2A79EB6C_9") INDEX_RS_ASC(@"SET$2A79EB6C_9" "T1"@"SET$2A79EB6C_9" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_9" "T1"@"SET$2A79EB6C_9") LEADING(@"SET$2A79EB6C_9" "T2"@"SET$2A79EB6C_9" "T1"@"SET$2A79EB6C_9") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_9" "T1"@"SET$2A79EB6C_9") INDEX_RS_ASC(@"SET$2A79EB6C_10" "T1"@"SET$2A79EB6C_10" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_10" "T1"@"SET$2A79EB6C_10") INDEX_RS_ASC(@"SET$2A79EB6C_10" "T2"@"SET$2A79EB6C_10" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_10" "T2"@"SET$2A79EB6C_10") LEADING(@"SET$2A79EB6C_10" "T1"@"SET$2A79EB6C_10" "T2"@"SET$2A79EB6C_10") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_10" "T2"@"SET$2A79EB6C_10") INDEX_RS_ASC(@"SET$2A79EB6C_11" "T2"@"SET$2A79EB6C_11" ("T2"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_11" "T2"@"SET$2A79EB6C_11") INDEX_RS_ASC(@"SET$2A79EB6C_11" "T1"@"SET$2A79EB6C_11" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_11" "T1"@"SET$2A79EB6C_11") LEADING(@"SET$2A79EB6C_11" "T2"@"SET$2A79EB6C_11" "T1"@"SET$2A79EB6C_11") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_11" "T1"@"SET$2A79EB6C_11") INDEX_RS_ASC(@"SET$2A79EB6C_12" "T1"@"SET$2A79EB6C_12" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_12" "T1"@"SET$2A79EB6C_12") INDEX_RS_ASC(@"SET$2A79EB6C_12" "T2"@"SET$2A79EB6C_12" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_12" "T2"@"SET$2A79EB6C_12") LEADING(@"SET$2A79EB6C_12" "T1"@"SET$2A79EB6C_12" "T2"@"SET$2A79EB6C_12") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_12" "T2"@"SET$2A79EB6C_12") INDEX_RS_ASC(@"SET$2A79EB6C_13" "T2"@"SET$2A79EB6C_13" ("T2"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_13" "T2"@"SET$2A79EB6C_13") INDEX_RS_ASC(@"SET$2A79EB6C_13" "T1"@"SET$2A79EB6C_13" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_13" "T1"@"SET$2A79EB6C_13") LEADING(@"SET$2A79EB6C_13" "T2"@"SET$2A79EB6C_13" "T1"@"SET$2A79EB6C_13") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_13" "T1"@"SET$2A79EB6C_13") INDEX_RS_ASC(@"SET$2A79EB6C_14" "T1"@"SET$2A79EB6C_14" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_14" "T1"@"SET$2A79EB6C_14") INDEX_RS_ASC(@"SET$2A79EB6C_14" "T2"@"SET$2A79EB6C_14" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_14" "T2"@"SET$2A79EB6C_14") LEADING(@"SET$2A79EB6C_14" "T1"@"SET$2A79EB6C_14" "T2"@"SET$2A79EB6C_14") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_14" "T2"@"SET$2A79EB6C_14") INDEX_RS_ASC(@"SET$2A79EB6C_15" "T2"@"SET$2A79EB6C_15" ("T2"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_15" "T2"@"SET$2A79EB6C_15") INDEX_RS_ASC(@"SET$2A79EB6C_15" "T1"@"SET$2A79EB6C_15" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_15" "T1"@"SET$2A79EB6C_15") LEADING(@"SET$2A79EB6C_15" "T2"@"SET$2A79EB6C_15" "T1"@"SET$2A79EB6C_15") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_15" "T1"@"SET$2A79EB6C_15") INDEX_RS_ASC(@"SET$2A79EB6C_16" "T1"@"SET$2A79EB6C_16" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_16" "T1"@"SET$2A79EB6C_16") INDEX_RS_ASC(@"SET$2A79EB6C_16" "T2"@"SET$2A79EB6C_16" ("T2"."OBJECT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A79EB6C_16" "T2"@"SET$2A79EB6C_16") LEADING(@"SET$2A79EB6C_16" "T1"@"SET$2A79EB6C_16" "T2"@"SET$2A79EB6C_16") USE_MERGE_CARTESIAN(@"SET$2A79EB6C_16" "T2"@"SET$2A79EB6C_16") INDEX_RS_ASC(@"SEL$4350D319" "T2"@"SET$2A79EB6C_5" ("T2"."CREATED")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4350D319" "T2"@"SET$2A79EB6C_5") FULL(@"SEL$A8E4AE42" "T2"@"SET$2A79EB6C_3") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 3 - (NUMBER, Primary=1) 6 - (VARCHAR2(30), CSID=852, Primary=4) 9 - (VARCHAR2(30), CSID=852, Primary=7) 12 - (VARCHAR2(30), CSID=852, Primary=10) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter((TO_DATE(:ENDDATE)>=TO_DATE(:STARTDATE) AND LNNVL(:V_NAME IS NULL) AND LNNVL(:ENDDATE IS NULL) AND LNNVL(:STARTDATE IS NULL) AND LNNVL(:V_ID IS NULL))) 6 - access("T1"."OBJECT_ID"=:V_ID) 8 - filter(("T2"."OBJECT_ID"=:V_ID AND "T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED">=:STARTDATE AND "T2"."CREATED"<=:ENDDATE)) 9 - access("T2"."OBJECT_NAME"=:V_NAME) 10 - filter((TO_DATE(:ENDDATE)>=TO_DATE(:STARTDATE) AND LNNVL(:ENDDATE IS NULL) AND LNNVL(:STARTDATE IS NULL) AND LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL)) 12 - filter(("T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED">=:STARTDATE AND "T2"."CREATED"<=:ENDDATE)) 13 - access("T2"."OBJECT_ID"=:V_ID) 16 - access("T1"."OBJECT_ID"=:V_ID) 17 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:STARTDATE IS NULL) AND LNNVL(:V_ID IS NULL) AND :ENDDATE IS NULL)) 20 - access("T1"."OBJECT_ID"=:V_ID) 22 - filter(("T2"."OBJECT_ID"=:V_ID AND "T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED">=:STARTDATE)) 23 - access("T2"."OBJECT_NAME"=:V_NAME) 24 - filter((LNNVL(:STARTDATE IS NULL) AND LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL AND :ENDDATE IS NULL)) 26 - filter(("T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED">=:STARTDATE)) 27 - access("T2"."OBJECT_ID"=:V_ID) 30 - access("T1"."OBJECT_ID"=:V_ID) 31 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:ENDDATE IS NULL) AND LNNVL(:V_ID IS NULL) AND :STARTDATE IS NULL)) 34 - access("T1"."OBJECT_ID"=:V_ID) 36 - filter(("T2"."OBJECT_ID"=:V_ID AND "T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED"<=:ENDDATE)) 37 - access("T2"."OBJECT_NAME"=:V_NAME) 38 - filter((LNNVL(:ENDDATE IS NULL) AND LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL AND :STARTDATE IS NULL)) 40 - filter(("T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED"<=:ENDDATE)) 41 - access("T2"."OBJECT_ID"=:V_ID) 44 - access("T1"."OBJECT_ID"=:V_ID) 45 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_ID IS NULL) AND :ENDDATE IS NULL AND :STARTDATE IS NULL)) 48 - access("T1"."OBJECT_ID"=:V_ID) 50 - filter(("T2"."OBJECT_ID"=:V_ID AND "T2"."OBJECT_TYPE"='TABLE')) 51 - access("T2"."OBJECT_NAME"=:V_NAME) 52 - filter((LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL AND :ENDDATE IS NULL AND :STARTDATE IS NULL)) 54 - filter("T2"."OBJECT_TYPE"='TABLE') 55 - access("T2"."OBJECT_ID"=:V_ID) 58 - access("T1"."OBJECT_ID"=:V_ID) 59 - filter((TO_DATE(:ENDDATE)>=TO_DATE(:STARTDATE) AND LNNVL(:V_NAME IS NULL) AND LNNVL(:ENDDATE IS NULL) AND LNNVL(:STARTDATE IS NULL) AND :V_ID IS NULL)) 62 - filter(("T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED">=:STARTDATE AND "T2"."CREATED"<=:ENDDATE)) 63 - access("T2"."OBJECT_NAME"=:V_NAME) 64 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 66 - filter((TO_DATE(:ENDDATE)>=TO_DATE(:STARTDATE) AND LNNVL(:ENDDATE IS NULL) AND LNNVL(:STARTDATE IS NULL) AND :V_NAME IS NULL AND :V_ID IS NULL)) 69 - filter("T2"."OBJECT_TYPE"='TABLE') 70 - access("T2"."CREATED">=:STARTDATE AND "T2"."CREATED"<=:ENDDATE) 71 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 73 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:STARTDATE IS NULL) AND :ENDDATE IS NULL AND :V_ID IS NULL)) 76 - filter(("T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED">=:STARTDATE)) 77 - access("T2"."OBJECT_NAME"=:V_NAME) 78 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 80 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:ENDDATE IS NULL) AND :STARTDATE IS NULL AND :V_ID IS NULL)) 83 - filter(("T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED"<=:ENDDATE)) 84 - access("T2"."OBJECT_NAME"=:V_NAME) 85 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 87 - filter((LNNVL(:V_NAME IS NULL) AND :ENDDATE IS NULL AND :STARTDATE IS NULL AND :V_ID IS NULL)) 90 - filter("T2"."OBJECT_TYPE"='TABLE') 91 - access("T2"."OBJECT_NAME"=:V_NAME) 92 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 94 - filter((:V_NAME IS NULL AND :ENDDATE IS NULL AND :STARTDATE IS NULL AND :V_ID IS NULL)) 95 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 96 - filter("T2"."OBJECT_TYPE"='TABLE') 102 - filter((:V_ID IS NULL AND :ENDDATE IS NULL AND :V_NAME IS NULL AND LNNVL(:STARTDATE IS NULL))) 103 - filter("T2"."OBJECT_TYPE"='TABLE') 104 - access("T2"."CREATED">=:STARTDATE) 105 - filter((:V_ID IS NULL AND :STARTDATE IS NULL AND :V_NAME IS NULL AND LNNVL(:ENDDATE IS NULL))) 106 - filter(("T2"."OBJECT_TYPE"='TABLE' AND "T2"."CREATED"<=:ENDDATE)) 107 - access("T1"."OBJECT_ID"="ITEM_1") Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 3 - SET$2A79EB6C_16 - or_expand(@"SEL$683367AA") Note ----- - this is an adaptive plan 298 rows selected. --//真正使用or_expand的具体内容是OR_EXPAND(@"SEL$683367AA" (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) (12) (13) (14) (15) (16)) --//现在有点明白为什么使用我写的spsw.sql交换执行计划不行，因为这部分内容会变化，只能使用sql patch方式稳定执行计划。 --//如果删除CREATED索引。 SCOTT@book01p> drop index i_t2_CREATED ; Index dropped. --//执行计划就没有这么多展开。 Plan hash value: 4157235882 --------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 832 (100)| | 1 |00:00:00.01 | 7 | | 1 | NESTED LOOPS | | 1 | 271 | 32520 | 832 (1)| 00:00:01 | 1 |00:00:00.01 | 7 | | 2 | NESTED LOOPS | | 1 | | | | | 1 |00:00:00.01 | 6 | | 3 | VIEW | VW_JF_SET$200A9920 | 1 | 271 | 21409 | 419 (1)| 00:00:01 | 1 |00:00:00.01 | 3 | | 4 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 3 | |* 5 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 7 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 8 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 3 | |* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 59 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 10 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | |* 11 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 1 | 59 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 13 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 14 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 15 | TABLE ACCESS FULL | T2 | 0 | 268 | 15812 | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 16 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | | | | | 1 |00:00:00.01 | 3 | | 17 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 41 | 412 (1)| 00:00:01 | 1 |00:00:00.01 | 1 | --------------------------------------------------------------------------------------------------------------------------------------------------------- --//or_expand的提示变成 OR_EXPAND(@"SEL$683367AA" (1) (2) (3) (4))。 --//似乎oracle根据索引展开执行计划。 SCOTT@book01p> drop index i_t2_object_name ; Index dropped. Plan hash value: 620923136 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 443 (100)| | 1 |00:00:00.01 | 7 | | 1 | VIEW | VW_ORE_37E917D2 | 1 | 14 | 1848 | 443 (1)| 00:00:01 | 1 |00:00:00.01 | 7 | | 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 7 | |* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 4 | NESTED LOOPS | | 0 | 13 | 1300 | 439 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | 5 | NESTED LOOPS | | 0 | 13 | 1300 | 439 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 6 | TABLE ACCESS FULL | T2 | 0 | 13 | 767 | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 7 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 8 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 41 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 9 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 7 | | 10 | MERGE JOIN CARTESIAN | | 1 | 1 | 100 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | |* 11 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 12 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | 13 | BUFFER SORT | | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | 14 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 15 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------------------------------------------------------ --//or_expand的提示变成 OR_EXPAND(@"SEL$683367AA" (1) (2) )。

编辑推荐：

返回列表

[20250109]19c使用or_expand提示遇到的问题2.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推