按天对相关的Oracle表集进行分区'我们都有时间信息

Question

我有一组看起来类似的表：

Time_Table（相对较小）:

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

表2（大，每时间表行约30行）：

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

表3（非常大，每表2行大约10行，目前几百天后有14亿行）：

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

我的查询至少总是在timeId上加入，并且每个查询都被分解为几天（10天的读取将导致10个较小的查询）。每天都会向所有表写入新数据。我们需要从这些表中存储（和查询）多年的数据。

当时间信息仅通过JOIN已知时，我如何将这些表划分为日常块？我应该以不依赖于时间的方式来看待分区吗？这可以自动完成吗？还是必须是手动过程？

Oracle 11.2版

Answer 1

引用分区在这里可能会有所帮助。它允许由父表确定子表的分区方案。

架构

--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);

执行计划

Pstart和Pstop表明，即使分区谓词只设置在小的父表上，也可以正确地修剪大的子表。

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

警告

引用分区有一些怪癖。它不适用于11g中的间隔分区，因此您必须手动为父表定义每个分区。外键也不可能禁用，这可能需要修改一些脚本。和任何很少使用的功能一样，它也有一些bug。

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')
);