Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Chris.

Asked: March 18, 2020 - 5:29 am UTC

Answered by: Connor McDonald - Last updated: April 03, 2020 - 10:13 am UTC

Category: Database Development - Version: 12c

Viewed 1000+ times

You Asked

Hi there,

Firstly, Thank you to you all for an amazing service you provide here.

Now onto business, I am a complete novice at understanding the ins and out of Oracle, though I have been dabbling with it for years.

I am currently working on a data warehousing project with Cognos 'writing the SQL', we're struggling here to understand the best way to help improve some of the reports we're developing. To set the scene, due to licensing restrictions we're unable to use the nice functions such as partitioning, if we could then I would not be sat here writing to you for help. To help us we've shrunk the table to only have 5 years worth of data, it improves things slightly.

Our (5 year tables) fact and dim tables have roughly 20 million rows each, I don't quite understand why both dim and fact have the same number of rows, something to do with always needing to use from and to date to get the "as of date" of a record.

We run this at the end of the daily ETL process:-
dbms_stats.gather_schema_stats( ownname => 'USER', cascade => dbms_stats.auto_cascade, estimate_percent => dbms_stats.auto_sample_size, degree => dbms_stats.auto_degree ); 



I'm unsure of how to upload the explain plan.

The Below SQL is taking roughly 9 seconds to return is was taking over 30 sec prior to the above change.

SELECT
    COUNT(DISTINCT 
        CASE 
            WHEN 
                "FACT_RC_PAYMENTS0"."CERTIFIED_DATE" < ("FACT_RC_PAYMENTS0"."RECEIVED_DATE" + (INTERVAL '1' DAY * (21))) AND
                "DIM_RC_PAYMENT_TYPE0"."SERVICE_PROVIDER_PAYMENT" = 'Y'
                THEN
                    CASE 
                        WHEN 
                            "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" IS NULL OR
                            "FACT_RC_PAYMENTS0"."PAYMENT_NO" IS NULL
                            THEN
                                NULL
                        ELSE "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" || '-' || "FACT_RC_PAYMENTS0"."PAYMENT_NO"
                    END
        END) AS "C1", 
    COUNT(DISTINCT 
        CASE 
            WHEN 
                "DIM_RC_PAYMENT_TYPE0"."WCRC_PAY_TYPE" IN ( 
                    'DOCT', 
                    'HOSP', 
                    'MEDI', 
                    'REHB' )
                THEN
                    CASE 
                        WHEN 
                            "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" IS NULL OR
                            "FACT_RC_PAYMENTS0"."PAYMENT_NO" IS NULL
                            THEN
                                NULL
                        ELSE "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" || '-' || "FACT_RC_PAYMENTS0"."PAYMENT_NO"
                    END
        END) AS "C2", 
    COUNT(DISTINCT 
        CASE 
            WHEN 
                "FACT_RC_PAYMENTS0"."CERTIFIED_DATE" < ("FACT_RC_PAYMENTS0"."RECEIVED_DATE" + (INTERVAL '1' DAY * (21))) AND
                "DIM_RC_PAYMENT_TYPE0"."SERVICE_PROVIDER_PAYMENT" = 'Y'
                THEN
                    CASE 
                        WHEN 
                            "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" IS NULL OR
                            "FACT_RC_PAYMENTS0"."PAYMENT_NO" IS NULL
                            THEN
                                NULL
                        ELSE "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" || '-' || "FACT_RC_PAYMENTS0"."PAYMENT_NO"
                    END
        END) / NULLIF(COUNT(DISTINCT 
        CASE 
            WHEN 
                "DIM_RC_PAYMENT_TYPE0"."WCRC_PAY_TYPE" IN ( 
                    'DOCT', 
                    'HOSP', 
                    'MEDI', 
                    'REHB' )
                THEN
                    CASE 
                        WHEN 
                            "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" IS NULL OR
                            "FACT_RC_PAYMENTS0"."PAYMENT_NO" IS NULL
                            THEN
                                NULL
                        ELSE "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" || '-' || "FACT_RC_PAYMENTS0"."PAYMENT_NO"
                    END
        END), 0) AS "C3"
