"the hash join just cannot do what you want it to do. It has NO idea before it
begins what partition MIGHT be involved -- you are making it hash the two tables
and join them -- therefore, it has to do the entire thing"


Tom,

I did not understand the above statement? what does it mean?

according to oracle document: 179518.1

In 8.1.6 the code was enhanced to allow elimination to occur with a hash join. It was recognized that the non-partitioned table contains the information to eliminate partitions. If this information is compared with the dictionary information regarding partition ranges, then a list of partitions to be accessed can be generated. This activity only occurs if certain cost and selectivity criteria are met. The elimination IS NOT done in the join step itself, rather it is performed as recursive SQL independently of the join itself and therefore elimination is not join order dependant. In this case the following plan can be seen:



The example which is given does a partition elimination for a hash join. I tried that example. It works. However for my case it does not. Might be because all my partitions belong to same tablespace ????????

Tom,

I tried that every possible option. But cannot get it to work in my case (using hash join to eliminate partition).

I will post the use_NL plan on this page after sometime.

Thanks for all the help.

Tom,


Please look at the below results:

First the NL query path.

Q1) why did it eliminate the partitions in case of nested loop?

Plan Table
--------------------------------------------------------------------------------
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------

| SELECT STATEMENT           |          |   999K|  263M|     36M|      |      |
           |       |       |

|  NESTED LOOPS              |          |   999K|  263M|     36M|      |      |
           |       |       |

Plan Table
--------------------------------------------------------------------------------

|   TABLE ACCESS FULL        |SUBMISSIO |     9K|    1M|     29 |      |      |
           |       |       |

|   PARTITION RANGE ITERATOR |          |       |      |        |      |      |
           |   KEY |   KEY |

|    TABLE ACCESS FULL       |SUBMISSIO |     2M|  443M|   3995 |      |      |
           |   KEY |   KEY |

--------------------------------------------------------------------------------

Plan Table
--------------------------------------------------------------------------------
----------------------------


8 rows selected.           



Q2)   select column_name, num_distinct from user_tab_columns
SQL> /
DEPTNO                                    4
DNAME                                     4
LOC                                       4


SQL> alter session set "_subquery_pruning_cost_factor"=1;

Session altered.

SQL> alter session set "_subquery_pruning_reduction"=100;

Session altered.

SQL>
explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';SQL>   2    3    4    5

Explained.

SQL> @?/rdbms/admin/utlxplp.sql

Plan Table
--------------------------------------------------------------------------------
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------

| SELECT STATEMENT           |          |     1 |   22 |      3 |      |      |
           |       |       |

|  HASH JOIN                 |          |     1 |   22 |      3 |      |      |
           |       |       |

Plan Table
--------------------------------------------------------------------------------

|   TABLE ACCESS FULL        |DEPT      |     1 |    9 |      1 |      |      |
           |       |       |

|   PARTITION RANGE ITERATOR |          |       |      |        |      |      |
           |   KEY |   KEY |

|    TABLE ACCESS FULL       |RANGE_PAR |    84 |    1K|      1 |      |      |
           |   KEY |   KEY |

--------------------------------------------------------------------------------

Plan Table
--------------------------------------------------------------------------------
----------------------------

    
*** 2002-12-25 21:56:10.634
*** SESSION ID:(10.437) 2002-12-25 21:56:10.630
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = SQL {count= 1, text = SELECT distinct TBL$OR$IDX$PART$NUM("RANGE_P
ART", 0,  "COL1") FROM (SELECT "D"."DEPTNO" COL1 FROM "DEPT" "D" WHERE "D"."LOC"
='DALLAS') ORDER BY 1}
   index = 0
  current partition: part# = 2, subp# = 65535, abs# = 2 



SQL> insert into dept values(50,'RESEARCH','OPERATIONS');

1 row created.

SQL> commit;

Commit complete.

SQL> analyze table dept compute statistics;

Table analyzed.

SQL> alter session set "_subquery_pruning_cost_factor"=1;

Session altered.

SQL>  alter session set "_subquery_pruning_reduction"=100;

Session altered.

SQL> select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';  2    3    4

no rows selected

SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';  2    3    4    5

Explained.

SQL> @?/rdbms/admin/utlxplp.sql

Plan Table
--------------------------------------------------------------------------------
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------

| SELECT STATEMENT           |          |     1 |   23 |      3 |      |      |
           |       |       |

|  HASH JOIN                 |          |     1 |   23 |      3 |      |      |
           |       |       |

Plan Table
--------------------------------------------------------------------------------

|   TABLE ACCESS FULL        |DEPT      |     1 |   10 |      1 |      |      |
           |       |       |

|   PARTITION RANGE ITERATOR |          |       |      |        |      |      |
           |   KEY |   KEY |

|    TABLE ACCESS FULL       |RANGE_PAR |    84 |    1K|      1 |      |      |
           |   KEY |   KEY |

--------------------------------------------------------------------------------

Plan Table
--------------------------------------------------------------------------------
----------------------------


8 rows selected.               


why did the optimizer do a partition elimination in this case when using hash join?


q3)  I have 

 select column_name, num_distinct from user_tab_columns
  2  where table_name='SUBMISSION_FILE'
  3  and column_name in ('DT_CR','LATEST_STATUS_CHANGE_DT');

DT_CR                                  9120
LATEST_STATUS_CHANGE_DT                9120

We get around 10000 submission files and around 8 million transaction details associated with those submission files.
partition key is dt_cr.  But the biggest problem is
DT_CR on submission files and transaction details can be same or have a difference of 1 day between them. The files get there status changed in between and for reporting purposes we have to look at latest_status_change_dt and not dt_cr. So how can we write queries so that it does partition elimination when doing a join? NL will not help ...


Thanks
 

q1) you have to think about how NL's are *done*

for x in ( select * from outer_table )
loop
    do some query against inner table
end loop

vs how a hash join is done


hash t1
hash t2
put them together


totally -- very different logic....  It is trivial to use partition elimination against the inner table using NL join - it is like running a correlated subquery.  hash operations are "en-mass", bulk operations.  We would have to:

select distinct dt_cr from outer table
rewrite query against inner table to say "where dt_cr = :x or dt_cr = :y...."

sometimes we do, sometimes we say "not worth it"



q2) because your artificially induced it to with the undocumented parameters.  There isn't enough data in any of those tables to warrant what work we are doing unless you artificially make it do it that way.  You have no data, no stats maybe even -- worth doing this extra work for.


q3) Do you have STATS on these objects??? Here is my test case:


ops$tkyte@ORA817DEV> drop table small_table;

Table dropped.

ops$tkyte@ORA817DEV> drop table big_table;

Table dropped.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table small_table ( latest_status_dt date, dt_cr date, subm_id number );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table big_table ( dt_cr date, subm_id number, data char(255) )
  2  partition by range( dt_cr )
  3  ( partition p01 values less than (to_date('01012002','ddmmyyyy') ),
  4    partition p02 values less than (to_date('01022002','ddmmyyyy') ),
  5    partition p03 values less than (to_date('01032002','ddmmyyyy') ),
  6    partition p04 values less than (to_date('01042002','ddmmyyyy') ),
  7    partition p05 values less than (to_date('01052002','ddmmyyyy') ),
  8    partition p06 values less than (to_date('01062002','ddmmyyyy') ),
  9    partition p07 values less than (to_date('01072002','ddmmyyyy') ),
 10    partition p08 values less than (to_date('01082002','ddmmyyyy') ),
 11    partition p09 values less than (to_date('01092002','ddmmyyyy') ),
 12    partition p10 values less than (to_date('01102002','ddmmyyyy') ),
 13    partition p11 values less than (to_date('01112002','ddmmyyyy') ),
 14    partition p12 values less than (to_date('01122002','ddmmyyyy') )
 15  )
 16  /

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec dbms_stats.set_table_stats( user, 'SMALL_TABLE', numrows => 10000, numblks => 100 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> exec dbms_stats.set_table_stats( user, 'BIG_TABLE', numrows => 10000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> delete from plan_table;

5 rows deleted.

ops$tkyte@ORA817DEV> set echo off

Explained.


Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |    26K|    3M|   3073 |       |       |
|  HASH JOIN                |          |    26K|    3M|   3073 |       |       |
|   TABLE ACCESS FULL       |SMALL_TAB |    26 |  806 |      3 |       |       |
|   PARTITION RANGE ITERATOR|          |       |      |        |   KEY |   KEY |
|    TABLE ACCESS FULL      |BIG_TABLE |    10M|  953M|   2879 |   KEY |   KEY |
--------------------------------------------------------------------------------

8 rows selected.


I get the *same* results in 817 and 9iR2.

I'm guessing you don't have any or they are totally wrong.

 

Tom,

"(eg: populated off of data
dictionary data or some procedural process)" what does this mean?

SQL> select * from dept;
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 RESEARCH       OPERATIONS

SQL> update dept set loc='DALLAS' where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> select column_name, num_distinct from user_tab_columns
  2  where table_name='DEPT';
DEPTNO                                    5
DNAME                                     4
LOC                                       5

SQL> analyze table dept compute statistics;

Table analyzed.

SQL> /

Table analyzed.

SQL> select column_name, num_distinct from user_tab_columns
  2   where table_name='DEPT';
DEPTNO                                    5
DNAME                                     4
LOC                                       4

SQL> select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';  2    3    4

no rows selected

SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';  2    3    4    5

Explained.

SQL> @?/rdbms/admin/utlxplp.sql
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------

| SELECT STATEMENT           |          |     2 |   44 |      3 |      |      |
           |       |       |

|  HASH JOIN                 |          |     2 |   44 |      3 |      |      |
           |       |       |

|   TABLE ACCESS FULL        |DEPT      |     2 |   18 |      1 |      |      |
           |       |       |

|   PARTITION RANGE ALL      |          |       |      |        |      |      |
           |     1 |     4 |

|    TABLE ACCESS FULL       |RANGE_PAR |    84 |    1K|      1 |      |      |
           |     1 |     4 |

--------------------------------------------------------------------------------
----------------------------


8 rows selected.

SQL> alter session set "_subquery_pruning_cost_factor"=1;

Session altered.

SQL> >  alter session set "_subquery_pruning_reduction"=100;
SP2-0734: unknown command beginning ">  alter s..." - rest of line ignored.
SQL> alter session set "_subquery_pruning_reduction"=100;

Session altered.

SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';    2    3    4    5

Explained.

SQL> alter system flush shared_pool;

System altered.

SQL> explain plan for
select /*+ ordered use_hash(p) */ col1
from dept d, range_part p
where d.deptno = p.col1
and d.loc='DALLAS';   2    3    4    5

Explained.

SQL> @?/rdbms/admin/utlxplp.sql
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------

| SELECT STATEMENT           |          |     2 |   44 |      3 |      |      |
           |       |       |

|  HASH JOIN                 |          |     2 |   44 |      3 |      |      |
           |       |       |

|   TABLE ACCESS FULL        |DEPT      |     2 |   18 |      1 |      |      |
           |       |       |

|   PARTITION RANGE ITERATOR |          |       |      |        |      |      |
           |   KEY |   KEY |

|    TABLE ACCESS FULL       |RANGE_PAR |    84 |    1K|      1 |      |      |
           |   KEY |   KEY |

--------------------------------------------------------------------------------
----------------------------


8 rows selected.


Thanks                                            

Tom,


Thanks for your very very valuable input. I did analyze all the partitions....but think somehow my stats got screwed up.

It now does use hash join.

Thanks again....

Tom,

For a given dt_cr of submission file, The details can be either 

 a) on the date created as of submission file (dt_cr)
 b) one date prior the submission file dt_cr (dt_cr - 1)
 c) one day after the submission file (dt_cr + 1)

so i query:

explain plan for select /*+ ordered use_hash(b)  */ *
from submission_file1 a, submission_detail1 b
where a.latest_status_change_dt between to_date('12/01/2002','mm/dd/yyyy') and
to_date('12/05/2002','mm/dd/yyyy')
and a.subm_id = b.subm_id
and b.dt_cr between trunc(a.dt_cr) - 1 and  trunc(a.dt_cr) + 1;

SQL> @?/rdbms/admin/utlxplp.sql
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT|
PQ Distrib | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------

| SELECT STATEMENT           |          |     6K|    1M| 123872 |      |      |
           |       |       |

|  HASH JOIN                 |          |     6K|    1M| 123872 |      |      |
           |       |       |

|   TABLE ACCESS FULL        |SUBMISSIO |    26 |    2K|     16 |      |      |
           |       |       |

|   PARTITION RANGE ALL      |          |       |      |        |      |      |
           |     1 |    12 |

|    TABLE ACCESS FULL       |SUBMISSIO |    10M|    1G|  15178 |      |      |
           |     1 |    12 |

--------------------------------------------------------------------------------
---------------------------- 


does the partition elimination not work for this?

 

Here -- it would be even less likely.  

you are performing functions on columns (stats are almost useless there)


We have no idea what domain of b.dt_cr's are likely or possible here.

Now, since submission_file1 is a "small table" -- only 10k rows -- you can do this easily enough:

ops$tkyte@ORA920> delete from plan_table;
5 rows deleted.

ops$tkyte@ORA920> explain plan
  2  for
  3      select *   <b>/* Look ma -- no hints!!!*/</b>
  4        from small_table a, big_table b,<b>
  5        ( select min(trunc(dt_cr)-1) min_dt_cr,
  6                max(trunc(dt_cr)+1) max_dt_cr
  7                   from small_table
  8          where latest_status_dt between to_date('12/01/2002','mm/dd/yyyy')
  9                                     and to_date('12/05/2002','mm/dd/yyyy')
 10            ) parms</b>
 11       where a.latest_status_dt between to_date('12/01/2002','mm/dd/yyyy') 
                                       and to_date('12/05/2002','mm/dd/yyyy')
 12         and b.dt_cr between trunc(a.dt_cr)-1 and trunc(a.dt_cr)+1<b>
 13         and b.dt_cr between parms.min_dt_cr and parms.max_dt_cr</b>
 14         and a.subm_id = b.subm_id
 15  /

