Wednesday, March 8, 2017

How to partition a table using a virtual column and online redefinition


Consider the following table:
desc DOCUMENT_SUMMARY
Navn                                      Null?    Type
----------------------------------------- -------- ----------------------------
DOCUMENTNAME                              NOT NULL VARCHAR2(100)
SUMMARY                                            VARCHAR2(32)
A SELECT * FROM DOCUMENT_SUMMARY reveals contents of the following type (excerpt only):

DOCUMENT1:2015-08:9200060665151:9000010707472:9000044000014 9b37f5bcee4ce643058ee633359b3253
DOCUMENT2:2016-01:9200060665151:8837773777371:9000088000009 17bf7170217960f303755bdd4c648676

The table is quite big, and expected to grow.
The customer would like to get it partitioned.

The column DOCUMENTNAME already contains a string that is partly generated from a date, as revealed by the query below:

SELECT SUBSTR(DOCUMENTNAME,32,7) "datepart" 
FROM DOCUMENT_SUMMARY
FETCH FIRST 2 ROWS ONLY;

datepart
2015-01
2015-01


We could most likely use a substring of the value to create a new, virtual column and use it as a partition key.

So let's do that:
CREATE TABLE DOCUMENT_SUMMARY_INTERIM
(
  DOCUMENTNAME  VARCHAR2(100 BYTE),
  SUMMARY       VARCHAR2(32 BYTE),
  PERIOD AS (
     CAST(
        TO_DATE(SUBSTR(documentname,32,7),'YYYY-MM') 
        AS DATE)
  ) VIRTUAL
)
PARTITION BY RANGE(PERIOD)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH') )
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2013-01','YYYY-MM') )
)
TABLESPACE USERS
;

Run through a normal online redefinition, and the tables should be switched seamlessly.

Selecting from the new table shows that the date is being generated as a virtual column for every row:

SELECT * 
FROM DOCUMENT_SUMMARY
FETCH FIRST 2 ROWS ONLY;

DOKUMENTNAME SUMMARY PERIOD
DOCUMENT1:2015-08:9200060665151:9000010707472:9000044000014 f1081c88eb379b736954641589d5715b 01.08.2015
DOCUMENT2:2016-01:9200060665151:8837773777371:9000088000009 051722d803b2fbaca4192a047b7689a6 01.01.2016

You can verify your partition key attributes with this SQL:

SELECT K.COLUMN_NAME, C.DATA_TYPE,C.VIRTUAL_COLUMN ,PT.INTERVAL
FROM DBA_PART_KEY_COLUMNS K JOIN DBA_TAB_COLS C
    ON (K.COLUMN_NAME = C.COLUMN_NAME) 
        JOIN DBA_PART_TABLES PT ON PT.TABLE_NAME = C.TABLE_NAME
WHERE  K.NAME='DOCUMENT_SUMMARY' ;

COLUMN_NAME DATA_TYPE VIRTUAL_COLUMN INTERVAL
PERIODE DATE YES NUMTOYMINTERVAL(1,'MONTH')

No comments:

Post a Comment