FROM
    "DTMP_RC"."DIM_CALENDAR" "Snapshot_Calendar"
        INNER JOIN "DTMP_RC"."FACT_RC_PAYMENTS_5YEARS" "FACT_RC_PAYMENTS0"
        ON 
            "Snapshot_Calendar"."CALENDAR_DATE" >= "FACT_RC_PAYMENTS0"."FROM_DATE" AND
            "Snapshot_Calendar"."CALENDAR_DATE" <= "FACT_RC_PAYMENTS0"."TO_DATE"
            INNER JOIN "DTMP_RC"."DIM_RC_PAYMENTS_5YEARS" "DIM_RC_PAYMENTS0"
            ON "FACT_RC_PAYMENTS0"."RC_PAYMENT_KEY" = "DIM_RC_PAYMENTS0"."RC_PAYMENT_KEY"
                INNER JOIN "DTMP_RC"."DIM_RC_PAYMENT_TYPE" "DIM_RC_PAYMENT_TYPE0"
                ON "FACT_RC_PAYMENTS0"."PAYMENT_TYPE_KEY" = "DIM_RC_PAYMENT_TYPE0"."RC_PAYMENT_TYPE_KEY"
                                   
WHERE 
    "FACT_RC_PAYMENTS0"."CERTIFIED_IN_5YEARS" = 'Y' AND
    "Snapshot_Calendar"."CALENDAR_DATE" = '30-JUN-2018' AND
    "FACT_RC_PAYMENTS0"."CERTIFIED_DATE" BETWEEN '01-JUL-2017' AND  '30-JUN-2018';




As requested...
PLAN_TABLE_OUTOUT 

