Partition on Exisiting Table with Millions of Record

Question

I need you suggestion on creating a partition on a table having millions of record.

table definitions

1. CompanyId
2. Type_Of_Data
3. Emp_id
4. Destination
5. Destination_id

Now here for a single company ,type of data and emp_id can be different

 COMPANY_ID   TYPE_OF_DATA EMP_ID

 A  EMP_DATA          A1   
 A  EMP_DATA          A2
 A  EMP_DATA          A3
 A  EMP_DATA          A4
 A  EMP_ADDRESS_DATA  A1   
 A  EMP_ADDRESS_DATA  A2
 A  EMP_ADDRESS_DATA  A3
 A  EMP_ADDRESS_DATA  A4
 B  EMP_DATA          B1
 B  EMP_DATA          B2
 B  EMP_DATA          B3
 B  EMP_DATA          B4
 B  EMP_ADDRESS_DATA  B1   
 B  EMP_ADDRESS_DATA  B2
 B  EMP_ADDRESS_DATA  B3
 B  EMP_ADDRESS_DATA  B4

My basic selecting will be on company_id and then type of data and emp_id

I was thinking of creating a List - Hash or List -List Partition.

Can anyone suggest something else and how to add partition to the existing table will be helpful

Answer 1

You cannot add any partition to an existing "non-partitionized" table. You have to create a new table and copy your data into it. Have a look at DBMS_REDEFINITION which helps you to do this without any downtime of your application.

A composite List-List partition would be this one:

CREATE TABLE MY_TABLE
(
COMPANY_ID VARCHAR2(100),
TYPE_OF_DATA VARCHAR2(100),
EMP_ID  VARCHAR2(10),
...
)
PARTITION BY LIST (COMPANY_ID)
    SUBPARTITION BY LIST (TYPE_OF_DATA) SUBPARTITION TEMPLATE 
        (
        SUBPARTITION EMP VALUES ('EMP_DATA'),
        SUBPARTITION EMP_ADDRESS VALUES ('EMP_ADDRESS_DATA'),
        SUBPARTITION MISCELLANEOUS VALUES (DEFAULT) -- if needed
        )
(
PARTITION COMPANY_A VALUES ('A'),
PARTITION COMPANY_B VALUES ('B'),
PARTITION COMPANY_C VALUES ('C'),
PARTITION COMPANY_OTHER VALUES (DEFAULT)
);

In case you can get additional companies in your table consider RANGE partition instead.