Explained.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set echo off

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
| Id  | Operation                  |  N| Rows  | Bytes | Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |   |     1 |   149 |  9642 |       |       |
|*  1 |  HASH JOIN                 |   |     1 |   149 |  9642 |       |       |
|*  2 |   TABLE ACCESS FULL        | SM|    25 |   775 |    11 |       |       |
|   3 |   NESTED LOOPS             |   | 25000 |  2880K|  9630 |       |       |
|   4 |    VIEW                    |   |     1 |    18 |    11 |       |       |
|*  5 |     FILTER                 |   |       |       |       |       |       |
|   6 |      SORT AGGREGATE        |   |     1 |    18 |       |       |       |
|*  7 |       TABLE ACCESS FULL    | SM}    25 |   450 |    11 |       |       |
|   8 |    PARTITION RANGE ITERATOR|   |       |       |       |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL      | BI|     3 |   300 |  9619 |   KEY |   KEY |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."SUBM_ID"="B"."SUBM_ID")
       filter("B"."DT_CR">=TRUNC("A"."DT_CR")-1 AND "B"."DT_CR"<=TRUNC("A"."DT_CR")+1)
   2 - filter("A"."LATEST_STATUS_DT">=TO_DATE('2002-12-01 00:00:00', 'yyyy-mm-dd hh24:mi
              :ss') AND "A"."LATEST_STATUS_DT"<=TO_DATE('2002-12-05 00:00:00', 'yyyy-mm-
              dd hh24:mi:ss'))
   5 - filter(TO_DATE('2002-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<=TO_DATE('2002-12-
              05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   7 - filter("SMALL_TABLE"."LATEST_STATUS_DT">=TO_DATE('2002-12-01 00:00:00', 'yyyy-mm-
              dd hh24:mi:ss') AND "SMALL_TABLE"."LATEST_STATUS_DT"<=TO_DATE('2002-12-05
              00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   9 - filter("B"."DT_CR">="PARMS"."MIN_DT_CR" AND "B"."DT_CR"<="PARMS"."MAX_DT_CR")

Note: cpu costing is off

32 rows selected.
 

Tom,
I created the small_table and big_table like your example and set the statistics. However I was suprised at the explain plan when I ran this SQL:

SQL> explain plan for SELECT  *
  2  FROM big_table b
  3  WHERE b.dt_cr BETWEEN TO_DATE('11/01/2002','mm/dd/yyyy') AND TO_DATE('11/20/2002','mm/dd/yyyy');

Explained.

SQL> @?/RDBMS/ADMIN/UTLXPLS 

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |    25K|    2M|   1266 |       |       |
|  TABLE ACCESS FULL        |BIG_TABLE |    25K|    2M|   1266 |    12 |    12 |
--------------------------------------------------------------------------------

It appears to only be reading the last partition like I expected (Pstart = 12 Pstop = 12), but why doesn't PARTITION RANGE show up in the plan?

Thanks 

Hi Tom!

I did the following: 

SQL> create table parttest (
  2    part_key number(38) not null,
  3    data varchar2(10)
  4  )
  5  partition by range (part_key) (
  6    partition p1 values less than(1000) tablespace tbs_1,
  7    partition p2 values less than(2000) tablespace tbs_2
  8  );

Tabelle wurde angelegt.

SQL> analyze table parttest compute statistics
  2  for table for all columns for all indexes;

Tabelle wurde analysiert.

SQL> alter tablespace tbs_1 offline normal;

Tablespace wurde geändert.

SQL> var a number
SQL> var b number
SQL> var c number
SQL> execute :a := 1000; :b := 500; :c := 1500;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select count(*) from parttest
  2  where part_key >= :a
  3  and part_key between :b and :c;

  COUNT(*)
----------
         0

This statement works, but:

SQL> select count(*)
  2  from (select * from parttest
  3        where part_key >= :a)
  4  where part_key between :b and :c;
from (select * from parttest
                    *
FEHLER in Zeile 2:
ORA-00376: Datei 16 kann zur Zeit nicht gelesen werden
ORA-01110: Datendatei 16: 'E:\APPS\ORACLE\ORADATA\ORA92DB\TBS_1.ORA'

Seldom, what's the difference?

SQL> explain plan for
  2  select count(*) from parttest
  3  where part_key >= :a
  4  and part_key between :b and :c;

EXPLAIN PLAN ausgeführt.

SQL> @?/RDBMS/ADMIN/UTLXPLS

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| Id  | Operation                  |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |    13 |     2 |       |       |
|   1 |  SORT AGGREGATE            |             |     1 |    13 |       |       |       |
|*  2 |   FILTER                   |             |       |       |       |       |       |
|   3 |    PARTITION RANGE ITERATOR|             |       |       |       |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PARTTEST    |     1 |    13 |     2 |   KEY |   KEY |

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER(:Z)<=TO_NUMBER(:Z) AND TO_NUMBER(:Z)<=TO_NUMBER(:Z))
   4 - filter("PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
              "PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND "PARTTEST"."PART_KEY"<=TO_NUMBER(:Z))

Note: cpu costing is off

19 Zeilen ausgewählt.

SQL> explain plan for
  2  select count(*)
  3  from (select * from parttest
  4        where part_key >= :a)
  5  where part_key between :b and :c;

EXPLAIN PLAN ausgeführt.

SQL> @?/RDBMS/ADMIN/UTLXPLS

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| Id  | Operation                  |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |    13 |     2 |       |       |
|   1 |  SORT AGGREGATE            |             |     1 |    13 |       |       |       |
|*  2 |   FILTER                   |             |       |       |       |       |       |
|   3 |    PARTITION RANGE ITERATOR|             |       |       |       |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PARTTEST    |     1 |    13 |     2 |   KEY |   KEY |

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER(:Z)>=TO_NUMBER(:Z) AND TO_NUMBER(:Z)<=TO_NUMBER(:Z))
   4 - filter("PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
              "PARTTEST"."PART_KEY"<=TO_NUMBER(:Z) AND "PARTTEST"."PART_KEY">=TO_NUMBER(:Z))

Note: cpu costing is off

19 Zeilen ausgewählt.

So, what is the difference between these queries??

I would like to use a view which is identical with the inline view of the second (failing) query, so that i can take offline partitions and modify the view so that the endusers (which are using this view) don't notice the change (and moreover don't get any ORA-00376 error messages).

Regards. 

the first query is filtering based on the "where part_key >= :a" first AND THEN on the between.

The second query is filtering on the between AND THEN the "where part_key >= :a"

the "where part_key >= :a" precluded the first partition.
the "between" does not.

it is the difference between:

4 - filter("PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
           <b>"PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND 
           "PARTTEST"."PART_KEY"<=TO_NUMBER(:Z))</b>

and

4 - filter(<b>"PARTTEST"."PART_KEY">=TO_NUMBER(:Z) AND
           "PARTTEST"."PART_KEY"<=TO_NUMBER(:Z) AND </b>
           "PARTTEST"."PART_KEY">=TO_NUMBER(:Z))

<b>Now, as much as I "don't like it" -- since it will have side effects on the optimizer in other places, we can request that the "view" not be merged into</b>

It can be very tricky hiding hints in views, it can lead to suboptimal plans further on down the road.  It accomplishes your goal, but that might not always be "good".

Here is a small simulation based on your case.  Since you cannot "bind" into a view, i'll use an application context -- that is what your end uses will have to do...



ops$tkyte@ORA920PC> alter tablespace users offline normal;
 
Tablespace altered.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> var a number
ops$tkyte@ORA920PC> var b number
ops$tkyte@ORA920PC> var c number
ops$tkyte@ORA920PC> execute :a := 1000; :b := 500; :c := 1500;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_application_info.set_client_info( 1000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace view v
  2  as
  3  select /*+ NO_MERGE */ *
  4    from parttest
  5   where part_key >= to_number( sys_context( 'userenv', 'client_info' ) )
  6  /
 
View created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select count(*) from parttest
  2  where part_key >= :a
  3  and part_key between :b and :c;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select *
  2   from (select /*+ NO_MERGE */ part_key from parttest
  3          where part_key >= :a)
  4   where part_key between :b and :c
  5  /
 
no rows selected
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select count(*)
  2  from (select /*+ NO_MERGE */ part_key from parttest
  3        where part_key >= :a)
  4  where part_key between :b and :c;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select count(*)
  2    from v
  3   where part_key between :b and :c;
 
  COUNT(*)
----------
         0
 

Hi Tom!

The trick with the hint works great.
But there seems to be a way to do it without the hint, but i don't know if it is better than the hint ...

First i added antoher partition to the table parttest:

  1  alter table parttest
  2  add partition p3 values less than (3000)
  3*     tablespace tbs_2
SQL> /

Tabelle wurde geändert.

(There a still no rows in the table.)

SQL> analyze table parttest compute statistics
  2  for table for all columns for all indexes;

Tabelle wurde analysiert.

Now i turn tablespace TBS_1 offline again.

SQL> alter tablespace tbs_1 offline normal;

Tablespace wurde geändert.

Now i know that partition p1 is not available. Then i modify the view v from:

create or replace view v as
select part_key, data
from parttest partition (p1)
union all
select part_key, data
from parttest partition (p2)
union all
select part_key, data
from parttest partition (p3);

To:

create or replace view v as
/* select part_key, data
   from parttest partition (p1) 
union all */
select part_key, data
from parttest partition (p2)
union all
select part_key, data
from parttest partition (p3);

I explicitly tell Oracle which partitions it should (not) use.

SQL> var b number
SQL> var c number
SQL> execute :b := 500; :c := 1500;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> set autotrace on
SQL> select *
  2  from v
  3  where part_key between :b and :c;

Es wurden keine Zeilen ausgewählt

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
   1    0   FILTER
   2    1     VIEW OF 'V' (Cost=2 Card=1 Bytes=20)
   3    2       UNION-ALL (PARTITION)
   4    3         FILTER
   5    4           TABLE ACCESS (FULL) OF 'PARTTEST' (Cost=2 Card=1 Bytes=20)
   6    3         FILTER
   7    6           TABLE ACCESS (FULL) OF 'PARTTEST' (Cost=2 Card=1 Bytes=20)

Statistiken
----------------------------------------------------------
         96  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        283  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

This works, but why is the third partition also scanned, since the data simply cannot be in there? I think only partition p2 should be scanned.

Are there other dangers involved in this "name-the- partitons-exclicitly-in-the-view"-approach except that it is not easy to administer and "ugly".

Regards.
 

The second view (with the partitions) doesn't have the predicate because i exclicitly omit the unavailable partition with all the rows with partkey < 1000.
It is simply not necessary. (Right?)

What i want to achieve through the view is, that the unavailable partition is not used.

Yes, if the user wouldn't know about the missing data, it would be "a really bad idea"!
But the user will know that the data with partkey < 1000 isn't queried.
In reality partkey is a date and each partition contains the data from one year. The users know: We are in 2003, the data from the actual year and the data from the last two years is online, so the data from year 2000 and before is offline.
For example in january 2004 i want to offline the data from 2001. To achive this i plan to do the following:

1. Inform the end users about the "offlining" of the 2001 data.
2. create or replace v, so that the data from 2001 isn't queried. Here i don't know, which method is better: predicate or union all on the available partitions (See my previous reply)???
3. alter tablespace DATA_2001 offline normal;
4. Archiving the data files from the DATA_2001 tablespace.

If an end users needs the data from 2001 (or before) - rare - i will restore the required data files, turn the tablespace DATA_2001 online again and then change the view so that the data is visible and can be queried by the user.
When the end user finish the work with the old data, i archive it again.

Is this a "bad idea"?

Well, you are right - an error message is definitly better than a "wrong" (= here: not the expected data was queried) query result.

But - i know it's "funny" -, it were the end users, that said: "We don't want to see an error message about unavailable data, we just want results!"

I think the best i can do is to change the end users attitude about this issue, so that they understand why an error message can be better than a (wrong!) result.
(Even when this probably means more work for me, because then they will immediately realize that they need the old data ;-)

Hi Tom,

I have a table TXNS which is partitioned on a DATE column TXNDT.

PARTITION BY RANGE (TXNDT)
(
PARTITION Y2005 VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DUCO_OWNER
,
PARTITION Y2010 VALUES LESS THAN (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DUCO_OWNER
,
...
...
PARTITION Y2080 VALUES LESS THAN (MAXVALUE)
NOLOGGING
NOCOMPRESS
TABLESPACE DUCO_OWNER
)

I want to get the MIN(TXNDT) from this table, but surprisingly the query is not pruned to the partition containing the smallest range.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 4398
SORT AGGREGATE 1 8
TABLE ACCESS FULL TXNS 480 K 3 M 4398

Do you have any idea why?

Thanks. Now I understand. However I was expecting Oracle to do sth. smart, just like "Hey, I have found some values in the lowest range, so I will just skip the other ranges." Just like what it does when scanning an index.

Is there a hint that I can use to direct the query to use only one partition, like the PARTIION clause?

We have one table which is partitioned. Its definition is

*******************************************************
CREATE TABLE "CCIS"."LITIGANT"
("MAINCASE" "CCIS"."CASEOBJECT",
"LITIGANT_TYPE" CHAR(1) ,
"SERIALNUMBER" NUMBER(4),
"GENERATIONNUMBER" NUMBER(1),
"MEMBERNUMBER" NUMBER(2),
"ADVOCATE" "CCIS"."ADVOCATENTYPE" ,
"LITIGANTNAME" VARCHAR2(60) NOT NULL,
)
PARTITION BY RANGE (MAINCASE.SIDE)
(PARTITION CIVILSIDE VALUES less than ('D')
TABLESPACE LOBISCIVIL,
PARTITION OJSIDE VALUES less than ('P')
TABLESPACE LOBIS ,
PARTITION LOBISCRIMINAL VALUES less than ('S')
TABLESPACE LOBISCRIMINAL)
TABLESPACE "LOBIS"
NESTED TABLE "ADVOCATE" STORE AS ADVOCATE_STORE_NTAB ;

The CASEOBJECT has following structure...

CREATE OR REPLACE TYPE "CCIS"."CASEOBJECT" AS OBJECT
( "SIDE" CHAR(1),
"CASETYPE" VARCHAR2(5),
"CASEYEAR" NUMBER(4),
"CASENUMBER" NUMBER(6),
"CASESUBTYPE" VARCHAR2(2)
)

And nested table ADVOCATE has following sturcture...

CREATE OR REPLACE TYPE "CCIS"."ADVOCATEOBJECT" AS OBJECT
( "ADVOCATECODE" NUMBER(6),
"REPRESENTINGCAPACITY" NUMBER(2)
)

************************************************************

The litigant table has a primary key on maincase,litigant_type,serialnumber,generationnumber and membernumber

The nested table is indexed on nested_table_id and advocatecode


Now when we try to insert one row into nested table column ADVOCATE, it took too much time.

Plan table output is...
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| INSERT STATEMENT | | 987K| 35M| 1836 | | |
--------------------------------------------------------------------------------



The SQL trace and tkprof shows following result...

********************************************************************************

insert into table(select advocate from litigant l1
where l1.maincase.side='C' and l1.maincase.casetype='ST' and
l1.maincase.caseyear=2004 and l1.maincase.casenumber=40 and
l1.serialnumber = 1 and l1.membernumber = 0 and l1.generationnumber = 1
and litigant_type='P')
values(1237,'1')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 18 0 0
Execute 1 0.00 0.14 2 9 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.15 2 27 4 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 53
********************************************************************************

SELECT 1
FROM
"CCIS"."LITIGANT" WHERE "SYS_NC0001400015$" = :1 FOR UPDATE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 5.51 49.63 66347 66363 62 0
Fetch 1 5.47 12.99 66347 66363 60 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 10.98 62.62 132694 132726 122 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 FOR UPDATE
2 PARTITION RANGE ALL PARTITION: START=1 STOP=3
2 TABLE ACCESS FULL LITIGANT PARTITION: START=1 STOP=3

*********************************************************************************

The least thing I understood from above is that my query scan all partition instead of single partition.
That is why query took so much time .

How can we force to search into one partition and improve query performance?


Thanks,
Kiran Shah

Sorry Tom for incomplete info. Giving below more detail info. so that you can re-produce them easily...

We are using Oracle 9.1 database.

Following is the definition of objects....
CREATE OR REPLACE TYPE "CCIS"."CASEOBJECT" AS OBJECT
( "SIDE" CHAR(1),
"CASETYPE" VARCHAR2(5),
"CASEYEAR" NUMBER(4),
"CASENUMBER" NUMBER(6),
"CASESUBTYPE" VARCHAR2(2)
)

CREATE OR REPLACE TYPE "CCIS"."ADVOCATEOBJECT" AS OBJECT
( "ADVOCATECODE" NUMBER(6),
"REPRESENTINGCAPACITY" NUMBER(2)
)

CREATE OR REPLACE TYPE "CCIS"."ADVOCATENTYPE"
AS TABLE OF "CCIS"."ADVOCATEOBJECT"


CREATE TABLE "CCIS"."LITIGANT"
("MAINCASE" "CCIS"."CASEOBJECT",
"LITIGANT_TYPE" CHAR(1) ,
"SERIALNUMBER" NUMBER(4),
"GENERATIONNUMBER" NUMBER(1),
"MEMBERNUMBER" NUMBER(2),
"ADVOCATE" "CCIS"."ADVOCATENTYPE" ,
"LITIGANTNAME" VARCHAR2(60) NOT NULL
)
PARTITION BY RANGE (MAINCASE.SIDE)
(PARTITION CIVILSIDE VALUES less than ('D')
TABLESPACE LOBISCIVIL,
PARTITION OJSIDE VALUES less than ('P')
TABLESPACE LOBIS ,
PARTITION LOBISCRIMINAL VALUES less than ('S')
TABLESPACE LOBISCRIMINAL)
TABLESPACE "LOBIS"
NESTED TABLE "ADVOCATE" STORE AS ADVOCATE_STORE_NTAB ;

(Note : Actual table contains more fields than shown here. However they are not relevant here, so they have been kept out.)

(Note : This LITIGANT table contains 3million records right now. And it is expanded day by day. And nested table also contains roughly same no. of rows)

Now I am firing following insert query first on LITIGANT table...

insert into litigant
values(caseobject('C','ST',2004,40,NULL),'P',1,1,0,advocatentype(),'KIRAN SHAH');

And then inserting advocate codes into nested table...using folowing query...

insert into table(select advocate from litigant l1
where l1.maincase.side='C' and l1.maincase.casetype='ST' and
l1.maincase.caseyear=2004 and l1.maincase.casenumber=40 and
l1.serialnumber = 1 and l1.membernumber = 0 and l1.generationnumber = 1
and litigant_type='P')
values(1237,'1')


Above query (insertion into nested table) takes too much time.

Plan table output shows only following one line.

Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| INSERT STATEMENT | | 987K| 35M| 1836 | | |
--------------------------------------------------------------------------------



The SQL trace and tkprof shows following result...

********************************************************************************

insert into table(select advocate from litigant l1
where l1.maincase.side='C' and l1.maincase.casetype='ST' and
l1.maincase.caseyear=2004 and l1.maincase.casenumber=40 and
l1.serialnumber = 1 and l1.membernumber = 0 and l1.generationnumber = 1
and litigant_type='P')
values(1237,'1')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 18 0 0
Execute 1 0.00 0.14 2 9 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.15 2 27 4 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 53
********************************************************************************

SELECT 1
FROM
"CCIS"."LITIGANT" WHERE "SYS_NC0001400015$" = :1 FOR UPDATE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 5.51 49.63 66347 66363 62 0
Fetch 1 5.47 12.99 66347 66363 60 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 10.98 62.62 132694 132726 122 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 FOR UPDATE
2 PARTITION RANGE ALL PARTITION: START=1 STOP=3
2 TABLE ACCESS FULL LITIGANT PARTITION: START=1 STOP=3

*********************************************************************************

I hope this is as per your requirement.

As per my understanding, tkprof output shows that query scans all partitions. And that is why it takes more time.

So what modification is required for better performance ?

Thanks,
Kiran Shah

Dear Tom,

Thanks for perfect - to-the point diagnosis...

I put UNIQUE constraint on hidden column and everything works fine...

Thanks again,

Kiran Shah

Tom,
I am seeking your expert idea here (Solaris 5.8, Oracle 9.2.0.5):
I have a 50M row table analyzed using
DBMS_STATS.GATHER_TABLE_STATS with the following settings:
CASCADE=>TRUE,
ESTIMATE_PERCENT => 5);
Then I issued the following query against it:

SELECT COUNT(*), CLAIM_TYPE_KEY FROM CLAIM_FACT PARTITION (P200409) -- about 2M rows
GROUP BY CLAIM_TYPE_KEY

Sure enough, it's executing the best plan by using the bitmap index on the CLAIM_TYPE_KEY (fast full scan and bitmap conversion), it returns in 0.03 seconds;
In real world, however, we cannot ask users to specify partition names, since the table is range partitioned monthly by TRANS_DATE, I changed it to:

SELECT COUNT(*), CLAIM_TYPE_KEY FROM CLAIM_FACT
WHERE TRANS_DATE BETWEEN '1-SEP-2004' AND '30-SEP-2004'
GROUP BY CLAIM_TYPE_KEY

But this time, it's doing a full table scan and return the result in 18 seconds.
My question is:
What could have prevented it from finding the right plan (partition elimination)?
I noticed that the following settings on the DB:
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 100
(We probably should ask DBA to lower the second one to make it leaning more toward using index).

Thanks,

Tom,
Thanks in advance for looking into that.
Here's actual SQL and their explain plans (reformatted).
The whole table has 58M rows.
That 200409 partition has about 1.9M rows.
---------------------------------------------

SELECT COUNT(*), CLM_RESP_KEY FROM XPS_PTCLAIM_FACT PARTITION (P200409)
GROUP BY CLM_RESP_KEY
<<OUTPUT>>
COUNT(*) CLM_RESP_KEY
---------- ------------
937866 1
88333 2
946687 3
4005 4
Elapsed: 00:00:00.01


-----------------------------------------------------------------------------
|Id | Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 |69 | |
| 1 | SORT GROUP BY NOSORT | | 4 | 12 |69 | |
| 2 | BITMAP CONVERSION COUNT| | | | | |
| 3 | BITMAP INDEX FULL SCAN| CLMRESP_BIDX | | | | 40 | 40
-----------------------------------------------------------------------------
Note: cpu costing is off
11 rows selected.

SELECT COUNT(*), CLM_RESP_KEY FROM XPS_PTCLAIM_FACT
WHERE TRANS_DATE BETWEEN '1-sep-2004' AND '30-sep-2004'
GROUP BY CLM_RESP_KEY
<< OUTPUT>>
COUNT(*) CLM_RESP_KEY
---------- ------------
937866 1
88333 2
946687 3
4005 4
Elapsed: 00:00:19.02
------------------------------------------------------------------------------
| Id | Operation |Name |Rows |Bytes|Cost |Pstart|Pstop |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 44 |4682 | | |
| 1 | SORT GROUP BY | | 4 | 44 |4682 | | |
|* 2 | TABLE ACCESS FULL |XPS_PTCLAIM_FACT|1983K| 20M|3199 | 40 | 40|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("XPS_PTCLAIM_FACT"."TRANS_DATE"<=TO_DATE('2004-09-30 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))

Note: cpu costing is off
16 rows selected.

Tom,
I applied the to_date('9/1/2004', 'mm/dd/yyyy') functions and xplan is exactly the same as before and it takes about 17-23 seconds to run (versus 0.01 seconds with hardcoded partition name).

Here're some of the parameters:
NAME TYPE VALUE
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608

I believe Oracle is executing the Wayne’s query correctly.
The first query contains the partition name, I believe Oracle executes the query as if it were an isolated table, thus allowing a full bitmap scan on CLMRESP_BIDX for that partition.

The second query contains a filter on the trans_date, while this is part of the partition key Oracle has no way to determine if all the rows will be returned for the partition, thus it cannot use a bitmap conversion count, so it does a full table scan on the partition.

Hi Tom,
I tried to send you the exp files for that data and the stats table, but it was rejected because of the zip files (not accepting .zip or .Z files).

What should I do?

just rename them (file.zip to file.zip.rename) oracle
email rejectss zipped files due to some security reason.

Tom,
Thanks for the followup.
You are right that these queries are not the same. My questions are:
1. The table is partitioned by TRANS_DATE. Oracle might think the condition:
TRANS_DATE BETWEEN '1-SEP-2004' AND '30-SEP-2004'
might leave some records off with trans_dates between '30-sep-2004' and '30-sep-2004'+0.99999 (the date is actually truncated so it is not a problem), but even if I changed the query to:
select count(*), clm_resp_key
from xps_ptclaim_fact
where trans_date >= first_date_of_partition
and trans_date < first_date_of_next_partition
group by clm_resp_key

It's still taking 20 seconds. Will the optimizer be smart enough to know that this is exactly the condition it uses to find the right partition to put incoming new records in so use the same condition to eliminate the partition when executing this query (i.e. execute exactly the same as if the partition name is hardcoded)?

2. More importantly, what suggestions do you recommend on the index structure or query structure so that we can make it run the bitmap conversion (returns within mili seconds)? Most of our queries use the TRANS_DATE as a condition and group by something else.
(Well, 19 seconds is not a bad response time anyway).

Thanks in advance.

Tom,
You are right, the partition elimination does work, and we are discussing here is what happend after the elimination is done.

Most of our reports are monthly (partition-wise), some are weekly (so they hit single partition most of the weeks in a month).

In annual reports when they need to hit 12 partitions and assuming the aggregate MVs are not there yet (so no query rewrite), I think the optimizer should still be able to run(in parallel) 12 partition-wise queries and adds them togher, if it is faster than doing a whole 12 partition group-by operation.

Your second question is right to the point (That's why you are the best!).

No, we don't. In most cases, we only run reports for a specific client (or a group of several), so in these cases, we cannot use the simple bitmap conversion anyway.

What I am also wondering is why a query like the following is not doing a bitmap conversion (Do it and throw 4 away):

SELECT COUNT(*), CLM_RESP_KEY
FROM CLAIM_FACT PARTITION (P200409)
WHERE CLM_RESP_KEY IN (1,2,3) --Only 4 values: 1,2,3,4 and it is bitmap indexed
GROUP BY CLM_RESP_KEY

Thanks again for looking into that and talking to the development team.

Tom,
You are right (as usual). Sorry for the wrong information. Yesterday we were migrating and upgrading from 8.1.7.4 to 9.2.0.5. Before the 9i was ready, I logged in 8i. In 8i, it did not use the bitmap conversion, but in 9iR2, it does, just like what you just showed me.

Thank you very much!

By the way, how do you generate the explain plan without seeing any data?

SQL> set autotrace traceonly explain 

Hi,

I have Fact table with partition on Range day_key and subpartition by List on client_key.

I am joining client_key with another look up table to restrict the set of clients from the single partition at RUN Time.

But my query is visiting all the subpartitions. ( Partition pruning not happening for the list subpartition )

The explain plan and the trace with level 12 shows all subpartition getting scaned.

Is there any method to enable the List partition pruning at run time.

BO_LIST is Partitioned on Range (date1) and Subpartition on List (client_key). (There are 9 sub partitions )


SELECT COUNT(acckey),SUM(curr_bal),date1, snum
FROM bo_list b , client c
WHERE b.client_key=c.client_key
AND c.snum=9119
AND b.date1 =20050108
GROUP BY date1, snum

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (GROUP BY)
340672 HASH JOIN
79 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT'
454 BITMAP CONVERSION (TO ROWIDS)
454 BITMAP INDEX (FULL SCAN) OF 'CLIENT_CLIENT_KEY_BIDX'
601585 PARTITION LIST (ALL) PARTITION: START=1 STOP=9
601585 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BO_LIST'
PARTITION: START=19 STOP=27



Thanks in advance

Dilip Patel.

Here is the explain plan in readable format. It is confirmed that all client_key for the c.snum=9119 are in the same sub partition.

I also checked in the trace file, each block with db scattered read corresponds to the all 9 subpartitions.

Can't understand why it's not doing visit to only the subpartion required.


SELECT COUNT(acckey),SUM(curr_bal),date1, snum
FROM bo_list b , client c
WHERE b.client_key=c.client_key
AND c.snum=9119
AND b.date1 =20050108
GROUP BY date1, snum

---------------------------------------------------------------------------------------------------------
Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 8 | 464 | 92 | | |
1 | SORT GROUP BY | | 8 | 464 | 92 | | |
* 2 | HASH JOIN | | 8 | 464 | 20 | | |
* 3 | TABLE ACCESS BY INDEX ROWID | CLIENT | 7 | 133 | 2 | | |
4 | BITMAP CONVERSION TO ROWIDS| | | | | | |
5 | BITMAP INDEX FULL SCAN | CLIENT_CLIENT_KEY_BIDX | | | | | |
6 | PARTITION LIST ALL | | | | | 1 | 9 |
* 7 | TABLE ACCESS FULL | BO_LIST | 327 | 12753 | 17 | 19 | 27 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------

2 - access("B"."CLIENT_KEY"="C"."CLIENT_KEY")
3 - filter(TO_NUMBER("C"."SNUM")=9119)
7 - filter("B"."DATE1"=20050108)


Thanks

Dilip Patel
Satyam Computers Services Limited
www.satyam.com

Hi Tom,
This is very puzzling from below test case #2, I have hard time to figure out why Oracle still needs to scan the table in order to find the result set, since the index has all it needs. It seems to me that oracle is smart enough to determint when (i<2), it looks for part1, but why isn't it smart enough to get data out of index, instead, it does tablescan? Both test case#1 & #3 are expected though.

Many thanks!

SQL> create table dropme (i int, c char(10))
  2  partition by range (i)
  3  (partition p1 values less than (2),
  4   partition p2 values less than (3),
  5   partition p3 values less than (4)
  6  );

Table created.

SQL> create index dropme_i on dropme (c) local;

Index created.

SQL> insert into dropme values (1, 'p1');

1 row created.

SQL> insert into dropme values (2, 'p2');

1 row created.

SQL> insert into dropme values (3, 'p3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dropme partition (p1);

         I C
---------- ----------
         1 p1

SQL> select * from dropme partition (p2);

         I C
---------- ----------
         2 p2

SQL> /

         I C
---------- ----------
         2 p2

SQL> select * from dropme partition (p3);

         I C
---------- ----------
         3 p3

SQL> explain plan for
  2  select c from dropme where c='p1';

Explained.

----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
--test1, no partition pruning, oracle is smart enough to get data from index structure without table scan
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------

SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     2 |    24 |     2  (50)|       |       |
|   1 |  PARTITION RANGE ALL |             |       |       |            |     1 |     3 |
|*  2 |   INDEX RANGE SCAN   | DROPME_I    |     2 |    24 |     4   (0)|     1 |     3 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DROPME"."C"='p1')

13 rows selected.

SQL>

----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
--test2, with partition pruning, oracle is NOT able to get data from index structure, it has to do table scan(why???)
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------

SQL> explain plan for
  2  select c from dropme where c='p1' and i<2;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |     1 |    25 |     2  (50)|       |       |
|*  1 |  TABLE ACCESS BY LOCAL INDEX ROWID| DROPME      |     1 |    25 |     2  (50)|     1 |     1 |
|*  2 |   INDEX RANGE SCAN                | DROPME_I    |     1 |       |     1   (0)|     1 |     1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DROPME"."I"=1)
   2 - access("DROPME"."C"='p1')

14 rows selected.

----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
--test3, with partition pruning, oracle is able to get data from index without table scan
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
SQL> explain plan for
  2  select c from dropme partition (p1) where c='p1';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    12 |     2  (50)|       |       |
|*  1 |  INDEX RANGE SCAN    | DROPME_I    |     1 |    12 |     1   (0)|     1 |     1 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DROPME"."C"='p1')

12 rows selected.

SQL>
 

Hi Tom,
The number of records is irrelavent to my question. I can load it up with many rows but it does not change the question I was asking.

For this SQL below
select c from dropme where c='p1' and i<2;

Why does Oracle need to scan the table segment(execution plan has table access by rowid)? All the information is in the index column (C) already, no need to scan the table at all.

It seems oracle knows partition pruning to check part1 only by looking at (i<2) but it's not smart enough to know that all rows and only rows in part1 meet the criteria.

Because of this reason, there are extra buffer gets for table access of this type of SQL in our system, and due to extream high execution rate per second, it's overloading the system. If we can some how get rid of this table access by rowid, we could have save the system from hardware upgrade.

Also because the partition keys are in a certain range(more than one partition involved) so we can not use the syntax like
select c from dropme partition (part2) where c='p1'
as a alternative, which does not need a table scan by rowid, as you can see in previous post.

We want the SQL to be like
select c from dropme where c='P1' and i>1 and i<5, however, it has to read the table segment, which looks unnecessary and wrong.

Thank you and happy holiday.

It doesn't reproduce in 10.2.0.2 (with statistics in place):

dellera@ORACLE10> create table dropme (i int, c char(10))
2 partition by range (i)
3 (partition p1 values less than (2),
4 partition p2 values less than (3),
5 partition p3 values less than (4)
6 );

Table created.

dellera@ORACLE10> create index dropme_i on dropme (c) local;

Index created.

dellera@ORACLE10> insert into dropme values (1, 'p1');

1 row created.

dellera@ORACLE10> insert into dropme values (2, 'p2');

1 row created.

dellera@ORACLE10> insert into dropme values (3, 'p3');

1 row created.

dellera@ORACLE10> commit;

Commit complete.

dellera@ORACLE10> exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

dellera@ORACLE10> set autotrace traceonly explain
dellera@ORACLE10> select c from dropme where c='p1' and i<2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1964748326

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 14 | 1 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | DROPME_I | 1 | 14 | 1 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C"='p1')

It does reproduce in 9.2.0.6, anyway the above test case shows that Oracle was aware of the problem/limitation and removed it in the current release (maybe also in 9.2.0.7 or 9.2.0.8, I have neither handy to check).

Also, as a workaround, you can create an index such as

create index dropme_2_idx on dropme (c,i) local;

this avoids the table access.

HTH
Alberto

Thank you, I'll need to open a TAR with Oracle to see whether this is bug related. For the workaround you mentioned, we have being evaluated whether to add partition key to the index, but we will have to make a decision before we know that's a bug or not.

JIANZHANG@O10GR2>begin
JIANZHANG@O10GR2> for i in 1..10000 loop
JIANZHANG@O10GR2> insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
JIANZHANG@O10GR2> end loop;
JIANZHANG@O10GR2>end;
JIANZHANG@O10GR2>/

PL/SQL procedure successfully completed.

JIANZHANG@O10GR2>commit;

Commit complete.

JIANZHANG@O10GR2>exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for
all columns size 1');

PL/SQL procedure successfully completed.

JIANZHANG@O10GR2>explain plan for select c from dropme where c='p1' and i<2;

Explained.

JIANZHANG@O10GR2>@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1386302003

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 14 | 2 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| DROPME | 1 | 14 | 2 (0)| 00:00:01 | 1
|* 3 | INDEX RANGE SCAN | DROPME_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("I"<2)
3 - access("C"='p1')

16 rows selected.

JIANZHANG@O10GR2>select i, count(*) from dropme group by i;

I COUNT(*)
---------- ----------
1 3334
2 3335
3 3334

JIANZHANG@O10GR2>select count(*) from dropme where c='p1';

COUNT(*)
----------
2

JIANZHANG@O10GR2>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

JIANZHANG@O10GR2>show parameter optim

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2
JIANZHANG@O10GR2>

select name || '=' || value from v$parameter where isdefault = 'FALSE'


I cannot reproduce your test on 9i or 10g.

ops$tkyte%ORA9IR2> create table dropme (i int, c char(10))
  2  partition by range (i)
  3  (partition p1 values less than (2),
  4   partition p2 values less than (3),
  5   partition p3 values less than (4)
  6  );

Table created.

ops$tkyte%ORA9IR2> create index dropme_i on dropme (c) local;

Index created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
  2  for i in 1..10000 loop
  3    insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> @at
ops$tkyte%ORA9IR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select c from dropme where c='p1' and i<2;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DROPME' (Cost=2 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'DROPME_I' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte%ORA9IR2> select /*+ INDEX(dropme dropme_i) */ c from dropme where c='p1' and i<2;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DROPME' (Cost=2 Card=1 Bytes=14)
   2    1     INDEX (RANGE SCAN) OF 'DROPME_I' (NON-UNIQUE) (Cost=1 Card=1)




I have all defaults. 

Just for completeness - I've repeated the test in 10GR2 using the above data set

dellera@ORACLE10> begin
2 for i in 1..10000 loop
3 insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
4 end loop;
5 end;
6 /

And the plan doesn't show the access to the table (as we saw with a mere row per partition):

dellera@ORACLE10> column PLAN_TABLE_OUTPUT format a72 truncate
dellera@ORACLE10> set autotrace traceonly explain
dellera@ORACLE10> select c from dropme where c='p1' and i<2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1964748326

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)|
| 1 | PARTITION RANGE SINGLE| | 1 | 14 | 1 (0)|
|* 2 | INDEX RANGE SCAN | DROPME_I | 1 | 14 | 1 (0)|
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C"='p1')

Pstart = Pstop = 1 of course

you are right - in 10.2.0.2 it did not reproduce:

ops$tkyte%ORA10GR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select c from dropme where c='p1' and i<2

Explained.

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 1964748326

------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |   500 | 12500 |     5   (0)|
|   1 |  PARTITION RANGE SINGLE|          |   500 | 12500 |     1   (0)|
|*  2 |   INDEX RANGE SCAN     | DROPME_I |   500 | 12500 |     1   (0)|
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"='p1')

14 rows selected.


Looks like it was something corrected/added/enhanced in 10.2.0.2.
 

look for this line in the execution plan
<
TABLE ACCESS BY LOCAL INDEX ROWID| DROPME
>

since all the information column C already in the index, there should not be necessary to do table scan. From your 10GR2 example, the plan is expected, only index scan, no table scan by rowid.

Hello Tom,
As you recommended, I loaded the table with more data, you can see the difference of the execution plan. While I specify partition key (i)'s range, execution plan has one unnecessary step

<
TABLE ACCESS BY LOCAL INDEX ROWID
>

But Oracle knows how to do partition pruning, why does it need to read the table when all column C is already presented in the index??? (also the same result after gathering the stats) 

Could you take a look? Thanks a lot!


  1  begin
  2      for i in 1..100000 loop
  3        insert into dropme values (mod(i,3)+1, 'p'||to_char(i));
  4      end loop;
  5* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select i, count(*) from dropme group by i;

         I   COUNT(*)
---------- ----------
         1      33333
         2      33334
         3      33333

SQL> select count(*) from dropme where c='p1';

  COUNT(*)
----------
         1

SQL> show parameter opti

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      asynch
object_cache_optimal_size            integer     102400
optimizer_dynamic_sampling           integer     1
optimizer_features_enable            string      9.2.0
optimizer_index_caching              integer     75
optimizer_index_cost_adj             integer     25
optimizer_max_permutations           integer     10000
optimizer_mode                       string      CHOOSE
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> explain plan for select c from dropme partition (p1) where c='p1';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    12 |     2  (50)|       |       |
|*  1 |  INDEX RANGE SCAN    | DROPME_I    |     1 |    12 |     1   (0)|     1 |     1 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DROPME"."C"='p1')

12 rows selected.

SQL> explain plan for select c from dropme where c='p1' and i<2;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |     1 |    25 |     2  (50)|       |       |
|*  1 |  TABLE ACCESS BY LOCAL INDEX ROWID| DROPME      |     1 |    25 |     2  (50)|     1 |     1 |
|*  2 |   INDEX RANGE SCAN                | DROPME_I    |     1 |       |     1   (0)|     1 |     1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DROPME"."I"<2)
   2 - access("DROPME"."C"='p1')

14 rows selected.

SQL> exec dbms_stats.gather_table_stats (user, 'dropme', cascade=>true, method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> explain plan for select c from dropme where c='p1' and i<2;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name       | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |     1 |    14 |     2  (50)|       |       |
|*  1 |  TABLE ACCESS BY LOCAL INDEX ROWID| DROPME      |     1 |    14 |     2  (50)|     1 |     1 |
|*  2 |   INDEX RANGE SCAN                | DROPME_I    |     1 |       |     1   (0)|     1 |     1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DROPME"."I"<2)
   2 - access("DROPME"."C"='p1')

14 rows selected.
 

Hi Tom,
Thanks for the reply. So other then building new index with partition key in it, is there other workaround such as hint or something else to let the CBO not to scan the table?

Regards,

If say, table emp is range partitioned on salary column.
and there are some rows in the first partition , and
there may or may not be rows in other partitions.
Will the query SELECT MIN(SALARY) FROM EMP will only
scan first partition and stop or will it scan all the
partitions.

Can any body answer the above.

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt       date,
  4    salary   int,
  5    y        varchar2(30)
  6  )
  7  PARTITION BY RANGE (salary)
  8  (
  9    PARTITION part1 VALUES LESS THAN (1),
 10    PARTITION part2 VALUES LESS THAN (2),
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> insert into t select created, mod(rownum,3), rpad('*',30,'*') from all_objects;

49951 rows created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select min(salary) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 3225603066

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     3 |    76   (3)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      | 51250 |   150K|    76   (3)| 00:00:01 |     1 |     3 |
|   3 |    TABLE ACCESS FULL | T    | 51250 |   150K|    76   (3)| 00:00:01 |     1 |     3 |
---------------------------------------------------------------------------------------------

ops$tkyte%ORA10GR2> set autotrace off

If one is willing to list the partitions explicitly in increasing order, a nice workaround is to exploit the short-circuit properties of coalesce(). In 10.2.0.2:

dellera@ORACLE10> select coalesce (
  2  (select min(salary) from t partition (part1)),
  3  (select min(salary) from t partition (part2)),
  4  (select min(salary) from t partition (junk))
  5  ) as min_salary
  6  from dual;

MIN_SALARY
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 3618731627

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |       |     2   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |      |     1 |     2 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE      |      | 19439 | 38878 |    41   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS FULL          | T    | 19439 | 38878 |    41   (0)| 00:00:01 |     1 |     1 |
|   4 |     SORT AGGREGATE            |      |     1 |     3 |            |          |       |       |
|   5 |      PARTITION RANGE SINGLE   |      | 19440 | 58320 |    41   (0)| 00:00:01 |     2 |     2 |
|   6 |       TABLE ACCESS FULL       | T    | 19440 | 58320 |    41   (0)| 00:00:01 |     2 |     2 |
|   7 |        SORT AGGREGATE         |      |     1 |     3 |            |          |       |       |
|   8 |         PARTITION RANGE SINGLE|      | 19439 | 58317 |    41   (0)| 00:00:01 |     3 |     3 |
|   9 |          TABLE ACCESS FULL    | T    | 19439 | 58317 |    41   (0)| 00:00:01 |     3 |     3 |
|  10 |  FAST DUAL                    |      |     1 |       |     2   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       (snip)
       157  consistent gets
       (snip)

dellera@ORACLE10> select min(salary) from t;

MIN(SALARY)
-----------
          0


Execution Plan
----------------------------------------------------------
Plan hash value: 3225603066

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     3 |   121   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      | 58111 |   170K|   121   (1)| 00:00:02 |     1 |     3 |
|   3 |    TABLE ACCESS FULL | T    | 58111 |   170K|   121   (1)| 00:00:02 |     1 |     3 |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        (snip)
        503  consistent gets
        (snip)

dellera@ORACLE10> select min(salary) from t partition (part1);

MIN(SALARY)
-----------
          0


Execution Plan
----------------------------------------------------------
Plan hash value: 2993254470

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |     2 |    41   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     2 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      | 19439 | 38878 |    41   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS FULL    | T    | 19439 | 38878 |    41   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        (snip)
        157  consistent gets
        (snip)

So - it stopped as soon as the first partition was scanned. Note also the (very nice) indentetion of the three partition accesses on the first plan, that suggests the dependency.

It is semantically equivalent even when the table is empty or salary is null over all rows.

I'm sure that Tom knows about this technique, I'm just adding to the answer :)

Jonathan Lewis has suggested me (and He was right of course) that the indentation (which is based on the "depth" column of plan_table) of the first plan does not convey the id/parent_id hierarchy:

dellera@ORACLE10> explain plan for
  2  select coalesce (
  3  (select min(salary) from t partition (part1)),
  4  (select min(salary) from t partition (part2)),
  5  (select min(salary) from t partition (junk))
  6  ) as min_salary
  7  from dual;

dellera@ORACLE10> select id, parent_id, depth, OPERATION, options from plan_table order by id;

        ID  PARENT_ID      DEPTH OPERATION                      OPTIONS
---------- ---------- ---------- ------------------------------ -------------
         0                     0 SELECT STATEMENT
         1          0          1 SORT                           AGGREGATE
         2          1          2 PARTITION RANGE                SINGLE
         3          2          3 TABLE ACCESS                   FULL
         4          0          4 SORT                           AGGREGATE
         5          4          5 PARTITION RANGE                SINGLE
         6          5          6 TABLE ACCESS                   FULL
         7          0          7 SORT                           AGGREGATE
         8          7          8 PARTITION RANGE                SINGLE
         9          8          9 TABLE ACCESS                   FULL
        10          0          1 FAST DUAL

So - we cannot say that the indentation, in this case, means "dependency" between the three scalar subquery.
The short-circuit dependency exists (as shown above in the test case) but its existence is not reflected in the plan.

Hi

I am working with several databases which have DWH like nature. What I am finding is that the execution plan are so wrong that a query which takes seconds usually takes hours and ends up with Snapshot Too Old

For example:

Time Dimension D1 has 3000 rows
Fact Table F1 has 300 Million of rows, 30 partition, 10 million per partition, partitioned by date

the query looks like this

select
from F1, D1
where F1.p_date > trunc(sysdate - 10)
and F1.p_Date = D1.p_date

Instead of eliminating partitions in F1, the Optimizer chooses the predicate for D1, instead of F1.p_date it changed to

where D1.p_date > trunc(sysdate - 10)

So instead of elimination partition a Nested Loop is used

for i in (Select * from D1)
loop
select * from F1
end loop


I wonder has this soemthing to do with star_transformation_enabled? We have it set to FALSE

I understand very well partition pruning when we
do LIST or RANGE partitions. But I am unable to understand
how partitions will be pruned with HASH PARTITION.

Tom,

If you could please find some time to look into this query and the explain plan that follows:

Will there be any improvement using /*+ USE_HASH(e) */?

The query takes 10+ hours to run. The number of records in each table is:
t 20
t1 2mill
t2 2mill
t3 100k
t4 13mill and hash partitioned
t5 2mill

Can you comment on the query?

select count(*) from
t ,t1 , t2,t3 ,t4 ,t5
WHERE t.type = 'ABC'
AND t1.dt = t.last_eff_dt
AND t1.id = t2.id
AND t4.a_id = t1.a_id
AND t2.sec_id = t3.sec_id
AND t5.ast_id = t3.ast_id;


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 11 | | |
| 1 | SORT AGGREGATE | | 1 | 159 | | | |
| 2 | NESTED LOOPS | | 1 | 159 | 11 | | |
| 3 | NESTED LOOPS | | 1 | 150 | 9 | | |
| 4 | NESTED LOOPS | | 1 | 43 | 8 | | |
| 5 | NESTED LOOPS | | 1 | 34 | 5 | | |
| 6 | NESTED LOOPS | | 1 | 28 | 4 | | |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | TABLE ACCESS BY INDEX ROWID | t | 1 | 12 | 2 | | |
| 8 | INDEX SKIP SCAN | xpk_t | 1 | | 1 | | |
| 9 | TABLE ACCESS BY INDEX ROWID | t1 | 1 | 16 | 2 | | |
| 10 | INDEX RANGE SCAN | UIXt1 | 794K| | 2 | | |
| 11 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 12 | INDEX UNIQUE SCAN | XPKt4 | 1 | 6 | 1 | KEY | KEY |
| 13 | TABLE ACCESS BY INDEX ROWID | t2 | 6 | 54 | 3 | | |
| 14 | INDEX RANGE SCAN | IXt2 | 6 | | 2 | | |
| 15 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 16 | TABLE ACCESS BY LOCAL INDEX ROWID| t3 | 1 | 107 | 1 | KEY | KE
| 17 | INDEX UNIQUE SCAN | XPKt3 | 1 | | | KEY | KEY |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 18 | INDEX RANGE SCAN | IXt5 | 17 | 153 | 2 | | |
----------------------------------------------------------------------------------------------------

hi Tom, below is a very slow query that runs for several hours for only a few hundred records.

The wrk_csf_billing_account_xref table is a temp table containing the records we want to process(~100,000 records), and the BILL_CHARGE_DETAIL table is a partitioned table containing millions of detail records, partitioning on Bill_Dt.

From the plan, I see the problem is that the detail table is used as a driving table(ID 8) for the query. I think the temp table should have been used instead, as it has much less records, also when we hint the query with /*+ Leading(wrk_csf_billing_account_xref) */, it came back almost immediately.

I think the reason that Oracle used the detail table to drive the query is because the statistics are not up to date, but our envirnonment team do not want to update these statistic, which was generated from last last year, when it only has 10% of data. How do I prove the statistic is the problem?

Another problem I have is, the whole batch job usually completes in 11 hours for 150K bills on the same bill_dt. On certain dates, we rerun for 100 records, where the bill_dt contains several dates within the month, and this process ran at a multiple of 11 hours. for example, if the rerun is for July 4, 8 11, 15, 23, it would ran for 55 hours!! We notice the trend that the jobs runs longer at the end of the month, than the beginning of the month. It appears every run has the same cost, regardless how many bills being processed. Where should I look to confirm this? Or what causes could be associated with these type of problems?

Thanks again Tom ! I hope I have provided enough information, otherwise please let me know.

SQL> explain Plan for
  2  SELECT   bill_charge_detail.bill_charge_dtl_id, bill_charge_detail.bill_dt,
  3           bill_charge_detail.bill_document_id,
  4           bill_charge_detail.bill_charge_dtl_type_cd,
  5           bill_charge_detail.price_plan_grp_cd,
  6           bill_charge_detail.cmpnt_subscrptn_key_id,
  7           DECODE (bill_charge_detail.sum_dtl_bill_itm_ind, 'Y', '1', '2'),
  8           bill_charge_detail.free_unit_qty
  9      FROM bill_charge_detail,
 10           bill_recurring_charge_dtl,
 11           wrk_csf_billing_account_xref,
 12           wrk_csf_bill_cycle_date
 13     WHERE bill_charge_detail.bill_dt >= wrk_csf_bill_cycle_date.min_bill_dt
 14       AND bill_charge_detail.bill_dt <= wrk_csf_bill_cycle_date.max_bill_dt
 15       AND bill_charge_detail.bill_dt = wrk_csf_billing_account_xref.bill_dt
 16       AND bill_charge_detail.bill_document_id =
 17                                   wrk_csf_billing_account_xref.bill_document_id
 18       AND bill_charge_detail.bill_dt = bill_recurring_charge_dtl.bill_dt(+)
 19       AND bill_charge_detail.bill_charge_dtl_id = bill_recurring_charge_dtl.bill_charge_dtl_id(+
)
 20       AND bill_charge_detail.bill_itm_sum_lvl_cd != '01'
 21       AND bill_charge_detail.charge_rev_cd = 'RC'
 22       AND (   bill_charge_detail.paper_bill_dsply_ind = 'Y'
 23            OR bill_charge_detail.ebill_dsply_ind = 'Y'
 24            OR bill_charge_detail.intrnl_portal_dsply_ind = 'Y'
 25           )
 26       AND bill_charge_detail.bill_sect_cd IS NOT NULL
 27  ORDER BY bill_sect_cd, root_bundle_price_plan_cd, price_plan_grp_cd, var_cd;

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1064781282

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                              |     1 |   102 |     5  (20)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                         |                              |     1 |   102 |     5  (20)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS OUTER                   |                              |     1 |   102 |     4   (0)| 00:00:01 |       |      
|   3 |    NESTED LOOPS                        |                              |     1 |    88 |     3   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                       |                              |     1 |    74 |     3   (0)| 00:00:01 |       |       |
|   5 |      INDEX FULL SCAN                   | WRK_CSF_BILL_CYCLE_DATE_PK   |     1 |    16 |     1   (0)| 00:
|   6 |      PARTITION RANGE ITERATOR          |                              |     1 |    58 |     2   (0)| 00:00:01 |   KEY
|*  7 |       TABLE ACCESS BY LOCAL INDEX ROWID| BILL_CHARGE_DETAIL           |     1 |    58 |     2   (0
|*  8 |        INDEX RANGE SCAN                | BILL_CHARGE_DETAIL_IX1       |     1 |       |     2   (0)| 
|*  9 |     INDEX UNIQUE SCAN                  | WRK_CSF_BILLING_ACCT_XREF_PK |     1 |    14 |     0   (0)| 00
|  10 |    PARTITION RANGE ITERATOR            |                              |     1 |    14 |     1   (0)| 00:00:01 |   KEY |
|* 11 |     INDEX RANGE SCAN                   | BILL_RECURRING_CHARGE_DTL_PK |     1 |    14 |     1   (0)| 00:
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   7 - filter("BILL_CHARGE_DETAIL"."CHARGE_REV_CD"='RC' AND "BILL_CHARGE_DETAIL"."BILL_SECT_CD" IS N
              ("BILL_CHARGE_DETAIL"."INTRNL_PORTAL_DSPLY_IND"='Y' OR "BILL_CHARGE_DETAIL"."EBILL_DSPLY_IND"
              "BILL_CHARGE_DETAIL"."PAPER_BILL_DSPLY_IND"='Y') AND "BILL_CHARGE_DETAIL"."BILL_ITM_SUM_LVL_C
   8 - access("BILL_CHARGE_DETAIL"."BILL_DT">="WRK_CSF_BILL_CYCLE_DATE"."MIN_BILL_DT" AND
              "BILL_CHARGE_DETAIL"."BILL_DT"<="WRK_CSF_BILL_CYCLE_DATE"."MAX_BILL_DT")
   9 - access("BILL_CHARGE_DETAIL"."BILL_DT"="WRK_CSF_BILLING_ACCOUNT_XREF"."BILL_DT" AND
              "BILL_CHARGE_DETAIL"."BILL_DOCUMENT_ID"="WRK_CSF_BILLING_ACCOUNT_XREF"."BILL_DOCUMENT_ID")
  11 - access("BILL_CHARGE_DETAIL"."BILL_DT"="BILL_RECURRING_CHARGE_DTL"."BILL_DT"(+) AND
              "BILL_CHARGE_DETAIL"."BILL_CHARGE_DTL_ID"="BILL_RECURRING_CHARGE_DTL"."BILL_CHARGE_DTL_ID"(+)

31 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=8168 Bytes
          =16336)

   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY' (PROCEDUR
          E)

To brief you, we have a data warehouse database running on Oracle 10.2.0.3. Mostly all the tables are partitioned by month and few have two partitions for a month.

Daily at night, data is populated from multiple feeder databases (Oracle, SQL Server, Main Frames, text files,....) into this database.

Whenever, batch loads data into new partition, it takes very long to complete. After a day or two the batch functions smootly, i.e. completes on time.

I have captured Execution Plans for data loads when the batch completes on time (i.e, at the month end) and when it suffers performance issues (i.e. loading new partitions).
I see the executions plans differ and the elapsed time differs in multiples as a result. Attached you can see the plans for the same query.

Following is the background information, I think I should share with you:

Database: Oracle 10g (10.2.0.3)
OS: IBM - Aix 5.3 - ML -5
Processors: 10 Physical (40 Logical)
Memory: 14 GB

Initialization Parameter that might be of significance:
CURSOR_SHARING = FORCE

Snapshot Interval: 30 Minutes

Table Name: Authorization
Paritions: 49 (13 partitions contain full months data and remaining partitions have only the last day's data)
Partitioned on: As_Of_Date (column)
Partition Type: One Gregorian Month
Tablesize: 13.5 GB
Active Partitions Size: 1 GB (for the 13 partitions)
Rows: 80 Million

Total Indexes: 3
Partitioned Indexes: 2
Global Indexes: 1

Index Name:     SYS_C0010238
Unique:         Yes
Partitioned:    No
Index Columns:  AUTH_SEQ_KEY & AS_OF_DATE
CF:             91452068
Blocks:         375419 
Rows:           91452068
Size (GB):      5.75



Index Name:     IDX_AUTHORIZATION_1
Unique:         No
Partitioned:    Yes
Index Columns:  AS_OF_DATE
CF:             940360
Blocks:         153460
Rows:           81106590
Size (GB):      2.59 

Index Name:     IDX_AUTHORIZATION_2
Unique:         No
Partitioned:    Yes
Index Columns:  CUST_CODE
CF:             42264920
Blocks:         116710
Rows:           79968760
Size (GB):      2.03

Below is the *GOOD* plan

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
OFDMP         2543030685 OFDMP               1 10.2.0.3.0  NO  ofdmprd

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      4187 01-ÇÈÑêä -08 04:30:       98       2.9
  End Snap:      4188 01-ÇÈÑêä -08 05:00:       98       2.9
   Elapsed:               29.85 (mins)
   DB Time:               39.65 (mins)

SQL Summary                             DB/Inst: OFDMP/OFDMP  Snaps: 4187-4188

                Elapsed
   SQL Id      Time (ms)
------------- ----------
6k5jkqzvfz7gs      3,807
Module: uvsh.exe
UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = :B4 , OVER
STEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_OF_DATE = :B1

          -------------------------------------------------------------

SQL ID: 6k5jkqzvfz7gs                   DB/Inst: OFDMP/OFDMP  Snaps: 4187-4188
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = ...

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   1474318199                  3,807       22,5629          4188           4188
          -------------------------------------------------------------


Plan 1(PHV: 1474318199)
-----------------------

Plan Statistics                         DB/Inst: OFDMP/OFDMP  Snaps: 4187-4188
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                             3,807            0.0     0.2
CPU Time (ms)                                 3,807            0.0     0.4
Executions                                  225,629            N/A     N/A
Buffer Gets                                 676,889            3.0     8.2
Disk Reads                                        1            0.0     0.0
Parse Calls                                       1            0.0     0.0
Rows                                              0            0.0     N/A
User I/O Wait Time (ms)                           7            N/A     N/A
Cluster Wait Time (ms)                            0            N/A     N/A
Application Wait Time (ms)                        0            N/A     N/A
Concurrency Wait Time (ms)                        0            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 22            N/A     N/A
          -------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |               |       |       |     3 (100)|          |
|   1 |  UPDATE            | AUTHORIZATION |       |       |            |          |
|   2 |   INDEX UNIQUE SCAN| SYS_C0010238  |     1 |    27 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------



Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
6k5jkqzvfz7g UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_A
             AMT = :B4 , OVERSTEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_O
             F_DATE = :B1

Here's what happens when data is populated into new partition:

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
OFDMP         2543030685 OFDMP               1 10.2.0.3.0  NO  ofdmprd

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      4235 02-ÇÈÑêä -08 04:30:       98       3.1
  End Snap:      4236 02-ÇÈÑêä -08 05:00:       98       3.2
   Elapsed:               30.51 (mins)
   DB Time:               85.49 (mins)

SQL Summary                             DB/Inst: OFDMP/OFDMP  Snaps: 4235-4236

                Elapsed
   SQL Id      Time (ms)
------------- ----------
6k5jkqzvfz7gs    683,494
Module: uvsh.exe
UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = :B4 , OVER
STEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_OF_DATE = :B1

          -------------------------------------------------------------

SQL ID: 6k5jkqzvfz7gs                   DB/Inst: OFDMP/OFDMP  Snaps: 4235-4236
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = ...

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   3727740582                683,494          3381          4236           4236
          -------------------------------------------------------------


Plan 1(PHV: 3727740582)
-----------------------

Plan Statistics                         DB/Inst: OFDMP/OFDMP  Snaps: 4235-4236
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                           683,494          202.2    13.3
CPU Time (ms)                               643,617          190.4    20.7
Executions                                    3,381            N/A     N/A
Buffer Gets                               8,727,370        2,581.3     5.8
Disk Reads                                    2,572            0.8     0.6
Parse Calls                                       1            0.0     0.1
Rows                                              0            0.0     N/A
User I/O Wait Time (ms)                       1,648            N/A     N/A
Cluster Wait Time (ms)                            0            N/A     N/A
Application Wait Time (ms)                        0            N/A     N/A
Concurrency Wait Time (ms)                        1            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 22            N/A     N/A
          -------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Psto
------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |                     |       |       |     1 (100)|          |       |
|   1 |  UPDATE                             | AUTHORIZATION       |       |       |            |          |       |
|   2 |   PARTITION RANGE SINGLE            |                     |     1 |    56 |     1   (0)| 00:00:01 |   KEY |   KE
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| AUTHORIZATION       |     1 |    56 |     1   (0)| 00:00:01 |   KEY |   KE
|   4 |     INDEX RANGE SCAN                | IDX_AUTHORIZATION_1 |     1 |       |     1   (0)| 00:00:01 |   KEY |   KE
------------------------------------------------------------------------------------------------------------------------



Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
6k5jkqzvfz7g UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_A
             AMT = :B4 , OVERSTEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_O
             F_DATE = :B1

I can upload the table (rows=no) with statistics, if required.

When I change CURSOR_SHARING=EXACT on my test database (before executing the UPDATE statement), it picks the right index. As this is the production database and performance issues happen once in 15 days, I would like to make sure that I pick the right change.

Below is the script of my test case:

***********************************************************************************
     Worst Plan 
***********************************************************************************
comment on table authorization is 'worse_2';

alter session set cursor_sharing = force;

alter session set tracefile_identifier = 'Worse';

alter session set events '10053 trace name context forever, level 1';

UPDATE AUTHORIZATION
   SET AMORTZ_UNIT_AMT = 1 ,  INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/01/08', 'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/01/08', 'MM/DD/YY');

UPDATE AUTHORIZATION
   SET AMORTZ_UNIT_AMT = 1 ,  INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/06/08', 'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/06/08', 'MM/DD/YY');


alter session set events '10053 trace name context off';

exit

Execution plan from the trace file:

------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                            | Name               | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | UPDATE STATEMENT                     |                    |       |       |     1 |           |       |       |
| 1   |  UPDATE                              | AUTHORIZATION      |       |       |       |           |       |       |
| 2   |   PARTITION RANGE SINGLE             |                    |     1 |    56 |     1 |  00:00:01 | KEY   | KEY   |
| 3   |    TABLE ACCESS BY LOCAL INDEX ROWID | AUTHORIZATION      |     1 |    56 |     1 |  00:00:01 | KEY   | KEY   |
| 4   |     INDEX RANGE SCAN                 | IDX_AUTHORIZATION_1|     1 |       |     1 |  00:00:01 | KEY   | KEY   |
------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("AUTH_SEQ_KEY"=:SYS_B_4)
4 - access("AS_OF_DATE"=TO_DATE(:SYS_B_5,:SYS_B_6))



***********************************************************************************
     Best Plan 
***********************************************************************************

comment on table authorization is 'best';

alter session set tracefile_identifier = 'Best';

alter session set events '10053 trace name context forever, level 1';

UPDATE AUTHORIZATION
   SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 ,  OVERSTEP_DATE = to_date('04/01/08', 'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/01/08', 'MM/DD/YY');

UPDATE AUTHORIZATION
   SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 ,  OVERSTEP_DATE = to_date('04/06/08', 'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/06/08', 'MM/DD/YY');


alter session set events '10053 trace name context off';

Execution plan from the trace file:

============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id  | Operation           | Name         | Rows  | Bytes | Cost  | Time      |
-------------------------------------------+-----------------------------------+
| 0   | UPDATE STATEMENT    |              |       |       |     3 |           |
| 1   |  UPDATE             | AUTHORIZATION|       |       |       |           |
| 2   |   INDEX UNIQUE SCAN | SYS_C0010238 |     1 |    53 |     2 |  00:00:01 |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("AUTH_SEQ_KEY"='CPT22113622211362C02' AND "AS_OF_DATE"=TO_DATE('04/01/08','MM/DD/YY'))
 

I hope, I have furnished all the required information for you to analyze my situation. If you need any other information, please let me know.

Your comments are appreciated.

Take care !!!

Warm Regards

Momen

rajesh@10GR2> create table sales
  2  (
  3     SALES_ID                ,
  4     CUST_ID                 ,
  5     QUANTITY_SOLD   ,
  6     AMOUNT_SOLD             ,
  7     CUST_DATE
  8  )
  9  partition by range(CUST_DATE)
 10  subpartition by hash(cust_id)
 11  subpartition template
 12  (
 13    subpartition sp1,
 14    subpartition sp2,
 15    subpartition sp3,
 16    subpartition sp4
 17  )
 18  (
 19    partition p_q1_2005  values less than ( to_date('01-apr-2005','dd-mon-yyyy') ),
 20    partition p_q2_2005  values less than ( to_date('01-jul-2005','dd-mon-yyyy') ),
 21    partition p_q3_2005  values less than ( to_date('01-oct-2005','dd-mon-yyyy') ),
 22    partition p_q4_2005  values less than ( to_date('01-jan-2006','dd-mon-yyyy') ),
 23    partition p_q1_2006  values less than ( to_date('01-apr-2006','dd-mon-yyyy') ),
 24    partition p_q2_2006  values less than ( to_date('01-jul-2006','dd-mon-yyyy') ),
 25    partition p_q3_2006  values less than ( to_date('01-oct-2006','dd-mon-yyyy') ),
 26    partition p_q4_2006  values less than ( to_date('01-jan-2007','dd-mon-yyyy') ),
 27    partition p_q1_2007  values less than ( to_date('01-apr-2007','dd-mon-yyyy') ),
 28    partition p_q2_2007  values less than ( to_date('01-jul-2007','dd-mon-yyyy') ),
 29    partition p_q3_2007  values less than ( to_date('01-oct-2007','dd-mon-yyyy') ),
 30    partition p_q4_2007  values less than ( to_date('01-jan-2008','dd-mon-yyyy') ),
 31    partition p_q1_2008  values less than ( to_date('01-apr-2008','dd-mon-yyyy') ),
 32    partition p_q2_2008  values less than ( to_date('01-jul-2008','dd-mon-yyyy') ),
 33    partition p_q3_2008  values less than ( to_date('01-oct-2008','dd-mon-yyyy') ),
 34    partition p_q4_2008  values less than ( to_date('01-jan-2009','dd-mon-yyyy') ),
 35    partition p_q1_2009  values less than ( to_date('01-apr-2009','dd-mon-yyyy') ),
 36    partition p_q2_2009  values less than ( to_date('01-jul-2009','dd-mon-yyyy') ),
 37    partition p_q3_2009  values less than ( to_date('01-oct-2009','dd-mon-yyyy') ),
 38    partition p_q4_2009  values less than ( to_date('01-jan-2010','dd-mon-yyyy') ),
 39    partition p_q1_2010  values less than ( to_date('01-apr-2010','dd-mon-yyyy') ),
 40    partition p_q2_2010  values less than ( to_date('01-jul-2010','dd-mon-yyyy') ),
 41    partition p_q3_2010  values less than ( to_date('01-oct-2010','dd-mon-yyyy') ),
 42    partition p_q4_2010  values less than ( to_date('01-jan-2011','dd-mon-yyyy') ),
 43    partition p_q1_2011  values less than ( to_date('01-apr-2011','dd-mon-yyyy') ),
 44    partition p_q2_2011  values less than ( to_date('01-jul-2011','dd-mon-yyyy') ),
 45    partition p_q3_2011  values less than ( to_date('01-oct-2011','dd-mon-yyyy') ),
 46    partition p_q4_2011  values less than ( to_date('01-jan-2012','dd-mon-yyyy') )
 47  )
 48  nologging
 49  as
 50  select             SALES_ID         ,
 51                     CUST_ID          ,
 52                     QUANTITY_SOLD    ,
 53                     AMOUNT_SOLD      ,
 54                     trunc(CUST_DATE)
 55  from sales_data;

Table created.

Elapsed: 00:01:50.23
rajesh@10GR2>

rajesh@10GR2> select cust_id,sum(amount_sold)
  2  from sales
  3  group by cust_id
  4  having sum(amount_sold) > 10000
  5  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3947202833

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |     9 | 13043   (9)| 00:02:37 |       |       |
|*  1 |  FILTER                |       |       |       |            |          |       |       |
|   2 |   PARTITION HASH ALL   |       |     1 |     9 | 13043   (9)| 00:02:37 |     1 |     4 |
|   3 |    HASH GROUP BY       |       |     1 |     9 | 13043   (9)| 00:02:37 |       |       |
|   4 |     PARTITION RANGE ALL|       |    11M|   101M| 12107   (2)| 00:02:26 |     1 |    28 |
|   5 |      TABLE ACCESS FULL | SALES |    11M|   101M| 12107   (2)| 00:02:26 |     1 |   112 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SUM("AMOUNT_SOLD")>10000)

rajesh@10GR2>
rajesh@10GR2> set autotrace off;

Tom:

The tables sales is partitioned by quarter by RANGE for 7 years (so 28 Range partitions) and each is sub-partitioned on Hash by 4 (so total 112 Hash partitions).

The step (id=2) say that its started at 1 (Pstart =1) and ended at 4 (Pstop=4), What does they convey? Are they represents 4 Range partitions (or) 4 Hash subpartitions?

I was try to find the performance disparity between static partition pruning and dynamic partition pruning but the result could tell the difference. Would you please tell me if there is any performance difference between static pruning and dynamic pruning in theory?

Thanks.

I am seeing after upgrade from 12.1.0.2 to 19.11, explain plan has changed. Oracle query is running for long. We see that Oracle is doing full san partitions. in 12.1.0.2, Oracle was using "JOIN FILTER USE" but its missing in 19.11 plan.

What is the reason for this missing in 19.11 ?


12.1.0.2 plan:

26 | JOIN FILTER USE | :BF0001 | 23G| 891G| | 6703K (11)| | | | |
| 27 | PARTITION RANGE JOIN-FILTER | | 23G| 891G| | 6703K (11)| | | | |

Hey Chris,
Please find attached below the execution plan .

On 12.1.0.2:
**********

SQL_ID 9vcnv1knmv6a6
--------------------
WITH RUNDATE AS (     SELECT TO_DATE(:1 ) AS RUN_DATE, :2  AS
LIQ_LOOKBACK     FROM DUAL ),      ALL_BATCHES AS (          SELECT
BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE          FROM
OP_METRICS.E_BATCH_JOB          WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing', '2200NY_LIQ')
      AND BATCH_DATE BETWEEN (SELECT RUN_DATE - LIQ_LOOKBACK FROM
RUNDATE) AND (SELECT RUN_DATE FROM RUNDATE)            AND BATCH_STATUS
!= 'SCHEDULED'      ),      BATCHES AS (          SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
     FROM ALL_BATCHES          WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing') AND BATCH_DATE = (SELECT
RUN_DATE FROM RUNDATE)          UNION ALL          SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
     FROM ALL_BATCHES          WHERE BATCH_NAME IN ('2200NY_LIQ')
AND BATCH_DATE =               (                   SELECT
MAX(BATCH_DATE)                   FROM ALL_BATCHES
WHERE BATCH_NAME IN ('2200NY_LIQ')               )      ),
PRICES AS (          SELECT P.INSTRUMENT_ID,
GREATEST(S.MODIFIED_DURATION, 0)
                                          AS MODIFIED_DURATION,
        MIN(CASE WHEN S.MODIFIED_DURATION != 0 THEN S.MODIFIED_DURATION
END)                     OVER ()
                                                               AS
MIN_DURATION,                 GREATEST(P.BID_YIELD, 0)

AS BID_YIELD,                 PERCENTILE_DISC(0.97) WITHIN GROUP (ORDER
BY GREATEST(P.BID_YIELD, 0) ASC)                                 OVER
(PARTITION BY CASE WHEN P.BID_YIELD IS NULL THEN 0 ELSE 1 END)
                     AS BID_YIELD_97TH,                 PERCENT_RANK()
                       OVER (PARTITION BY CASE WHEN P.BID_YIELD IS NULL
THEN 0 ELSE 1 END ORDER BY GREATEST(P.BID_YIELD, 0) ASC) AS
BID_YIELD_RANK          FROM BATCHES B                   JOIN
EODP.FF_INC_PRICE P                        ON B.SNAP_ID
= P.SNAP_ID AND B.BATCH_ID = P.BATCH_ID AND P.SNAP_DATE = B.BATCH_DATE
AND P.PRICE_TYPE_ID = 5                   LEFT JOIN
EODP.F_SENSITIVITY S ON P.SENSITIVITY_ID = S.SENSITIVITY_ID AND
P.SNAP_ID = S.SNAP_ID AND P.INSTRUMENT_ID = S.INSTRUMENT_ID
WHERE B.BATCH_NAME IN ('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing')
  ),      INSTRUMENTS AS (          SELECT DISTINCT INSTRUMENT_ID
   FROM PRICES      ),      LIQ AS (          SELECT
LB.INSTRUMENT_ID, LB.AMIHUD, U.LIQ_SCORE          FROM BATCHES B
                 JOIN LIQ.F_LIQ_BASE LB
      ON B.BATCH_ID = LB.BATCH_ID AND B.SNAP_ID = LB.SNAP_ID AND
B.BATCH_DATE = LB.SNAP_DATE                   JOIN
LIQ.F_LIQ_UNIVERSE    U                        ON
LB.BATCH_ID = U.BATCH_ID AND LB.SNAP_ID = U.SNAP_ID AND LB.SNAP_DATE =
U.SNAP_DATE AND                           LB.INSTRUMENT_ID =
U.INSTRUMENT_ID AND U.UNIVERSE_GROUP_ID = 2          WHERE B.BATCH_NAME
= '2200NY_LIQ'      ),      FI AS (          SELECT
INSTRUMENT_ID, OUTSTANDING_AMOUNT, ISSUE_AMOUNT, CURRENT_COUPON_RATE,
MATURITY_DATE          FROM IG.F_INCOME      ),      MASTER AS
(          SELECT INSTRUMENT_ID, ISSUE_DATE, ORGANIZATION_ID
FROM IG.INSTR_MASTER      ),      RATINGS AS (          SELECT
R.INSTRUMENT_ID,                 R.RATING,
T.DESCRIPTION
                           AS AGENCY,                 ROW_NUMBER() OVER
(PARTITION BY R.INSTRUMENT_ID, R.RATING_AGENCY_CODE ORDER BY
R.RATING_DATE DESC) AS RN          FROM IG.INSTR_RATINGS   R
              JOIN IG.RATING_AGENCY_TYPES T ON R.RATING_AGENCY_CODE
= T.VALUE

Plan hash value: 2447948827

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                               |       |       |       |  7492K(100)|          |       |       |
|   1 |  TEMP TABLE TRANSFORMATION                 |                               |       |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT                           |                               |       |       |       |            |          |       |       |
|   3 |    INLIST ITERATOR                         |                               |       |       |       |            |          |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED    | E_BATCH_JOB                   |    15 |   825 |       |    23   (0)| 00:00:01 |       |       |
|   5 |      INDEX RANGE SCAN                      | E_BATCH_JOB_UK1               |    20 |       |       |     5   (0)| 00:00:01 |       |       |
|   6 |       FAST DUAL                            |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   7 |       FAST DUAL                            |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   8 |   LOAD AS SELECT                           |                               |       |       |       |            |          |       |       |
|   9 |    UNION-ALL                               |                               |       |       |       |            |          |       |       |
|  10 |     VIEW                                   |                               |    15 |  1350 |       |     2   (0)| 00:00:01 |       |       |
|  11 |      TABLE ACCESS STORAGE FULL             | SYS_TEMP_0FD9FC8C7_FE44BEBD   |    15 |   660 |       |     2   (0)| 00:00:01 |       |       |
|  12 |      FAST DUAL                             |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|  13 |     VIEW                                   |                               |    15 |  1350 |       |     2   (0)| 00:00:01 |       |       |
|  14 |      TABLE ACCESS STORAGE FULL             | SYS_TEMP_0FD9FC8C7_FE44BEBD   |    15 |   660 |       |     2   (0)| 00:00:01 |       |       |
|  15 |      SORT AGGREGATE                        |                               |     1 |    61 |       |            |          |       |       |
|  16 |       VIEW                                 |                               |    15 |   915 |       |     2   (0)| 00:00:01 |       |       |
|  17 |        TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FD9FC8C7_FE44BEBD   |    15 |   660 |       |     2   (0)| 00:00:01 |       |       |
|  18 |   LOAD AS SELECT                           |                               |       |       |       |            |          |       |       |
|  19 |    WINDOW SORT                             |                               |    30 |  4500 |       |  6714K (12)| 00:04:23 |       |       |
|  20 |     NESTED LOOPS OUTER                     |                               |    30 |  4500 |       |  6714K (12)| 00:04:23 |       |       |
|  21 |      HASH JOIN                             |                               |    30 |  3810 |       |  6713K (12)| 00:04:23 |       |       |
|  22 |       JOIN FILTER CREATE                   | :BF0001                       |    30 |  2610 |       |     2   (0)| 00:00:01 |       |       |
|  23 |        PART JOIN FILTER CREATE             | :BF0000                       |    30 |  2610 |       |     2   (0)| 00:00:01 |       |       |
|  24 |         VIEW                               |                               |    30 |  2610 |       |     2   (0)| 00:00:01 |       |       |
|  25 |          TABLE ACCESS STORAGE FULL         | SYS_TEMP_0FD9FC8C8_FE44BEBD   |    30 |  2700 |       |     2   (0)| 00:00:01 |       |       |
|  26 |       JOIN FILTER USE                      | :BF0001                       |    23G|   890G|       |  6606K (11)| 00:04:19 |       |       |
|  27 |        PARTITION RANGE JOIN-FILTER         |                               |    23G|   890G|       |  6606K (11)| 00:04:19 |:BF0000|:BF0000|
|  28 |         PARTITION HASH ALL                 |                               |    23G|   890G|       |  6606K (11)| 00:04:19 |     1 |  LAST |
|  29 |          TABLE ACCESS STORAGE FULL         | FF_INC_PRICE           |    23G|   890G|       |  6606K (11)| 00:04:19 |     1 |1048575|
|  30 |      TABLE ACCESS BY GLOBAL INDEX ROWID    | F_SENSITIVITY                 |     1 |    23 |       |     4   (0)| 00:00:01 | ROWID | ROWID |
|  31 |       INDEX UNIQUE SCAN                    | F_SENSITIVITY_PK              |     1 |       |       |     3   (0)| 00:00:01 |       |       |
|  32 |   LOAD AS SELECT                           |                               |       |       |       |            |          |       |       |
|  33 |    WINDOW SORT                             |                               |    16M|  4174M|  4473M|   756K  (2)| 00:00:30 |       |       |
|  34 |     HASH JOIN                              |                               |    16M|  4174M|       | 63406  (16)| 00:00:03 |       |       |
|  35 |      JOIN FILTER CREATE                    | :BF0000                       |     6 |  1488 |       |     4   (0)| 00:00:01 |       |       |
|  36 |       MERGE JOIN CARTESIAN                 |                               |     6 |  1488 |       |     4   (0)| 00:00:01 |       |       |
|  37 |        TABLE ACCESS STORAGE FULL           | RATING_TYPES                  |     1 |   124 |       |     2   (0)| 00:00:01 |       |       |
|  38 |        BUFFER SORT                         |                               |     6 |   744 |       |     2   (0)| 00:00:01 |       |       |
|  39 |         TABLE ACCESS STORAGE FULL          | RATING_AGENCY_TYPES           |     6 |   744 |       |     2   (0)| 00:00:01 |       |       |
|  40 |      JOIN FILTER USE                       | :BF0000                       |   368M|  8791M|       | 61749  (13)| 00:00:03 |       |       |
|  41 |       TABLE ACCESS STORAGE FULL            | INSTR_RATINGS                 |   368M|  8791M|       | 61749  (13)| 00:00:03 |       |       |
|  42 |   HASH JOIN OUTER                          |                               |     3 |  1890 |       | 22355   (6)| 00:00:01 |       |       |
|  43 |    HASH JOIN OUTER                         |                               |     2 |   946 |       | 14957   (6)| 00:00:01 |       |       |
|  44 |     NESTED LOOPS OUTER                     |                               |     1 |   316 |       |  7559   (6)| 00:00:01 |       |       |
|  45 |      HASH JOIN OUTER                       |                               |     1 |   288 |       |  7407   (6)| 00:00:01 |       |       |
|  46 |       HASH JOIN OUTER                      |                               |     1 |   210 |       |  7405   (6)| 00:00:01 |       |       |
|  47 |        NESTED LOOPS OUTER                  |                               |     1 |    53 |       |     7  (15)| 00:00:01 |       |       |
|  48 |         NESTED LOOPS OUTER                 |                               |     1 |    30 |       |     5  (20)| 00:00:01 |       |       |
|  49 |          VIEW                              |                               |     1 |    13 |       |     3  (34)| 00:00:01 |       |       |
|  50 |           HASH UNIQUE                      |                               |     1 |     7 |       |     3  (34)| 00:00:01 |       |       |
|  51 |            VIEW                            |                               |     1 |     7 |       |     2   (0)| 00:00:01 |       |       |
|  52 |             TABLE ACCESS STORAGE FULL      | SYS_TEMP_0FD9FC8C9_FE44BEBD   |     1 |    72 |       |     2   (0)| 00:00:01 |       |       |
|  53 |          TABLE ACCESS BY INDEX ROWID       | INSTR_MASTER                  |     1 |    17 |       |     2   (0)| 00:00:01 |       |       |
|  54 |           INDEX UNIQUE SCAN                | INSTR_MASTER_PK               |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  55 |         TABLE ACCESS BY INDEX ROWID        | F_INCOME                      |     1 |    23 |       |     2   (0)| 00:00:01 |       |       |
|  56 |          INDEX UNIQUE SCAN                 | F_INCOME_PK                   |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  57 |        VIEW                                |                               |    16M|  2400M|       |  7326   (5)| 00:00:01 |       |       |
|  58 |         TABLE ACCESS STORAGE FULL          | SYS_TEMP_0FD9FC8CA_FE44BEBD   |    16M|  2217M|       |  7326   (5)| 00:00:01 |       |       |
|  59 |       VIEW                                 |                               |     1 |    78 |       |     2   (0)| 00:00:01 |       |       |
|  60 |        TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FD9FC8C9_FE44BEBD   |     1 |    72 |       |     2   (0)| 00:00:01 |       |       |
|  61 |      VIEW PUSHED PREDICATE                 |                               |     1 |    28 |       |   151   (0)| 00:00:01 |       |       |
|  62 |       NESTED LOOPS                         |                               |     1 |   156 |       |   151   (0)| 00:00:01 |       |       |
|  63 |        NESTED LOOPS                        |                               |    30 |   156 |       |   151   (0)| 00:00:01 |       |       |
|  64 |         NESTED LOOPS                       |                               |    30 |  3690 |       |    62   (0)| 00:00:01 |       |       |
|  65 |          VIEW                              |                               |    30 |  2610 |       |     2   (0)| 00:00:01 |       |       |
|  66 |           TABLE ACCESS STORAGE FULL        | SYS_TEMP_0FD9FC8C8_FE44BEBD   |    30 |  2700 |       |     2   (0)| 00:00:01 |       |       |
|  67 |          PARTITION RANGE ITERATOR          |                               |     1 |    36 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  68 |           TABLE ACCESS BY LOCAL INDEX ROWID| F_LIQ_BASE                    |     1 |    36 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  69 |            INDEX UNIQUE SCAN               | F_LIQ_BASE_UNQ_CONST          |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  70 |         PARTITION RANGE ITERATOR           |                               |     1 |       |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  71 |          INDEX UNIQUE SCAN                 | F_LIQ_UNIVERSE_UNQ_CONST      |     1 |       |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  72 |        TABLE ACCESS BY LOCAL INDEX ROWID   | F_LIQ_UNIVERSE                |     1 |    33 |       |     3   (0)| 00:00:01 |     1 |     1 |
|  73 |     VIEW                                   |                               |    16M|  2400M|       |  7326   (5)| 00:00:01 |       |       |
|  74 |      TABLE ACCESS STORAGE FULL             | SYS_TEMP_0FD9FC8CA_FE44BEBD   |    16M|  2217M|       |  7326   (5)| 00:00:01 |       |       |
|  75 |    VIEW                                    |                               |    16M|  2400M|       |  7326   (5)| 00:00:01 |       |       |
|  76 |     TABLE ACCESS STORAGE FULL              | SYS_TEMP_0FD9FC8CA_FE44BEBD   |    16M|  2217M|       |  7326   (5)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$7C70BD48
   2 - SEL$22
   4 - SEL$22       / E_BATCH_JOB@SEL$22
   5 - SEL$22       / E_BATCH_JOB@SEL$22
   6 - SEL$CBF63DBF / DUAL@SEL$21
   7 - SEL$11594485 / DUAL@SEL$21
   8 - SET$1
  10 - SEL$96EC8427 / ALL_BATCHES@SEL$25
  11 - SEL$96EC8427 / T1@SEL$96EC8427
  12 - SEL$CEF22074 / DUAL@SEL$21
  13 - SEL$96EC8426 / ALL_BATCHES@SEL$27
  14 - SEL$96EC8426 / T1@SEL$96EC8426
  15 - SEL$28
  16 - SEL$96EC8425 / ALL_BATCHES@SEL$28
  17 - SEL$96EC8425 / T1@SEL$96EC8425
  18 - SEL$45D930ED
  24 - SEL$298E7975 / B@SEL$19
  25 - SEL$298E7975 / T1@SEL$298E7975
  29 - SEL$45D930ED / P@SEL$19
  30 - SEL$45D930ED / S@SEL$20
  31 - SEL$45D930ED / S@SEL$20
  32 - SEL$B21719C5
  37 - SEL$B21719C5 / Y@SEL$16
  39 - SEL$B21719C5 / T@SEL$15
  41 - SEL$B21719C5 / R@SEL$15
  49 - SEL$30       / I@SEL$2
  50 - SEL$30
  51 - SEL$B75AFF3E / PRICES@SEL$30
  52 - SEL$B75AFF3E / T1@SEL$B75AFF3E
  53 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
  54 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
  55 - SEL$7C70BD48 / F_INCOME@SEL$32
  56 - SEL$7C70BD48 / F_INCOME@SEL$32
  57 - SEL$E2979EE2 / R_SP@SEL$11
  58 - SEL$E2979EE2 / T1@SEL$E2979EE2
  59 - SEL$B75AFF3D / P@SEL$1
  60 - SEL$B75AFF3D / T1@SEL$B75AFF3D
  61 - SEL$F84B98B6 / L@SEL$13
  62 - SEL$F84B98B6
  65 - SEL$298E7974 / B@SEL$17
  66 - SEL$298E7974 / T1@SEL$298E7974
  68 - SEL$F84B98B6 / LB@SEL$17
  69 - SEL$F84B98B6 / LB@SEL$17
  71 - SEL$F84B98B6 / U@SEL$18
  72 - SEL$F84B98B6 / U@SEL$18
  73 - SEL$E2979EE1 / R_FITCH@SEL$9
  74 - SEL$E2979EE1 / T1@SEL$E2979EE1
  75 - SEL$E2979EE0 / R_MOODY@SEL$7
  76 - SEL$E2979EE0 / T1@SEL$E2979EE0

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$CBF63DBF")
      MERGE(@"SEL$BFEE6DA6")
      OUTLINE_LEAF(@"SEL$11594485")
      MERGE(@"SEL$BFEE6DA5")
      OUTLINE_LEAF(@"SEL$22")
      OUTLINE_LEAF(@"SEL$CEF22074")
      MERGE(@"SEL$21")
      OUTLINE_LEAF(@"SEL$96EC8427")
      OUTLINE_LEAF(@"SEL$25")
      OUTLINE_LEAF(@"SEL$96EC8425")
      MATERIALIZE(@"SEL$22")
      OUTLINE_LEAF(@"SEL$28")
      OUTLINE_LEAF(@"SEL$96EC8426")
      OUTLINE_LEAF(@"SEL$27")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$298E7975")
      OUTLINE_LEAF(@"SEL$45D930ED")
      MERGE(@"SEL$D2DE2538")
      OUTLINE_LEAF(@"SEL$B21719C5")
      MERGE(@"SEL$803786B2")
      OUTLINE_LEAF(@"SEL$B75AFF3E")
      OUTLINE_LEAF(@"SEL$30")
      OUTLINE_LEAF(@"SEL$B75AFF3D")
      MATERIALIZE(@"SEL$45D930ED")
      OUTLINE_LEAF(@"SEL$E2979EE0")
      MATERIALIZE(@"SEL$B21719C5")
      OUTLINE_LEAF(@"SEL$E2979EE1")
      OUTLINE_LEAF(@"SEL$E2979EE2")
      OUTLINE_LEAF(@"SEL$298E7974")
      MATERIALIZE(@"SET$1")
      OUTLINE_LEAF(@"SEL$F84B98B6")
      PUSH_PRED(@"SEL$7C70BD48" "L"@"SEL$13" 1)
      OUTLINE_LEAF(@"SEL$7C70BD48")
      MERGE(@"SEL$32")
      MERGE(@"SEL$33")
      OUTLINE(@"SEL$23")
      OUTLINE(@"SEL$BFEE6DA6")
      OUTLINE(@"SEL$24")
      OUTLINE(@"SEL$BFEE6DA5")
      OUTLINE(@"SEL$26")
      OUTLINE(@"SEL$21")
      OUTLINE(@"SEL$22")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$7153C5F2")
      ANSI_REARCH(@"SEL$29")
      OUTLINE(@"SEL$D2DE2538")
      MERGE(@"SEL$19")
      OUTLINE(@"SEL$34")
      OUTLINE(@"SEL$803786B2")
      MERGE(@"SEL$15")
      OUTLINE(@"SEL$45D930ED")
      MERGE(@"SEL$D2DE2538")
      OUTLINE(@"SEL$B21719C5")
      MERGE(@"SEL$803786B2")
      OUTLINE(@"SEL$ED95C77E")
      MERGE(@"SEL$D50F4D64")
      OUTLINE(@"SEL$7C70BD48")
      MERGE(@"SEL$32")
      MERGE(@"SEL$33")
      OUTLINE(@"SEL$28ED9374")
      MERGE(@"SEL$4E060E5B")
      OUTLINE(@"SEL$32")
      OUTLINE(@"SEL$33")
      OUTLINE(@"SEL$29")
      OUTLINE(@"SEL$2FEECD4C")
      ANSI_REARCH(@"SEL$20")
      OUTLINE(@"SEL$19")
      OUTLINE(@"SEL$16")
      OUTLINE(@"SEL$15")
      OUTLINE(@"SEL$31")
      OUTLINE(@"SEL$D50F4D64")
      MERGE(@"SEL$17")
      OUTLINE(@"SEL$35")
      OUTLINE(@"SEL$4E060E5B")
      MERGE(@"SEL$13")
      MERGE(@"SEL$72AEFE3E")
      OUTLINE(@"SEL$20")
      OUTLINE(@"SEL$18")
      OUTLINE(@"SEL$17")
      OUTLINE(@"SEL$14")
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$72AEFE3E")
      MERGE(@"SEL$11")
      MERGE(@"SEL$B97648DD")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$11")
      OUTLINE(@"SEL$B97648DD")
      MERGE(@"SEL$096E5AED")
      MERGE(@"SEL$9")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$096E5AED")
      MERGE(@"SEL$15E987C1")
      MERGE(@"SEL$7")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$15E987C1")
      MERGE(@"SEL$5")
      MERGE(@"SEL$7237DA6D")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$7237DA6D")
      MERGE(@"SEL$3")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$7C70BD48" "I"@"SEL$2")
      INDEX_RS_ASC(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33" ("INSTR_MASTER"."INSTRUMENT_ID"))
      INDEX_RS_ASC(@"SEL$7C70BD48" "F_INCOME"@"SEL$32" ("F_INCOME"."INSTRUMENT_ID"))
      NO_ACCESS(@"SEL$7C70BD48" "R_SP"@"SEL$11")
      NO_ACCESS(@"SEL$7C70BD48" "P"@"SEL$1")
      NO_ACCESS(@"SEL$7C70BD48" "L"@"SEL$13")
      NO_ACCESS(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
      NO_ACCESS(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
      LEADING(@"SEL$7C70BD48" "I"@"SEL$2" "INSTR_MASTER"@"SEL$33" "F_INCOME"@"SEL$32" "R_SP"@"SEL$11" "P"@"SEL$1" "L"@"SEL$13"
              "R_FITCH"@"SEL$9" "R_MOODY"@"SEL$7")
      USE_NL(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33")
      USE_NL(@"SEL$7C70BD48" "F_INCOME"@"SEL$32")
      USE_HASH(@"SEL$7C70BD48" "R_SP"@"SEL$11")
      USE_HASH(@"SEL$7C70BD48" "P"@"SEL$1")
      USE_NL(@"SEL$7C70BD48" "L"@"SEL$13")
      USE_HASH(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
      USE_HASH(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
      FULL(@"SEL$E2979EE2" "T1"@"SEL$E2979EE2")
      FULL(@"SEL$E2979EE1" "T1"@"SEL$E2979EE1")
      FULL(@"SEL$E2979EE0" "T1"@"SEL$E2979EE0")
      FULL(@"SEL$B75AFF3D" "T1"@"SEL$B75AFF3D")
      NO_ACCESS(@"SEL$30" "PRICES"@"SEL$30")
      USE_HASH_AGGREGATION(@"SEL$30")
      NO_ACCESS(@"SEL$F84B98B6" "B"@"SEL$17")
      INDEX_RS_ASC(@"SEL$F84B98B6" "LB"@"SEL$17" ("F_LIQ_BASE"."SNAP_DATE" "F_LIQ_BASE"."INSTRUMENT_ID"
              "F_LIQ_BASE"."SNAP_ID"))
      INDEX(@"SEL$F84B98B6" "U"@"SEL$18" ("F_LIQ_UNIVERSE   "."SNAP_DATE" "F_LIQ_UNIVERSE   "."INSTRUMENT_ID"
              "F_LIQ_UNIVERSE   "."SNAP_ID" "F_LIQ_UNIVERSE   "."UNIVERSE_GROUP_ID"))
      LEADING(@"SEL$F84B98B6" "B"@"SEL$17" "LB"@"SEL$17" "U"@"SEL$18")
      USE_NL(@"SEL$F84B98B6" "LB"@"SEL$17")
      USE_NL(@"SEL$F84B98B6" "U"@"SEL$18")
      NLJ_BATCHING(@"SEL$F84B98B6" "U"@"SEL$18")
      FULL(@"SEL$298E7974" "T1"@"SEL$298E7974")
      FULL(@"SEL$B75AFF3E" "T1"@"SEL$B75AFF3E")
      FULL(@"SEL$B21719C5" "Y"@"SEL$16")
      FULL(@"SEL$B21719C5" "T"@"SEL$15")
      FULL(@"SEL$B21719C5" "R"@"SEL$15")
      LEADING(@"SEL$B21719C5" "Y"@"SEL$16" "T"@"SEL$15" "R"@"SEL$15")
      USE_MERGE_CARTESIAN(@"SEL$B21719C5" "T"@"SEL$15")
      USE_HASH(@"SEL$B21719C5" "R"@"SEL$15")
      PX_JOIN_FILTER(@"SEL$B21719C5" "R"@"SEL$15")
      NO_ACCESS(@"SEL$45D930ED" "B"@"SEL$19")
      FULL(@"SEL$45D930ED" "P"@"SEL$19")
      INDEX_RS_ASC(@"SEL$45D930ED" "S"@"SEL$20" ("F_SENSITIVITY"."SENSITIVITY_ID"))
      LEADING(@"SEL$45D930ED" "B"@"SEL$19" "P"@"SEL$19" "S"@"SEL$20")
      USE_HASH(@"SEL$45D930ED" "P"@"SEL$19")
      USE_NL(@"SEL$45D930ED" "S"@"SEL$20")
      PX_JOIN_FILTER(@"SEL$45D930ED" "P"@"SEL$19")
      INDEX_RS_ASC(@"SEL$22" "E_BATCH_JOB"@"SEL$22" ("E_BATCH_JOB"."BATCH_NAME" "E_BATCH_JOB"."BATCH_DATE"
              "E_BATCH_JOB"."BATCH_INSTANCE_NO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$22" "E_BATCH_JOB"@"SEL$22")
      PUSH_SUBQ(@"SEL$11594485")
      PUSH_SUBQ(@"SEL$CBF63DBF")
      ORDER_SUBQ(@"SEL$22" "SEL$CBF63DBF" "SEL$11594485")
      NO_ACCESS(@"SEL$27" "ALL_BATCHES"@"SEL$27")
      PUSH_SUBQ(@"SEL$28")
      NO_ACCESS(@"SEL$25" "ALL_BATCHES"@"SEL$25")
      PUSH_SUBQ(@"SEL$CEF22074")
      FULL(@"SEL$96EC8427" "T1"@"SEL$96EC8427")
      FULL(@"SEL$96EC8426" "T1"@"SEL$96EC8426")
      NO_ACCESS(@"SEL$28" "ALL_BATCHES"@"SEL$28")
      FULL(@"SEL$96EC8425" "T1"@"SEL$96EC8425")
      FULL(@"SEL$298E7975" "T1"@"SEL$298E7975")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (DATE): 08/27/2021 00:00:00
   2 - :2 (NUMBER): 7

On 19.11.0.0:
=========

SQL_ID 9vcnv1knmv6a6
--------------------
WITH RUNDATE AS (     SELECT TO_DATE(:1 ) AS RUN_DATE, :2  AS
LIQ_LOOKBACK     FROM DUAL ),      ALL_BATCHES AS (          SELECT
BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE          FROM
OP_METRICS.E_BATCH_JOB          WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing', '2200NY_LIQ')
      AND BATCH_DATE BETWEEN (SELECT RUN_DATE - LIQ_LOOKBACK FROM
RUNDATE) AND (SELECT RUN_DATE FROM RUNDATE)            AND BATCH_STATUS
!= 'SCHEDULED'      ),      BATCHES AS (          SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
     FROM ALL_BATCHES          WHERE BATCH_NAME IN
('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing') AND BATCH_DATE = (SELECT
RUN_DATE FROM RUNDATE)          UNION ALL          SELECT /*+
materialize */ BATCH_ID, SNAP_ID, BATCH_NAME, BATCH_DATE, IS_ACTIVE
     FROM ALL_BATCHES          WHERE BATCH_NAME IN ('2200NY_LIQ')
AND BATCH_DATE =               (                   SELECT
MAX(BATCH_DATE)                   FROM ALL_BATCHES
WHERE BATCH_NAME IN ('2200NY_LIQ')               )      ),
PRICES AS (          SELECT P.INSTRUMENT_ID,
GREATEST(S.MODIFIED_DURATION, 0)
                                          AS MODIFIED_DURATION,
        MIN(CASE WHEN S.MODIFIED_DURATION != 0 THEN S.MODIFIED_DURATION
END)                     OVER ()
                                                               AS
MIN_DURATION,                 GREATEST(P.BID_YIELD, 0)

AS BID_YIELD,                 PERCENTILE_DISC(0.97) WITHIN GROUP (ORDER
BY GREATEST(P.BID_YIELD, 0) ASC)                                 OVER
(PARTITION BY CASE WHEN P.BID_YIELD IS NULL THEN 0 ELSE 1 END)
                     AS BID_YIELD_97TH,                 PERCENT_RANK()
                       OVER (PARTITION BY CASE WHEN P.BID_YIELD IS NULL
THEN 0 ELSE 1 END ORDER BY GREATEST(P.BID_YIELD, 0) ASC) AS
BID_YIELD_RANK          FROM BATCHES B                   JOIN
EODP.FF_INC_PRICE P                        ON B.SNAP_ID
= P.SNAP_ID AND B.BATCH_ID = P.BATCH_ID AND P.SNAP_DATE = B.BATCH_DATE
AND P.PRICE_TYPE_ID = 5                   LEFT JOIN
EODP.F_SENSITIVITY S ON P.SENSITIVITY_ID = S.SENSITIVITY_ID AND
P.SNAP_ID = S.SNAP_ID AND P.INSTRUMENT_ID = S.INSTRUMENT_ID
WHERE B.BATCH_NAME IN ('USIG_1600NY_Pricing', 'USHY_1600NY_Pricing')
  ),      INSTRUMENTS AS (          SELECT DISTINCT INSTRUMENT_ID
   FROM PRICES      ),      LIQ AS (          SELECT
LB.INSTRUMENT_ID, LB.AMIHUD, U.LIQ_SCORE          FROM BATCHES B
                 JOIN LIQ.F_LIQ_BASE LB
      ON B.BATCH_ID = LB.BATCH_ID AND B.SNAP_ID = LB.SNAP_ID AND
B.BATCH_DATE = LB.SNAP_DATE                   JOIN
LIQ.F_LIQ_UNIVERSE U                        ON
LB.BATCH_ID = U.BATCH_ID AND LB.SNAP_ID = U.SNAP_ID AND LB.SNAP_DATE =
U.SNAP_DATE AND                           LB.INSTRUMENT_ID =
U.INSTRUMENT_ID AND U.UNIVERSE_GROUP_ID = 2          WHERE B.BATCH_NAME
= '2200NY_LIQ'      ),      FI AS (          SELECT
INSTRUMENT_ID, OUTSTANDING_AMOUNT, ISSUE_AMOUNT, CURRENT_COUPON_RATE,
MATURITY_DATE          FROM IG.F_INCOME      ),      MASTER AS
(          SELECT INSTRUMENT_ID, ISSUE_DATE, ORGANIZATION_ID
FROM IG.INSTR_MASTER      ),      RATINGS AS (          SELECT
R.INSTRUMENT_ID,                 R.RATING,
T.DESCRIPTION
                           AS AGENCY,                 ROW_NUMBER() OVER
(PARTITION BY R.INSTRUMENT_ID, R.RATING_AGENCY_CODE ORDER BY
R.RATING_DATE DESC) AS RN          FROM IG.INSTR_RATINGS R
              JOIN IG.RATING_AGENCY_TYPES T ON R.RATING_AGENCY_CODE
= T.VALUE

Plan hash value: 2545680846

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                               |       |       |       |  7504K(100)|          |       |       |
|   1 |  TEMP TABLE TRANSFORMATION                 |                               |       |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)  | SYS_TEMP_0FD9F544E_C777D925   |       |       |       |            |          |       |       |
|   3 |    INLIST ITERATOR                         |                               |       |       |       |            |          |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED    | E_BATCH_JOB                   |    17 |   918 |       |    24   (0)| 00:00:01 |       |       |
|   5 |      INDEX RANGE SCAN                      | E_BATCH_JOB_UK1               |    22 |       |       |     5   (0)| 00:00:01 |       |       |
|   6 |       FAST DUAL                            |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   7 |       FAST DUAL                            |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   8 |   LOAD AS SELECT (CURSOR DURATION MEMORY)  | SYS_TEMP_0FD9F544F_C777D925   |       |       |       |            |          |       |       |
|   9 |    UNION-ALL                               |                               |       |       |       |            |          |       |       |
|  10 |     VIEW                                   |                               |    17 |  1530 |       |     2   (0)| 00:00:01 |       |       |
|  11 |      TABLE ACCESS STORAGE FULL             | SYS_TEMP_0FD9F544E_C777D925   |    17 |   731 |       |     2   (0)| 00:00:01 |       |       |
|  12 |      FAST DUAL                             |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|  13 |     VIEW                                   |                               |    17 |  1530 |       |     2   (0)| 00:00:01 |       |       |
|  14 |      TABLE ACCESS STORAGE FULL             | SYS_TEMP_0FD9F544E_C777D925   |    17 |   731 |       |     2   (0)| 00:00:01 |       |       |
|  15 |      SORT AGGREGATE                        |                               |     1 |    61 |       |            |          |       |       |
|  16 |       VIEW                                 |                               |    17 |  1037 |       |     2   (0)| 00:00:01 |       |       |
|  17 |        TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FD9F544E_C777D925   |    17 |   731 |       |     2   (0)| 00:00:01 |       |       |
|  18 |   LOAD AS SELECT (CURSOR DURATION MEMORY)  | SYS_TEMP_0FD9F5450_C777D925   |       |       |       |            |          |       |       |
|  19 |    WINDOW SORT                             |                               |    34 |  5100 |       |  6667K (10)| 00:04:21 |       |       |
|  20 |     NESTED LOOPS OUTER                     |                               |    34 |  5100 |       |  6667K (10)| 00:04:21 |       |       |
|  21 |      HASH JOIN                             |                               |    34 |  4318 |       |  6667K (10)| 00:04:21 |       |       |
|  22 |       VIEW                                 |                               |    34 |  2958 |       |     2   (0)| 00:00:01 |       |       |
|  23 |        TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FD9F544F_C777D925   |    34 |  3060 |       |     2   (0)| 00:00:01 |       |       |
|  24 |       PARTITION RANGE ALL                  |                               |    24G|   910G|       |  6583K  (9)| 00:04:18 |     1 |1048575|
|  25 |        PARTITION HASH ALL                  |                               |    24G|   910G|       |  6583K  (9)| 00:04:18 |     1 |  LAST |
|  26 |         TABLE ACCESS STORAGE FULL          | FF_INC_PRICE                  |    24G|   910G|       |  6583K  (9)| 00:04:18 |     1 |1048575|
|  27 |      TABLE ACCESS BY GLOBAL INDEX ROWID    | F_SENSITIVITY                 |     1 |    23 |       |     4   (0)| 00:00:01 | ROWID | ROWID |
|  28 |       INDEX UNIQUE SCAN                    | F_SENSITIVITY_PK              |     1 |       |       |     3   (0)| 00:00:01 |       |       |
|  29 |   LOAD AS SELECT (CURSOR DURATION MEMORY)  | SYS_TEMP_0FD9F5451_C777D925   |       |       |       |            |          |       |       |
|  30 |    WINDOW SORT                             |                               |    17M|  4484M|  4806M|   813K  (2)| 00:00:32 |       |       |
|  31 |     HASH JOIN                              |                               |    17M|  4484M|       | 69636  (12)| 00:00:03 |       |       |
|  32 |      MERGE JOIN CARTESIAN                  |                               |     6 |  1488 |       |     4   (0)| 00:00:01 |       |       |
|  33 |       TABLE ACCESS STORAGE FULL            | RATING_TYPES                  |     1 |   124 |       |     2   (0)| 00:00:01 |       |       |
|  34 |       BUFFER SORT                          |                               |     6 |   744 |       |     2   (0)| 00:00:01 |       |       |
|  35 |        TABLE ACCESS STORAGE FULL           | RATING_AGENCY_TYPES           |     6 |   744 |       |     2   (0)| 00:00:01 |       |       |
|  36 |      TABLE ACCESS STORAGE FULL             | INSTR_RATINGS                 |   396M|  9445M|       | 68265  (10)| 00:00:03 |       |       |
|  37 |   HASH JOIN OUTER                          |                               |     1 |   630 |       | 23721   (5)| 00:00:01 |       |       |
|  38 |    JOIN FILTER CREATE                      | :BF0000                       |     1 |   473 |       | 15874   (5)| 00:00:01 |       |       |
|  39 |     NESTED LOOPS OUTER                     |                               |     1 |   473 |       | 15874   (5)| 00:00:01 |       |       |
|  40 |      HASH JOIN OUTER                       |                               |     1 |   445 |       | 15703   (5)| 00:00:01 |       |       |
|  41 |       HASH JOIN OUTER                      |                               |     1 |   367 |       | 15701   (5)| 00:00:01 |       |       |
|  42 |        JOIN FILTER CREATE                  | :BF0001                       |     1 |   210 |       |  7854   (5)| 00:00:01 |       |       |
|  43 |         HASH JOIN OUTER                    |                               |     1 |   210 |       |  7854   (5)| 00:00:01 |       |       |
|  44 |          JOIN FILTER CREATE                | :BF0002                       |     1 |    53 |       |     7  (15)| 00:00:01 |       |       |
|  45 |           NESTED LOOPS OUTER               |                               |     1 |    53 |       |     7  (15)| 00:00:01 |       |       |
|  46 |            NESTED LOOPS OUTER              |                               |     1 |    30 |       |     5  (20)| 00:00:01 |       |       |
|  47 |             VIEW                           |                               |     1 |    13 |       |     3  (34)| 00:00:01 |       |       |
|  48 |              HASH UNIQUE                   |                               |     1 |     7 |       |     3  (34)| 00:00:01 |       |       |
|  49 |               VIEW                         |                               |     1 |     7 |       |     2   (0)| 00:00:01 |       |       |
|  50 |                TABLE ACCESS STORAGE FULL   | SYS_TEMP_0FD9F5450_C777D925   |     1 |    72 |       |     2   (0)| 00:00:01 |       |       |
|  51 |             TABLE ACCESS BY INDEX ROWID    | INSTR_MASTER                  |     1 |    17 |       |     2   (0)| 00:00:01 |       |       |
|  52 |              INDEX UNIQUE SCAN             | INSTRT_MASTER_PK              |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  53 |            TABLE ACCESS BY INDEX ROWID     | F_INCOME                      |     1 |    23 |       |     2   (0)| 00:00:01 |       |       |
|  54 |             INDEX UNIQUE SCAN              | F_INCOME_PK                   |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  55 |          VIEW                              |                               |    17M|  2579M|       |  7788   (4)| 00:00:01 |       |       |
|  56 |           JOIN FILTER USE                  | :BF0002                       |    17M|  2381M|       |  7788   (4)| 00:00:01 |       |       |
|  57 |            TABLE ACCESS STORAGE FULL       | SYS_TEMP_0FD9F5451_C777D925   |    17M|  2381M|       |  7788   (4)| 00:00:01 |       |       |
|  58 |        VIEW                                |                               |    17M|  2579M|       |  7788   (4)| 00:00:01 |       |       |
|  59 |         JOIN FILTER USE                    | :BF0001                       |    17M|  2381M|       |  7788   (4)| 00:00:01 |       |       |
|  60 |          TABLE ACCESS STORAGE FULL         | SYS_TEMP_0FD9F5451_C777D925   |    17M|  2381M|       |  7788   (4)| 00:00:01 |       |       |
|  61 |       VIEW                                 |                               |     1 |    78 |       |     2   (0)| 00:00:01 |       |       |
|  62 |        TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FD9F5450_C777D925   |     1 |    72 |       |     2   (0)| 00:00:01 |       |       |
|  63 |      VIEW PUSHED PREDICATE                 |                               |     1 |    28 |       |   171   (0)| 00:00:01 |       |       |
|  64 |       NESTED LOOPS                         |                               |     1 |   156 |       |   171   (0)| 00:00:01 |       |       |
|  65 |        NESTED LOOPS                        |                               |    34 |   156 |       |   171   (0)| 00:00:01 |       |       |
|  66 |         NESTED LOOPS                       |                               |    34 |  4182 |       |    70   (0)| 00:00:01 |       |       |
|  67 |          VIEW                              |                               |    34 |  2958 |       |     2   (0)| 00:00:01 |       |       |
|  68 |           TABLE ACCESS STORAGE FULL        | SYS_TEMP_0FD9F544F_C777D925   |    34 |  3060 |       |     2   (0)| 00:00:01 |       |       |
|  69 |          PARTITION RANGE ITERATOR          |                               |     1 |    36 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  70 |           TABLE ACCESS BY LOCAL INDEX ROWID| F_LIQ_BASE                    |     1 |    36 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  71 |            INDEX UNIQUE SCAN               | F_LIQ_BASE_UNQ_CONST          |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  72 |         PARTITION RANGE ITERATOR           |                               |     1 |       |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  73 |          INDEX UNIQUE SCAN                 | F_LIQ_UNIVERSE_UNQ_CONST      |     1 |       |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|  74 |        TABLE ACCESS BY LOCAL INDEX ROWID   | F_LIQ_UNIVERSE                |     1 |    33 |       |     3   (0)| 00:00:01 |     1 |     1 |
|  75 |    VIEW                                    |                               |    17M|  2579M|       |  7788   (4)| 00:00:01 |       |       |
|  76 |     JOIN FILTER USE                        | :BF0000                       |    17M|  2381M|       |  7788   (4)| 00:00:01 |       |       |
|  77 |      TABLE ACCESS STORAGE FULL             | SYS_TEMP_0FD9F5451_C777D925   |    17M|  2381M|       |  7788   (4)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$7C70BD48
   2 - SEL$22
   4 - SEL$22       / E_BATCH_JOB@SEL$22
   5 - SEL$22       / E_BATCH_JOB@SEL$22
   6 - SEL$CBF63DBF / DUAL@SEL$21
   7 - SEL$11594485 / DUAL@SEL$21
   8 - SET$1
  10 - SEL$96EC8427 / ALL_BATCHES@SEL$25
  11 - SEL$96EC8427 / T1@SEL$96EC8427
  12 - SEL$CEF22074 / DUAL@SEL$21
  13 - SEL$96EC8426 / ALL_BATCHES@SEL$27
  14 - SEL$96EC8426 / T1@SEL$96EC8426
  15 - SEL$28
  16 - SEL$96EC8425 / ALL_BATCHES@SEL$28
  17 - SEL$96EC8425 / T1@SEL$96EC8425
  18 - SEL$45D930ED
  22 - SEL$298E7975 / B@SEL$19
  23 - SEL$298E7975 / T1@SEL$298E7975
  26 - SEL$45D930ED / P@SEL$19
  27 - SEL$45D930ED / S@SEL$20
  28 - SEL$45D930ED / S@SEL$20
  29 - SEL$B21719C5
  33 - SEL$B21719C5 / Y@SEL$16
  35 - SEL$B21719C5 / T@SEL$15
  36 - SEL$B21719C5 / R@SEL$15
  47 - SEL$30       / I@SEL$2
  48 - SEL$30
  49 - SEL$3E1B8096 / PRICES@SEL$30
  50 - SEL$3E1B8096 / T1@SEL$3E1B8096
  51 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
  52 - SEL$7C70BD48 / INSTR_MASTER@SEL$33
  53 - SEL$7C70BD48 / F_INCOME@SEL$32
  54 - SEL$7C70BD48 / F_INCOME@SEL$32
  55 - SEL$E4433E7A / R_SP@SEL$11
  57 - SEL$E4433E7A / T1@SEL$E4433E7A
  58 - SEL$E4433E79 / R_FITCH@SEL$9
  60 - SEL$E4433E79 / T1@SEL$E4433E79
  61 - SEL$3E1B8095 / P@SEL$1
  62 - SEL$3E1B8095 / T1@SEL$3E1B8095
  63 - SEL$F84B98B6 / L@SEL$13
  64 - SEL$F84B98B6
  67 - SEL$298E7974 / B@SEL$17
  68 - SEL$298E7974 / T1@SEL$298E7974
  70 - SEL$F84B98B6 / LB@SEL$17
  71 - SEL$F84B98B6 / LB@SEL$17
  73 - SEL$F84B98B6 / U@SEL$18
  74 - SEL$F84B98B6 / U@SEL$18
  75 - SEL$E4433E78 / R_MOODY@SEL$7
  77 - SEL$E4433E78 / T1@SEL$E4433E78

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$CBF63DBF")
      MERGE(@"SEL$BFEE6DA6" >"SEL$23")
      OUTLINE_LEAF(@"SEL$11594485")
      MERGE(@"SEL$BFEE6DA5" >"SEL$24")
      OUTLINE_LEAF(@"SEL$22")
      OUTLINE_LEAF(@"SEL$CEF22074")
      MERGE(@"SEL$21" >"SEL$26")
      OUTLINE_LEAF(@"SEL$96EC8427")
      OUTLINE_LEAF(@"SEL$25")
      OUTLINE_LEAF(@"SEL$96EC8425")
      MATERIALIZE(@"SEL$22")
      OUTLINE_LEAF(@"SEL$28")
      OUTLINE_LEAF(@"SEL$96EC8426")
      OUTLINE_LEAF(@"SEL$27")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$298E7975")
      OUTLINE_LEAF(@"SEL$45D930ED")
      MERGE(@"SEL$D2DE2538" >"SEL$7153C5F2")
      OUTLINE_LEAF(@"SEL$B21719C5")
      MERGE(@"SEL$803786B2" >"SEL$34")
      OUTLINE_LEAF(@"SEL$3E1B8096")
      OUTLINE_LEAF(@"SEL$30")
      OUTLINE_LEAF(@"SEL$3E1B8095")
      MATERIALIZE(@"SEL$29")
      OUTLINE_LEAF(@"SEL$E4433E78")
      MATERIALIZE(@"SEL$34")
      OUTLINE_LEAF(@"SEL$E4433E79")
      OUTLINE_LEAF(@"SEL$E4433E7A")
      OUTLINE_LEAF(@"SEL$298E7974")
      MATERIALIZE(@"SET$1")
      OUTLINE_LEAF(@"SEL$F84B98B6")
      PUSH_PRED(@"SEL$7C70BD48" "L"@"SEL$13" 1)
      OUTLINE_LEAF(@"SEL$7C70BD48")
      MERGE(@"SEL$32" >"SEL$28ED9374")
      MERGE(@"SEL$33" >"SEL$28ED9374")
      OUTLINE(@"SEL$23")
      OUTLINE(@"SEL$BFEE6DA6")
      OUTLINE(@"SEL$24")
      OUTLINE(@"SEL$BFEE6DA5")
      OUTLINE(@"SEL$26")
      OUTLINE(@"SEL$21")
      OUTLINE(@"SEL$7153C5F2")
      ANSI_REARCH(@"SEL$29")
      OUTLINE(@"SEL$D2DE2538")
      MERGE(@"SEL$19" >"SEL$2FEECD4C")
      OUTLINE(@"SEL$34")
      OUTLINE(@"SEL$803786B2")
      MERGE(@"SEL$15" >"SEL$16")
      OUTLINE(@"SEL$29")
      OUTLINE(@"SEL$ED95C77E")
      MERGE(@"SEL$D50F4D64" >"SEL$31")
      OUTLINE(@"SEL$28ED9374")
      MERGE(@"SEL$4E060E5B" >"SEL$35")
      OUTLINE(@"SEL$32")
      OUTLINE(@"SEL$33")
      OUTLINE(@"SEL$2FEECD4C")
      ANSI_REARCH(@"SEL$20")
      OUTLINE(@"SEL$19")
      OUTLINE(@"SEL$16")
      OUTLINE(@"SEL$15")
      OUTLINE(@"SEL$31")
      OUTLINE(@"SEL$D50F4D64")
      MERGE(@"SEL$17" >"SEL$18")
      OUTLINE(@"SEL$35")
      OUTLINE(@"SEL$4E060E5B")
      MERGE(@"SEL$13" >"SEL$14")
      MERGE(@"SEL$72AEFE3E" >"SEL$14")
      OUTLINE(@"SEL$20")
      OUTLINE(@"SEL$18")
      OUTLINE(@"SEL$17")
      OUTLINE(@"SEL$14")
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$72AEFE3E")
      MERGE(@"SEL$11" >"SEL$12")
      MERGE(@"SEL$B97648DD" >"SEL$12")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$11")
      OUTLINE(@"SEL$B97648DD")
      MERGE(@"SEL$096E5AED" >"SEL$10")
      MERGE(@"SEL$9" >"SEL$10")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$096E5AED")
      MERGE(@"SEL$15E987C1" >"SEL$8")
      MERGE(@"SEL$7" >"SEL$8")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$15E987C1")
      MERGE(@"SEL$5" >"SEL$6")
      MERGE(@"SEL$7237DA6D" >"SEL$6")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$7237DA6D")
      MERGE(@"SEL$3" >"SEL$4")
      MERGE(@"SEL$58A6D7F6" >"SEL$4")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$7C70BD48" "I"@"SEL$2")
      INDEX_RS_ASC(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33" ("INSTR_MASTER"."INSTRUMENT_ID"))
      INDEX_RS_ASC(@"SEL$7C70BD48" "F_INCOME"@"SEL$32" ("F_INCOME"."INSTRUMENT_ID"))
      NO_ACCESS(@"SEL$7C70BD48" "R_SP"@"SEL$11")
      NO_ACCESS(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
      NO_ACCESS(@"SEL$7C70BD48" "P"@"SEL$1")
      NO_ACCESS(@"SEL$7C70BD48" "L"@"SEL$13")
      NO_ACCESS(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
      LEADING(@"SEL$7C70BD48" "I"@"SEL$2" "INSTR_MASTER"@"SEL$33" "F_INCOME"@"SEL$32" "R_SP"@"SEL$11" "R_FITCH"@"SEL$9" "P"@"SEL$1"
              "L"@"SEL$13" "R_MOODY"@"SEL$7")
      USE_NL(@"SEL$7C70BD48" "INSTR_MASTER"@"SEL$33")
      USE_NL(@"SEL$7C70BD48" "F_INCOME"@"SEL$32")
      USE_HASH(@"SEL$7C70BD48" "R_SP"@"SEL$11")
      USE_HASH(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
      USE_HASH(@"SEL$7C70BD48" "P"@"SEL$1")
      USE_NL(@"SEL$7C70BD48" "L"@"SEL$13")
      USE_HASH(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
      PX_JOIN_FILTER(@"SEL$7C70BD48" "R_SP"@"SEL$11")
      PX_JOIN_FILTER(@"SEL$7C70BD48" "R_FITCH"@"SEL$9")
      PX_JOIN_FILTER(@"SEL$7C70BD48" "R_MOODY"@"SEL$7")
      FULL(@"SEL$E4433E7A" "T1"@"SEL$E4433E7A")
      FULL(@"SEL$E4433E79" "T1"@"SEL$E4433E79")
      FULL(@"SEL$E4433E78" "T1"@"SEL$E4433E78")
      FULL(@"SEL$3E1B8095" "T1"@"SEL$3E1B8095")
      NO_ACCESS(@"SEL$30" "PRICES"@"SEL$30")
      USE_HASH_AGGREGATION(@"SEL$30")
      NO_ACCESS(@"SEL$F84B98B6" "B"@"SEL$17")
      INDEX_RS_ASC(@"SEL$F84B98B6" "LB"@"SEL$17" ("F_LIQ_BASE"."SNAP_DATE" "F_LIQ_BASE"."INSTRUMENT_ID"
              "F_LIQ_BASE"."SNAP_ID"))
      INDEX(@"SEL$F84B98B6" "U"@"SEL$18" ("F_LIQ_UNIVERSE"."SNAP_DATE" "F_LIQ_UNIVERSE"."INSTRUMENT_ID"
              "F_LIQ_UNIVERSE"."SNAP_ID" "F_LIQ_UNIVERSE"."UNIVERSE_GROUP_ID"))
      LEADING(@"SEL$F84B98B6" "B"@"SEL$17" "LB"@"SEL$17" "U"@"SEL$18")
      USE_NL(@"SEL$F84B98B6" "LB"@"SEL$17")
      USE_NL(@"SEL$F84B98B6" "U"@"SEL$18")
      NLJ_BATCHING(@"SEL$F84B98B6" "U"@"SEL$18")
      FULL(@"SEL$298E7974" "T1"@"SEL$298E7974")
      FULL(@"SEL$3E1B8096" "T1"@"SEL$3E1B8096")
      FULL(@"SEL$B21719C5" "Y"@"SEL$16")
      FULL(@"SEL$B21719C5" "T"@"SEL$15")
      FULL(@"SEL$B21719C5" "R"@"SEL$15")
      LEADING(@"SEL$B21719C5" "Y"@"SEL$16" "T"@"SEL$15" "R"@"SEL$15")
      USE_MERGE_CARTESIAN(@"SEL$B21719C5" "T"@"SEL$15")
      USE_HASH(@"SEL$B21719C5" "R"@"SEL$15")
      NO_ACCESS(@"SEL$45D930ED" "B"@"SEL$19")
      FULL(@"SEL$45D930ED" "P"@"SEL$19")
      INDEX_RS_ASC(@"SEL$45D930ED" "S"@"SEL$20" ("F_SENSITIVITY"."SENSITIVITY_ID"))
      LEADING(@"SEL$45D930ED" "B"@"SEL$19" "P"@"SEL$19" "S"@"SEL$20")
      USE_HASH(@"SEL$45D930ED" "P"@"SEL$19")
      USE_NL(@"SEL$45D930ED" "S"@"SEL$20")
      INDEX_RS_ASC(@"SEL$22" "E_BATCH_JOB"@"SEL$22" ("E_BATCH_JOB"."BATCH_NAME" "E_BATCH_JOB"."BATCH_DATE"
              "E_BATCH_JOB"."BATCH_INSTANCE_NO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$22" "E_BATCH_JOB"@"SEL$22")
      PUSH_SUBQ(@"SEL$11594485")
      PUSH_SUBQ(@"SEL$CBF63DBF")
      NO_ACCESS(@"SEL$27" "ALL_BATCHES"@"SEL$27")
      PUSH_SUBQ(@"SEL$28")
      NO_ACCESS(@"SEL$25" "ALL_BATCHES"@"SEL$25")
      PUSH_SUBQ(@"SEL$CEF22074")
      FULL(@"SEL$96EC8427" "T1"@"SEL$96EC8427")
      FULL(@"SEL$96EC8426" "T1"@"SEL$96EC8426")
      NO_ACCESS(@"SEL$28" "ALL_BATCHES"@"SEL$28")
      FULL(@"SEL$96EC8425" "T1"@"SEL$96EC8425")
      FULL(@"SEL$298E7975" "T1"@"SEL$298E7975")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (DATE): 11/29/2021 00:00:00
   2 - :2 (NUMBER): 7

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  SEL$25
           -  materialize

   0 -  SEL$27
           -  materialize