Convert Non-Partitioned to Partitioned Table

This article is written in oracle10g R2. In any database environment, if we need to covert any huge table to partition table, then first we need to think about two factor.

1. Partition key
2. What type of partition we need to use.

Once we decided the above key factor, then there are couple of ways, we can convert the table to partition table. We have two option to convert the regular table to partition table.
Option 1
This option requires table down time and it might fit if business allows the application down time. Here are the steps to follow.

1. Stop using the table, make sure no one is chaning the data in table.
2. Create the partition table with the same structure of regular table.
3. Copy the data from regular table to partition table.
4. Create the constriant, keys, indexes on partition table.
5. Drop the original table
6. Analyze the partition table.

Option 2

This is ideal option to convert the regular table to partition table. It does not require down time and every one can use the table during the conversion. This option is introduced in oracle9i and enhanced to 10g. We are using online redefinition(DBMS_REDEFINITION) to convert the table to partition table.

Some restriction of using DBMS_REDEFINITION.

1· Cannot belong to SYS or SYSTEM Schema.
2. The table to be redefined online should not be using User-defined data types
3· Should not be a clustered table.
4. Should not have MV Logs or MVs defined
5. You cannot move a table from one schema to another using Online Redefinition feature.

Here are the steps to convert to partition table.

Step 1
Check to make sure that the table can use the online redefintion feature

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP');
PL/SQL procedure successfully completed.

Step 2
Create the temporary partition table as same structure of original table.

SQL> Create table EMP_part
2 (EMPNO NUMBER(4),
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2))
10 partition by range (SAL)
11 (Partition p1 values less than (1000),
12 Partition p2 values less than (2000),
13 Partition p3 values less than (3000),
14 Partition p4 values less than (4000),
15 Partition max values less than (maxvalue))
16 tablespace users;

Table created.

Step 3
Start the online redefinition process.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 4
Here is where oracle10g feature come into play. We do not need to copy any dependent objects to part_emp table. Dependent objects are like grants, synonym, triggers etc.

SQL> VARIABLE NUM_ERRORS NUMBER;
SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','EMP','EMP_PART', 1,TRUE,TRUE,TRUE,FALSE,
:NUM_ERRORS,FALSE);

PL/SQL procedure successfully completed.

SQL> PRINT NUM_ERRORS

NUM_ERRORS
----------
0

Step 5
Resync the table

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.
Step 6

Complete the online redefinition

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 7

Make sure emp table has all partitions as we created in emp_part table
SQL> Select partition_name, high_value from user_tab_partitions where table_name='EMP';

PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------
MAX MAXVALUE
P1 1000
P2 2000
P3 3000
P4 4000

SQL> select partition_name, high_value from user_tab_partitions where table_name='EMP_PART';

no rows selected

Step 8
Make sure all the dependent objects are copied .

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS
2 WHERE TABLE_NAME='EMP';

TRIGGER_NAME
------------------------------
EMPTRIG

SQL> select constraint_name from user_constraints
2 where table_name='EMP';

CONSTRAINT_NAME
------------------------------
PK_EMP
FK_DEPTNO

SQL>

Note : The only problem i see here is, if we have any index on the original table, it will convert to global index on partition table. If we need the index to be local index, then we have to drop and recreate the index.

When to Use Index Organized table?

What is Index Organized table?

In Heap tables, oracle stores the data in data segment with ROWID. When we create the index, oracle stores the index key value and ROWID in the index segment. So the index key value and rowid are stored in both index & data segments. Whenever we search any data record, it first scan the index segment and find the rowid and match the rowid in data segment to get the particular record. So the drawback here is, rowid & index key value is stored in two places. The space consumption is one issue. Another issue is, oracle has to scan both data and index segment to obtain the record.

To overcome this problem, oracle introduced IOT in version 8. In IOT, the data is stored in the primary key index itself with the rows placed in a key-sequenced order, thus eliminating the necessity of duplicate data being stored in index and table. When we search the record, it does scan only on index segment.

IOT do not consider ROWID. IOT stores the data in B-tree index and sorts the data on primary key whenever we insert, update the record.


Example to create IOT

SQL> CREATE TABLE iot_emp
2 (empno number PRIMARY KEY,
3 empname varchar2(10),
4 salary number)
5 ORGANIZATION INDEX
6 TABLESPACE RPTS_STAGE_DATA
7 PCTTHRESHOLD 20
8 INCLUDING empname
9 OVERFLOW TABLESPACE ods_stage_data
10 /

Table created.

SQL>

ORGANIZATION INDEX denotes that the table is index organized table.

PCTTHRESHOLD Specifies the percentage of space reserved in the index block for an index-organized table row. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50. PCTTHRESHOLD must be large enough to hold the primary key.

OVERFLOW - Index-organized table data rows exceeding the specified threshold will be placed in overflow segment.

INCLUDING - which can be used to specify nonkey columns that are to be stored in the overflow data segment.

When to use IOT?

Large tables where primary key exact fetch is time critical and there is a low volume of DML operations. In most cases, in the category of exact fetch via primary key, IOT organization is much faster than HEAP. This advantage varies between 15% and 400%, depending upon the row length, number of columns and the use of the PCTTHRESHOLD option.

Heavy volume of DML operations occurring in IOT, usually fragments the table, requiring frequent table reorganization. An index-organized table can be reorganized without invalidating its secondary indexes, and can be performed online thus reducing or even eliminating downtime.

Index-organized tables support parallel features for loading, index creation, and scans required for handling large volumes of data. Partitioned index-organized tables are also supported, so that each partition can be loaded concurrently. Data warehousing applications using star schemas can also gain performance and scalability by implementing fact tables as index-organized tables for efficient execution of star queries. All these features make index-organized tables suitable for handling large scale data.

Advantage of IOT?

1. Scanning both data and index segment is eliminated.
2. Storage requirement is reduced as data is stored only in primary key index segment.