Partitioning a related set of Oracle tables by day when they don't all have Time information

Question

I have a set of tables that look similar to this:

Time_Table (relatively small):

Time      (TIMESTAMP)
timeId    (NUMBER)
Data...   (NUMBER)

Table2 (large, about 30 rows per time_table row):

timeId    (NUMBER)
table2Id  (NUMBER)
Data...   (NUMBER)

Table3 (very large, around 10 rows per table2 row, currently 1.4 billion rows after a couple of hundred days):

timeId    (NUMBER)
table2Id  (NUMBER)
table3Id  (NUMBER)
Data...   (NUMBER)

My queries ALWAYS join on timeId at the very least, and each query is broken up into days (10 day read will result in 10 smaller queries). New data is written to all tables every day. We need to store (and query) years of data from these tables.

How do I partition these tables into daily chunks when the Time information is only known through a JOIN? Should I be looking at partitioning in ways not reliant on Time? Can this be done automatically, or does it have to be a manual process?

Oracle version 11.2

Answer 1

Reference partitioning may help here. It allows a child table's partitioning scheme to be determined by the parent table.

Schema

--drop table table3;
--drop table table2;
--drop table time_table;

drop table time_table;
create table Time_Table
(
    time   TIMESTAMP,
    timeId NUMBER,
    Data01 NUMBER,
    constraint time_table_pk primary key (timeId)
)
partition by range (time)
(
    partition p1 values less than (date '2000-01-02'),
    partition p2 values less than (date '2000-01-03'),
    partition p3 values less than (date '2000-01-04')
);

create table table2
(
    timeId   number,
    table2Id number,
    Data01   number,
    constraint table2_pk primary key (table2ID),
    constraint table2_fk foreign key (timeId) references time_table(timeId)
);


create table table3
(
    timeId   number not null,
    table2Id number,
    table3Id number,
    Data01   number,
    constraint table3_pk primary key (table3ID),
    constraint table3_fk1 foreign key (timeId) references time_table(timeId),
    constraint table3_fk2 foreign key (table2ID) references table2(table2ID)
) partition by reference (table3_fk1);

Execution Plans

The Pstart and Pstop show that the huge child table is correctly pruned even though the partition predicate is only set on the small parent table.

explain plan for
select *
from table3
join time_table using (timeId)
where time = date '2000-01-02';

select * from table(dbms_xplan.display);

Plan hash value: 832465087

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |    91 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|            |     1 |    91 |     3   (0)| 00:00:01 |     2 |     2 |
|   2 |   NESTED LOOPS         |            |     1 |    91 |     3   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL   | TIME_TABLE |     1 |    39 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |    TABLE ACCESS FULL   | TABLE3     |     1 |    52 |     1   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------

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

   3 - filter("TIME_TABLE"."TIME"=TIMESTAMP' 2000-01-02 00:00:00')
   4 - filter("TABLE3"."TIMEID"="TIME_TABLE"."TIMEID")

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: skipped because of IO calibrate statistics are missing

Warnings

Reference partitioning has a few quirks. It doesn't work with interval partitioning in 11g, so you have to manually define every partition for the parent table. The foreign keys are also impossible to disable which may require modifying some scripts. And like any rarely used feature it has a few bugs.

Answer 2

drop table time_table;
create table Time_Table
(
    time   TIMESTAMP,
--    timeId NUMBER, Why you need ID when you have timestamp?????
    Data01 NUMBER,
    constraint time_table_pk primary key (time) -- not timeID!!!
)
partition by range (time)
(
    partition p1 values less than (date '2000-01-02'),
    partition p2 values less than (date '2000-01-03'),
    partition p3 values less than (date '2000-01-04')
);

create table table2
(
    time     timestamp not null,
    table2ID number,
    Data01   number
)
partition by range (time)
(
    partition p1 values less than (date '2000-01-02'),
    partition p2 values less than (date '2000-01-03'),
    partition p3 values less than (date '2000-01-04')
);


create table table3
(
    time     timestamp not null,
    table2Id number,
    table3Id number,
    Data01   number
) 
partition by range (time)
(
    partition p1 values less than (date '2000-01-02'),
    partition p2 values less than (date '2000-01-03'),
    partition p3 values less than (date '2000-01-04')
);