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