Skip to Main Content
  • Questions
  • Online partitioning and purging old data

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Marko.

Asked: April 25, 2017 - 12:04 pm UTC

Last updated: May 03, 2017 - 1:04 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi Tom,
I am writing to you for the first time and I hope you could help me.I should consider resizing of some tables.I am talking about log tables(user event log, app event log, transaction log etc.).They are mostly standalone tables with no children or parents(some of them have just parents).Usually tables have just unique index for PK and some of them have index on timestamp column.Tables have become very large and old data should be dropped.

I am considering of using DBMS_REDEFINITION package to do online partitioning because it shouldn't be downtime(at least downtime mustn't last long) and than to drop old partitions.

I have several questions about it:

1.If everything goes fine i suppose i have to rebuild index when i drop old partition.Will it cause any impact on system except that that querying will be slow.Applications use that tables just to insert data(it is very frequent) and generally their purpose is just for debugging.We talk about 250GB tables and that is just on dev, production tables are far larger.Approximately how long does it take to rebuild index for tables of that size if i plan to keep 20% of data and what about if i plan to drop 20% of data.(In some tables data should be kept 7 years and in some 1 year)

2.Are there any drawbacks.Does copying of data from original to interim table generates large amount of redo logs or is there any other consequence.

3.Do you have any suggestion to do everything on other way.

I don't have much experience, i am junior in this job.I have already read about partitioning and i plan to do interval partitioning on timestamp columns.I also have done some testing with DBMS_REDEFINITION package and i only noticed that it doesn't copy default values for columns so i should do it at the end.Is there anything else i should be worried about.I appreciate for any help.

Best regards,
Marko

and Connor said...

See here for a complete example

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9526051800346812767

To answer your questions

1) the indexes will be taken care of, but *you* need to decide whether the indexes will be global or local (local preferred if you can, because then partition drop does not need any index maintenance)

2) there is obviously resource consumed during the exercise, but you can quanity that with testing

3) dbms_redef is the way to go.

Rating

  (1 rating)

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

Comments

any chance of upgrading to 12c ?

Rajeshwaran, May 02, 2017 - 9:40 am UTC

any chance of upgrading to 12c?

if yes, then you could use two new features ("online Conversion of a Non-Partitioned Table to a Partitioned Table" and "Filtering Maintenance Operations" ) introduced in 12.2 to get this done easily.

Sometime like this.

demo@ORA12C> create table t as
  2  select a.*, to_date('01-jan-2013','dd-mon-yyyy')
  3                     + mod(rownum,1825) created_dt
  4  from all_objects a;

Table created.

demo@ORA12C> select trunc(created_dt,'Y'),count(*)
  2  from t
  3  group by trunc(created_dt,'Y')
  4  order by trunc(created_dt,'Y');

TRUNC(CREAT   COUNT(*)
----------- ----------
01-JAN-2013      12409
01-JAN-2014      12241
01-JAN-2015      12045
01-JAN-2016      12078
01-JAN-2017      12012

demo@ORA12C> alter table t modify
  2  partition by range( created_dt )
  3  ( partition p_2017 values less than
  4             (to_date('01-jan-2018','dd-mon-yyyy')) ,
  5    partition p_2018 values less than
  6             (to_date('01-jan-2019','dd-mon-yyyy')) )
  7  including rows where created_dt >=
  8             to_date('01-jan-2017','dd-mon-yyyy')
  9  online;

Table altered.

demo@ORA12C> select trunc(created_dt,'Y'),count(*)
  2  from t
  3  group by trunc(created_dt,'Y')
  4  order by trunc(created_dt,'Y');

TRUNC(CREAT   COUNT(*)
----------- ----------
01-JAN-2017      12012

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "DEMO"."T"
   (    "OWNER" VARCHAR2(128) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(128),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(23),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER NOT NULL ENABLE,
        "EDITION_NAME" VARCHAR2(128),
        "SHARING" VARCHAR2(18),
        "EDITIONABLE" VARCHAR2(1),
        "ORACLE_MAINTAINED" VARCHAR2(1),
        "APPLICATION" VARCHAR2(1),
        "DEFAULT_COLLATION" VARCHAR2(100),
        "DUPLICATED" VARCHAR2(1),
        "SHARDED" VARCHAR2(1),
        "CREATED_APPID" NUMBER,
        "CREATED_VSNID" NUMBER,
        "MODIFIED_APPID" NUMBER,
        "MODIFIED_VSNID" NUMBER,
        "CREATED_DT" DATE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS  LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA"
  PARTITION BY RANGE ("CREATED_DT")
 (PARTITION "P_2017"  VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA" ,
 PARTITION "P_2018"  VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM
-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_DATA" )


demo@ORA12C>

Connor McDonald
May 03, 2017 - 1:04 am UTC

I recommend anyone and everyone to be on 12c... it's just so much easier to be a DBA on 12c.

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.