SQL_ID  9h6v5v3ch5s6z, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */      COUNT(DISTINCT          CASE  
            WHEN                  "FACT_RC_PAYMENTS0"."CERTIFIED_DATE" 
< ("FACT_RC_PAYMENTS0"."RECEIVED_DATE" + (INTERVAL '1' DAY * (21))) AND 
                "DIM_RC_PAYMENT_TYPE0"."SERVICE_PROVIDER_PAYMENT" = 'Y' 
                THEN                     CASE                          
WHEN                              "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" IS 
NULL OR                             "FACT_RC_PAYMENTS0"."PAYMENT_NO" IS 
NULL                             THEN                                 
NULL                         ELSE "DIM_RC_PAYMENTS0"."RC_CLAIM_NO" || 
'-' || "FACT_RC_PAYMENTS0"."PAYMENT_NO"                     END         
END) AS "C1",      COUNT(DISTINCT          CASE              WHEN       
           "DIM_RC_PAYMENT_TYPE0"."WCRC_PAY_TYPE" IN (                  
    'DOCT',                      'HOSP',                      'MEDI',   
                   'REHB' )                 THEN                     CAS
 
Plan hash value: 4236631715
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                                             |      1 |        |      1 |00:00:09.63 |     149K|    131K|       |       |          |
|   1 |  SORT GROUP BY                          |                                             |      1 |      1 |      1 |00:00:09.63 |     149K|    131K|    12M|  1342K|   11M (0)|
|*  2 |   HASH JOIN                             |                                             |      1 |    299K|    299K|00:00:10.45 |     149K|    131K|  1063K|  1063K| 1423K (0)|
|   3 |    TABLE ACCESS FULL                    | DIM_RC_PAYMENT_TYPE                         |      1 |   2748 |   2748 |00:00:00.01 |      11 |      0 |       |       |          |
|*  4 |    HASH JOIN                            |                                             |      1 |    299K|    299K|00:00:10.35 |     149K|    131K|    24M|  3616K|   25M (0)|
|   5 |     NESTED LOOPS                        |                                             |      1 |    299K|    299K|00:00:01.14 |   17410 |      0 |       |       |          |
|*  6 |      INDEX UNIQUE SCAN                  | SYS_C00199193                               |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| FACT_RC_PAYMENTS_5YEARS                     |      1 |    299K|    299K|00:00:01.11 |   17408 |      0 |       |       |          |
|   8 |       BITMAP CONVERSION TO ROWIDS       |                                             |      1 |        |    868K|00:00:00.79 |     875 |      0 |       |       |          |
|   9 |        BITMAP AND                       |                                             |      1 |        |      8 |00:00:00.88 |     875 |      0 |       |       |          |
|* 10 |         BITMAP INDEX SINGLE VALUE       | FACT_RC_PAYMENTS_5YEARS_CERTIFIED_IN_5YEARS |      1 |        |     61 |00:00:00.01 |      32 |      0 |       |       |          |
|  11 |         BITMAP CONVERSION FROM ROWIDS   |                                             |      1 |        |      8 |00:00:00.85 |     843 |      0 |       |       |          |
|  12 |          SORT ORDER BY                  |                                             |      1 |        |    868K|00:00:00.90 |     843 |      0 |    24M|  1332K|   22M (0)|
|* 13 |           INDEX RANGE SCAN              | FACT_RC_PAYMENTS_5YRS_CERT_DATE             |      1 |        |    868K|00:00:00.15 |     843 |      0 |       |       |          |
|  14 |     TABLE ACCESS FULL                   | DIM_RC_PAYMENTS_5YEARS                      |      1 |     19M|     19M|00:00:05.69 |     131K|    131K|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("FACT_RC_PAYMENTS0"."PAYMENT_TYPE_KEY"="DIM_RC_PAYMENT_TYPE0"."RC_PAYMENT_TYPE_KEY")
   4 - access("FACT_RC_PAYMENTS0"."RC_PAYMENT_KEY"="DIM_RC_PAYMENTS0"."RC_PAYMENT_KEY")
   6 - access("Snapshot_Calendar"."CALENDAR_DATE"=TO_DATE(' 2018-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - filter(("FACT_RC_PAYMENTS0"."FROM_DATE"<=TO_DATE(' 2018-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "FACT_RC_PAYMENTS0"."TO_DATE">=TO_DATE(' 2018-06-30 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss') AND "Snapshot_Calendar"."CALENDAR_DATE">="FACT_RC_PAYMENTS0"."FROM_DATE" AND "Snapshot_Calendar"."CALENDAR_DATE"<="FACT_RC_PAYMENTS0"."TO_DATE"))
  10 - access("FACT_RC_PAYMENTS0"."CERTIFIED_IN_5YEARS"='Y')
  13 - access("FACT_RC_PAYMENTS0"."CERTIFIED_DATE">=TO_DATE(' 2017-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "FACT_RC_PAYMENTS0"."CERTIFIED_DATE"<=TO_DATE(' 2018-06-30 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 
Note
-----
   - statistics feedback used for this statement






and we said...

Unless you can derive additional predicates, I think you're not going to get any significant improvements here because as it stands, the scan of DIM_RC_PAYMENTS_5YEARS is going to take ~6 seconds.

Assuming you have an index on RC_PAYMENT_KEY, you could try hint that into the query just to see if scanning the index will be better, but the data above suggest you'd be doing 300k index probes...which is unlikely to be better.

You mentioned that the dim tables are of comparable size to your fact tables to allow "as of" style queries. If this is indeed the case, it strikes me as odd that you don't have some sort of equivalent date based predicate available to you on the dim's.

Having said that, without a partitioning license.... you're up against it. You might want to consider home-grown partitioning using partition views. They still work fine in all versions


and you rated our response

  (3 ratings)

Reviews

Partitioned Views

April 02, 2020 - 4:29 am UTC

Reviewer: Chris from Perth, Australia

Hi, Thanks for the feedback, I have been looking into partitioned views, but I cannot see from the explain plan that they are being utilised.

drop table t1_after;
drop table t1_before;

create table t1_before  (constraint fr_date_before check(fr_date >='01-JAN-1960' and fr_date <='31-DEC-1990' ) disable, fr_date date) PARALLEL;
create table t1_after   (constraint fr_date_after  check(fr_date >='01-JAN-1991' and fr_date <= '31-DEC-2020' ) disable, fr_date date) PARALLEL;


/*
select 
to_char(to_date('01-JAN-1960','DD-MM-YYYY'),'J') "01-JAN-1960", 
to_char(to_date('31-DEC-1990','DD-MM-YYYY'),'J') "31-DEC-1990", 
to_char(to_date('01-JAN-1991','DD-MM-YYYY'),'J') "01-JAN-1991", 
to_char(to_date('31-DEC-2020','DD-MM-YYYY'),'J') "31-DEC-2020"
from dual;
*/

 begin
 for i in 1..1000 loop
 insert into t1_before values(to_date(trunc(dbms_random.value(2436935,2448257)),'J'));
 end loop;
 end;
 /

 begin
 for i in 1..1000 loop
 insert into t1_after values(to_date(trunc(dbms_random.value(2448258,2459215)),'J'));
 end loop;
 end;
 /

alter table t1_after enable constraint fr_date_after;
alter table t1_before enable constraint fr_date_before;

create index t1_before_fr_date on dtmp_rc.t1_before (fr_date) PARALLEL ; 
create index t1_before_fr_after on dtmp_rc.t1_after (fr_date) PARALLEL ; 

begin
DBMS_STATS.GATHER_TABLE_STATS (ownname => '"DTMP_RC"',tabname => '"T1_before"',estimate_percent => 1); 
DBMS_STATS.GATHER_TABLE_STATS (ownname => '"DTMP_RC"',tabname => '"T1_after"',estimate_percent => 1); 
end;

create or replace view v1
as
select * from t1_before union all
select * from t1_after;


select 
/*+ gather_plan_statistics */ 
fr_date
from v1
where fr_date < '01-JAN-1989';


SQL_ID agjnwcv3x10f0, child number 0
-------------------------------------
select * from v1 where fr_date < '01-JAN-1989'

Plan hash value: 2738899253

-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 |
| 1 | PX COORDINATOR | | 0 | | 0 |00:00:00.01 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 947 | 0 |00:00:00.01 |
| 3 | VIEW | V1 | 1 | 947 | 0 |00:00:00.01 |
| 4 | UNION-ALL | | 1 | | 0 |00:00:00.01 |
| 5 | PX BLOCK ITERATOR | | 1 | 946 | 0 |00:00:00.01 |
|* 6 | TABLE ACCESS FULL | T1_BEFORE | 0 | 946 | 0 |00:00:00.01 |
|* 7 | FILTER | | 1 | | 0 |00:00:00.01 |
| 8 | PX BLOCK ITERATOR | | 0 | 1 | 0 |00:00:00.01 |
|* 9 | TABLE ACCESS FULL| T1_AFTER | 0 | 1 | 0 |00:00:00.01 |
-------------------------------------------------------------------------------------

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

6 - access(:Z>=:Z AND :Z<=:Z)
filter("FR_DATE"<TO_DATE(' 1989-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
7 - filter(NULL IS NOT NULL)
9 - access(:Z>=:Z AND :Z<=:Z)
filter("FR_DATE"<TO_DATE(' 1989-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 6 because of table property
Chris Saxon

Followup  

April 02, 2020 - 10:19 am UTC

When using parallel query, you need to get ALL stats rather than LAST to see the details for the parallel threads:

set serveroutput off
select /*+ gather_plan_statistics */ fr_date
from   v1
where fr_date < '01-JAN-1989';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS ALL'));

-------------------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation               | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |    
-------------------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT        |           |      1 |        |       |     2 (100)|          |        |      |            |    933 |    
|   1 |  PX COORDINATOR         |           |      1 |        |       |            |          |        |      |            |    933 |    
|   2 |   PX SEND QC (RANDOM)   | :TQ10000  |      0 |    937 |  7496 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |      0 |    
|   3 |    VIEW                 | V1        |      4 |    937 |  7496 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |    524 |    
|   4 |     UNION-ALL           |           |      4 |        |       |            |          |  Q1,00 | PCWP |            |    933 |    
|   5 |      PX BLOCK ITERATOR  |           |      4 |    936 |  7488 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |    933 |    
|*  6 |       TABLE ACCESS FULL | T1_BEFORE |      5 |    936 |  7488 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |    933 |    
|*  7 |      FILTER             |           |      4 |        |       |            |          |  Q1,00 | PCWC |            |      0 |    
|   8 |       PX BLOCK ITERATOR |           |      0 |      1 |     8 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |      0 |    
|*  9 |        TABLE ACCESS FULL| T1_AFTER  |      0 |      1 |     8 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |      0 |    
-------------------------------------------------------------------------------------------------------------------------------------  


Notice that starts & A-rows are zero for lines 8 & 9? That tells you this full scan never happened!

Also beware: with the ALL format, you're going to get the stats for every previous execution. Which may mislead you. To avoid this, change the query slightly (e.g. add/remove whitespace) to force a new SQL id and get "fresh" stats.

Partioned Views

April 02, 2020 - 11:57 pm UTC

Reviewer: Chris Adams from Perth Australia

This is the output you suggested, I still don't see any evidence the PARTITIONED VIEWS are being used. Am I missing something ?

Thanks again.

SQL_ID  dpfnqtk1hx5zd, child number 4
-------------------------------------
select parameter,value from nls_session_parameters   union all SELECT 
'DB_TIMEZONE' name, DBTIMEZONE  value FROM DUAL   union all SELECT 
'SESSION_TIMEZONE' name, SESSIONTIMEZONE value FROM DUAL  union all 
SELECT 'SESSION_TIMEZONE_OFFSET' name, TZ_OFFSET(SESSIONTIMEZONE) value 
from DUAL  union all SELECT parameter, value FROM 
nls_database_parameters WHERE parameter='NLS_CHARACTERSET'   union all 
SELECT parameter, value FROM nls_database_parameters WHERE parameter= 
'NLS_LANGUAGE'
 
Plan hash value: 1646181368
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |      2 |        |       |     9 (100)|          |     44 |
|   1 |  UNION-ALL        |                  |      2 |        |       |            |          |     44 |
|*  2 |   FIXED TABLE FULL| X$NLS_PARAMETERS |      2 |     17 |   527 |     0   (0)|          |     34 |
|   3 |   FAST DUAL       |                  |      2 |      1 |       |     3   (0)| 00:00:01 |      2 |
|   4 |   FAST DUAL       |                  |      2 |      1 |       |     3   (0)| 00:00:01 |      2 |
|   5 |   FAST DUAL       |                  |      2 |      1 |       |     3   (0)| 00:00:01 |      2 |
|*  6 |   FIXED TABLE FULL| X$PROPS          |      2 |      1 |    28 |     0   (0)|          |      2 |
|*  7 |   FIXED TABLE FULL| X$PROPS          |      2 |      1 |    28 |     0   (0)|          |      2 |
---------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1       
   2 - SEL$88122447 / X$NLS_PARAMETERS@SEL$4
   3 - SEL$5        / DUAL@SEL$5
   4 - SEL$6        / DUAL@SEL$6
   5 - SEL$7        / DUAL@SEL$7
   6 - SEL$CF5359D5 / X$PROPS@SEL$9
   7 - SEL$285A8194 / X$PROPS@SEL$11
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("PARAMETER"<>'NLS_CHARACTERSET' AND "PARAMETER"<>'NLS_NCHAR_CHARACTERSET' AND 
              "PARAMETER"<>'NLS_SPECIAL_CHARS' AND "INST_ID"=USERENV('INSTANCE')))
   6 - filter(("NAME"='NLS_CHARACTERSET' AND "NAME" LIKE 'NLS%'))
   7 - filter(("NAME"='NLS_LANGUAGE' AND "NAME" LIKE 'NLS%'))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - STRDEF[128], STRDEF[256]
   2 - "INST_ID"[NUMBER,22], "PARAMETER"[VARCHAR2,64], "VALUE"[VARCHAR2,64]
   6 - "NAME"[VARCHAR2,128], "VALUE$"[VARCHAR2,4000]
   7 - "NAME"[VARCHAR2,128], "VALUE$"[VARCHAR2,4000]

Connor McDonald

Followup  

April 03, 2020 - 2:38 am UTC

That is for a different query...you need to run

select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS ALL'));

as the very first thing immediately after you have run your partitioned query.

Partitioned view

April 03, 2020 - 3:21 am UTC

Reviewer: Chris Adams from Perth, Australia

Hi, Yes this is a different query, but a simplified set of tables, I'm still struggling to verify if I'm doing things in the correct order. I've read that there should be some indication that it's reading a partitioned view. But I cant see one. as requested the explain plan output.


SQL_ID gv9j375wf46gh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ fr_date from v1 where fr_date <
'01-JAN-1979'

Plan hash value: 1082623332

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | | | | 50 |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 50 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 614 | 4912 | 4 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |
| 3 | VIEW | V1 | 6 | 614 | 4912 | 4 (0)| 00:00:01 | Q1,00 | PCWP | | 619 |
| 4 | UNION-ALL | | 6 | | | | | Q1,00 | PCWP | | 619 |
|* 5 | FILTER | | 6 | | | | | Q1,00 | PCWC | | 619 |
| 6 | PX BLOCK ITERATOR | | 6 | 613 | 4904 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | 619 |
|* 7 | TABLE ACCESS FULL| T1_BEFORE | 3 | 613 | 4904 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | 619 |
|* 8 | FILTER | | 6 | | | | | Q1,00 | PCWC | | 0 |
| 9 | PX BLOCK ITERATOR | | 0 | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | 0 |
|* 10 | TABLE ACCESS FULL| T1_AFTER | 0 | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | 0 |
-------------------------------------------------------------------------------------------------------------------------------------

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

1 - SEL$1
3 - SET$1 / V1@SEL$1
4 - SET$1
5 - SEL$2
7 - SEL$2 / T1_BEFORE@SEL$2
8 - SEL$3
10 - SEL$3 / T1_AFTER@SEL$3

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

5 - filter((TO_DATE('31-DEC-1990')>=TO_DATE('01-JAN-1960') AND TO_DATE('01-JAN-1960')<TO_DATE('01-JAN-1979')))
7 - access(:Z>=:Z AND :Z<=:Z)
filter("FR_DATE"<'01-JAN-1979')
8 - filter((TO_DATE('31-DEC-2020')>=TO_DATE('01-JAN-1991') AND TO_DATE('01-JAN-1991')<TO_DATE('01-JAN-1979')))
10 - access(:Z>=:Z AND :Z<=:Z)
filter("FR_DATE"<'01-JAN-1979')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "FR_DATE"[DATE,7]
2 - (#keys=0) "FR_DATE"[DATE,7]
3 - "FR_DATE"[DATE,7]
4 - STRDEF[7]
5 - "FR_DATE"[DATE,7]
6 - "FR_DATE"[DATE,7]
7 - "FR_DATE"[DATE,7]
8 - "FR_DATE"[DATE,7]
9 - "FR_DATE"[DATE,7]
10 - "FR_DATE"[DATE,7]

Note
-----
- Degree of Parallelism is 6 because of table property

Chris Saxon

Followup  

April 03, 2020 - 10:13 am UTC

I've read that there should be some indication that it's reading a partitioned view

AFAIK the only indicator is that some sections of the plan have zero starts. Which happens on lines 9 & 10 of your plan.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.