[20250109]19c使用or_expand提示遇到的问题2.txt
[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) )。
--//上午在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) )。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版