Thanks for the question, KN.

Asked: December 23, 2002 - 12:56 pm UTC

Last updated: June 23, 2016 - 6:47 am UTC

Version: 8.1.7.4

Viewed 1000+ times

You Asked

Hi Tom,

I need to build a table that will hold read-only data for up to 2 months. The table will have a load (via a perl script run half hourly) of 3 million new rows a day. Queries will be using the date col in the table for data elimination, hence partitioning is the way to go.

Using monthly our daily partitions seem impractical, partitioning data by week seems sensible, as I can easily drop a partition older than 9 weeks old (to ensure I hold 2 months data).

My question to you is what do you suggest as the best way to maintain this table. Should I create 9 partitions and have some code to cycle through these and truncate the oldest partition to make room for new data. Or is it better to have partitions stamped with creation date and have the maintenance code drop/ create partitions based on that timestamp.

Virtually all my DBA experience has been with OLTP databases (where speed is the essence), now have discovered the brave new world of datawarehouses, which has thrown up some interesting problems

Any examples will be greatly appreciated

Many Thanks

and Tom said...

I would have a procedure run weekly/whatever to add a new "high end" partition for new data. I would drop the oldest partition. It is called a sliding window. See

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:928294572145 <code>

for an example. there I used a partition "the_rest", that has the nice attribute that if the job that is to add the new partition doesn't run for whatever reason, we still collect the data - I'll just have to split that partition.

Perl would probably be among the total dog slowest methods I could imagine to bulk load the database. SQLLDR would be my tool of choice in 8i -- external tables + SQL would be my choice in 9i. Perl -- would not be on the radar screen even.

Rating

