[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) )。




posted @   lfree  阅读(2)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
点击右上角即可分享
微信分享提示