(9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Maintaining Partitioned Tables

A reader, December 23, 2002 - 8:25 pm UTC

Great. Has given me the confidence I need to work with Partitioned tables

OK

Kumar, April 09, 2007 - 3:50 am UTC

Hi Tom,
we split some table partitions and renamed it to our
requirement.(SYS_Pnn renamed to some string).
when renaming table partitions we were not able to use
"Update global indexes" clause.
Indexes are still existing with partition name as SYS_Pnn for each table partition it is referring.
when queried,user_tab_partitions shows the renamed table partitions but user_ind_partitions shows still the partition name as SYS_Pnn for each partitioned table.

How to rename the index's table partition as that of table partition it is referring?

Is there any feature which supports automatic index
maintenance when we do a split of partitions?

Please do reply.

April 09, 2007 - 10:48 am UTC

the indexes are "automatically maintained" - the data therein is. Your use of update global indexes is not appropriate since - well - you are not modifying any data here at all - you just renamed stuff.

and if these are global indexes (as your attempt at using update global would indicate) there is no relationship between the index partitions and the table partitions logically.

global indexes do not point to a single partition, but rather to potentially ALL partitions.

so, not really sure what you mean.

OK

Kumar, April 10, 2007 - 9:06 am UTC

Sorry Tom.
They are infact local indexes i.e. Each index partition
refers to each table partition based on partitioning key.
we renamed the table partitions and not the local index
partitions.How to achieve this since index maintenance is
not there for Local indexes?

April 10, 2007 - 11:09 am UTC

If you've renamed the partition, the local indexes take care of themselves...

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(x) local;

Index created.

ops$tkyte%ORA10GR2> select index_name, partition_name from user_ind_partitions union all select table_name, partition_name from user_tab_partitions order by 1, 2;

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T                              SYS_P155
T                              SYS_P156
T                              SYS_P157
T_IDX                          SYS_P155
T_IDX                          SYS_P156
T_IDX                          SYS_P157

6 rows selected.

ops$tkyte%ORA10GR2> column pname new_val p
ops$tkyte%ORA10GR2> select max(partition_name) pname from user_tab_partitions;

PNAME
------------------------------
SYS_P157

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t rename partition &p to last;
old   1: alter table t rename partition &p to last
new   1: alter table t rename partition SYS_P157 to last

Table altered.

ops$tkyte%ORA10GR2> alter index t_idx rename partition &p to last;
old   1: alter index t_idx rename partition &p to last
new   1: alter index t_idx rename partition SYS_P157 to last

Index altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t split partition last
  2  at (to_date('15-mar-2003','dd-mon-yyyy'))
  3  into (partition next_to_last, partition last)
  4  /

Table altered.

ops$tkyte%ORA10GR2> select index_name, partition_name from user_ind_partitions union all select table_name, partition_name from user_tab_partitions order by 1, 2;

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T                              LAST
T                              NEXT_TO_LAST
T                              SYS_P155
T                              SYS_P156
T_IDX                          LAST
T_IDX                          NEXT_TO_LAST
T_IDX                          SYS_P155
T_IDX                          SYS_P156

8 rows selected.

Partitions and sliding windows

A reader, February 20, 2013 - 5:32 am UTC

We're thinking of implementing a sliding window solution with our present table structures as follows;

create table parent_tbl(id number primary key, descr varchar2(30), part_date date);

create table child_tbl1 (id number primary key, parent_id references parent_tbl, descr varchar2(30));  

create table child_tbl2 (id number primary key, parent_id references parent_tbl, descr varchar2(30));  

create table child_tbl3,4,5,etc

We're looking to range partition PARENT_TBL by PART_DATE, and as we're on 11R2, we thought we could use REFERENCE partition on the all CHILD_TBL's.

Using the sliding window method, we aim to age out old partitions for parent_tbl and all child_tbl's with just one partition command on the parent_tbl only.

The problem we have is rather than being able to purge all records for year 2012 say, we have some exception rules defined where we need to keep some year 2012 records on the table, so how can we implement this whilst still being able to use the sliding window method which efficiently purges data for us?

February 25, 2013 - 8:30 am UTC

... The problem we have is rather than being able to purge all records for year 2012 say, we have some exception rules defined where we need to keep some year 2012 records on the table, so how can we implement this whilst still being able to use the sliding window method which efficiently purges data for us? ....

typically you would include an attribute that dictates whether a row is to be purged or not. this attribute would be used in the partitioning scheme so that you would range partition by date, list partition by "attribute to purge by"

Partitions and sliding windows

A reader, February 25, 2013 - 3:57 am UTC

Actually, we've decided against REFERENCE partitioning, it's too restrictive for our needs - we want to use INTERVAL partitioning and also it can only handle PARENT -> CHILD relationships, not GRANDPARENT -> PARENT -> CHILD

However, we still have to come up with a solution to handle the EXCEPTION rules whereby certain GRANDPARENT rows say have to be kept online and therefore we can't simply purge using the EXCHANGE PARTITION method

February 25, 2013 - 11:35 am UTC

interval cannot handle parent/child, reference does that.

I don't know what you mean.

Partitions and sliding windows

A reader, February 26, 2013 - 2:36 am UTC

I meant that REFERENCE only handles PARENT/CHILD, it cannot handle GRANDPARENT/PARENT/CHILD.

You can't range partition GRANDPARENT by date, then partition PARENT by REFERENCE and then partition CHILD also by REFERENCE.

So we're going duplicate the DATE column to the PARENT and CHILD so we can partition all 3 with INTERVAL partitioning.

February 26, 2013 - 8:06 am UTC

reference partitioning can certainly handle hierarchies of any level.

p = grandparent of c2
c1 = child of p
c2 = grandchild of p, but also child of c1

If a child inherits the partitioning scheme of the parent, then any child of the child will inherit the same.

ops$tkyte%ORA11GR2> create table p
  2  (
  3    order#      number primary key,
  4    order_date  date,
  5    data       varchar2(30)
  6  )
  7  enable row movement
  8  PARTITION BY RANGE (order_date)
  9  (
 10    PARTITION part_2007 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part_2008 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.


ops$tkyte%ORA11GR2> create table c1
  2  ( order#   number not null,   
  3    line#    number,            
  4    data     varchar2(30),
  5    constraint c1_pk primary key(order#,line#),
  6    constraint c1_fk_p foreign key(order#) references p
  7  )
  8  enable row movement
  9  partition by reference(c1_fk_p)
 10  /

Table created.

ops$tkyte%ORA11GR2> create table c2
  2  ( order#   number not null,
  3    line#    number not null,
  4    subline# number,
  5    data     varchar2(30),
  6    constraint c2_pk primary key(order#,line#,subline#),
  7    constraint c2_fk_c1 foreign key(order#,line#) references c1
  8  )
  9  enable row movement
 10  partition by reference(c2_fk_c1)
 11  /

Table created.



ops$tkyte%ORA11GR2> select table_name, partition_name
  2    from user_tab_partitions
  3   where table_name in ( 'P', 'C1', 'C2' )
  4   order by table_name, partition_name
  5  /

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
C1                             PART_2007
C1                             PART_2008
C2                             PART_2007
C2                             PART_2008
P                              PART_2007
P                              PART_2008

6 rows selected.

Partitions and sliding windows

A reader, February 27, 2013 - 2:31 am UTC

Thank you Tom - I misread the documentation and you've demonstrated it's possible to create a hierarchy with partition by REFERENCE.

Is partition sliding window possible in this case

A reader, June 19, 2016 - 7:17 am UTC

I can't work out how to implement a sliding window load into the CHILD table in this scenario;

create table parent (some_date date,
                     some_val  varchar2(10),
                     f1 number primary key
                    )
  PARTITION BY range (some_date)
  SUBPARTITION BY HASH (some_val) 
  SUBPARTITION template (
    subpartition sp1, 
    subpartition sp2)
  (PARTITION P_201505 VALUES less than (to_date('20150601','yyyymmdd')),
   PARTITION P_201506 VALUES less than (to_date('20150701','yyyymmdd'))
  );

create table child (id number primary key, 
                    f1 number not null,
  CONSTRAINT fk_parent FOREIGN KEY (f1) REFERENCES parent (f1)
)
partition by reference (fk_parent);

insert /*+ append */ into parent 
select to_date('20150630','yyyymmdd'), level, level
from dual
connect by level <10;
commit;

Looking here to see how I could build a "temp" child table to load in data for 20150630 -

https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm#i1107555

I came up with this (which doesn't work);

create table child_ex (id number primary key, 
                    f1 number not null,
  CONSTRAINT fk_parent2 FOREIGN KEY (f1) REFERENCES parent (f1)
)
partition by reference (fk_parent2);

insert /*+ append */ into child_ex
select level,level
from dual
connect by level <10;

alter table child
 exchange partition for (to_date('20150630','yyyymmdd'))
 with table child_ex
 including indexes 
 without validation
 update global indexes;

ORA-14093: data type of expression incompatible with that of partitioning column

Now at this point, I'm thinking I should be doing something similar to this;

create table child_ex (id number primary key, 
                    f1 number not null,
                    some_val varchar2(10),
  CONSTRAINT fk_parent2 FOREIGN KEY (f1) REFERENCES parent (f1)
)
partition by hash (some_val);

But obviously the child_ex table no longer matches child and therefore I can't do an exchange.

My question is with this kind of reference partitioning, am I prevented from loading child tables using the sliding window method?

June 20, 2016 - 1:18 am UTC

Here's an example of what you might be after...The key thing to notice here is that reference partitioning is NOT *copy* the parent partitioning scheme, it is to ensure that there is a 1-to-1 mapping between child and parent partitions. So you'll see below, the child table has *no* subpartitions...just partitions.

SQL> create table parent (some_date date,
  2                       some_val  varchar2(10),
  3                       f1 number primary key
  4                      )
  5    PARTITION BY range (some_date)
  6    SUBPARTITION BY HASH (some_val)
  7    SUBPARTITION template (
  8      subpartition sp1,
  9      subpartition sp2)
 10    (PARTITION P_201505 VALUES less than (to_date('20150601','yyyymmdd')),
 11     PARTITION P_201506 VALUES less than (to_date('20150701','yyyymmdd'))
 12    );

Table created.

SQL>
SQL> create table child (id number primary key,
  2                      f1 number not null,
  3    CONSTRAINT fk_parent FOREIGN KEY (f1) REFERENCES parent (f1)
  4  )
  5  partition by reference (fk_parent);

Table created.

SQL>
SQL> insert  into parent
  2  select to_date('20150630','yyyymmdd'), level, level
  3  from dual
  4  connect by level <10;

9 rows created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> insert into child
  2  select level,level
  3  from dual
  4  connect by level <10;

9 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> col partition_name format a30
SQL> col subpartition_name format a30
SQL>
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'PARENT';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P_201505
P_201506

2 rows selected.

SQL> select subpartition_name, num_rows from user_tab_subpartitions where table_name = 'PARENT';

SUBPARTITION_NAME                NUM_ROWS
------------------------------ ----------
P_201505_SP1
P_201505_SP2
P_201506_SP1
P_201506_SP2

4 rows selected.

SQL>
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'CHILD';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P_201505_SP1
P_201505_SP2
P_201506_SP1
P_201506_SP2

4 rows selected.

SQL> select subpartition_name, num_rows from user_tab_subpartitions where table_name = 'CHILD';

no rows selected

SQL>
SQL> drop table child_ex purge;

Table dropped.

SQL>
SQL> create table child_ex (id number primary key,
  2                      f1 number not null,
  3    CONSTRAINT fk_parent2 FOREIGN KEY (f1) REFERENCES parent (f1)
  4  );

Table created.

SQL>
SQL>
SQL> insert into child_ex values (2,2);

1 row created.

SQL> insert into child_ex values (4,4);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from child partition ( P_201506_SP1 ) ;

        ID         F1
---------- ----------
         2          2
         4          4
         9          9

3 rows selected.

SQL> select * from child_ex;

        ID         F1
---------- ----------
         2          2
         4          4

2 rows selected.

SQL>
SQL> alter table child
  2   exchange partition P_201506_SP1
  3   with table child_ex
  4   excluding indexes
  5   without validation
  6   update global indexes;

Table altered.

SQL>
SQL> select * from child partition ( P_201506_SP1 ) ;

        ID         F1
---------- ----------
         2          2
         4          4

2 rows selected.

SQL> select * from child_ex;

        ID         F1
---------- ----------
         2          2
         4          4
         9          9

3 rows selected.

SQL>
SQL> select table_name, index_name, status from user_indexes
  2  where table_name like 'CHILD%';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
CHILD                          SYS_C0020658                   VALID
CHILD_EX                       SYS_C0020661                   UNUSABLE

2 rows selected.

SQL>
SQL>

Hope this helps.

Is partition sliding window possible in this case

A reader, June 20, 2016 - 8:35 am UTC

Thank you for the follow up.

I can see that the CHILD has no subpartitions and how you have exchanged the CHILD_EX table with a CHILD partition.

If I wanted to load 1 month's worth of data, I would have to do that as 2 separate loads (2 being the number of hash values we have in this test case, but we actually have 64 in production so I would have to load 64 times).

Additionally, I would have to manually split the data up 2 (or 64) times so that the data is distributed evenly across all the partitions.

Am I correct ? If so, then it's a real shame that I can't simply load 1 months worth data into CHILD_EX (partitioned by hash 2 or 64), then exchange CHILD_EX with partition P_yyyymmdd of CHILD.

Whereas, I can load 1 months worth of data into PARENT_EX (partitioned by hash 2 or 64) and exchange it with partition P_yyyymmdd of PARENT.

It would seem a useful feature missing.

June 23, 2016 - 6:47 am UTC

I'll admit - I had the (mis)conception that the partiting "style" of the parent (ie partitions + subpartitions) would be reflected in the child table. Your question taught me something new there :-)

And that's why your statement:

"then exchange CHILD_EX with partition P_yyyymmdd of CHILD."

wont be possible, because there actually is *not* a partition (logical or physical) in the child called P_yyyymmdd. The child is in effect the sum of the subpartitions.

You didnt mention what version you are on - there are certainly more options at your disposal when it comes to 12c.

http://docs.oracle.com/database/121/VLDBG/GUID-C7A9BAD4-E4C9-4765-88C5-51AC7E97BAF1.htm#GUID-AA98AE67-AFD2-42BA-B3E6-888957AAAD56

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Maintaining Partitioned Tables

OK

OK

Partitions and sliding windows

Partitions and sliding windows

Partitions and sliding windows

Partitions and sliding windows

Is partition sliding window possible in this case

Is partition sliding window possible in this case

More to Explore

VLDB

VLDB