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

CompanyId
Type_Of_Data
Emp_id
Destination
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

score 1 · Accepted Answer · answered Sep 11 '15 at 09:51

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.

answered Sep 11 '15 at 09:51

Wernfried Domscheit

54,457
9
76
110

Thanks for the Answer :Can you please tell me how to add a new partition and sub partition to this table ? – Stay Curious Sep 11 '15 at 10:44
Simply add a partition `ALTER TABLE MY_TABLE ADD PARTITION COMPANY_D_TO_G VALUES ('D','E','F','G');`. Subpartitions are created automatically as defined by template. – Wernfried Domscheit Sep 11 '15 at 10:49
:I means if another subpartition comes up like EMP_SPOUSE ,then I don't want it to go under default subpartition ,then how will I add that , I am asking because I need to do this dynamic – Stay Curious Sep 11 '15 at 11:38
`ALTER TABLE my_table SET SUBPARTITION TEMPLATE (SUBPARTITION EMP VALUES ('EMP_DATA'), SUBPARTITION EMP_ADDRESS VALUES ('EMP_ADDRESS_DATA'), SUBPARTITION SPOUSE VALUES ('EMP_SPOUSE')).` – Wernfried Domscheit Sep 11 '15 at 12:26

score 0 · Answer 2 · answered Oct 14 '15 at 08:58

You cannot directly partition an existing non-partitioned table. You will need to create an interim table/new table depending on the following methods to partition:

DBMS_REDEFINITION
1. Create a Partitioned Interim Table
2. Start the Redefinition Process
3. Create Constraints and Indexes (Dependencies)
4. Complete the Redefinition Process
EXCHANGE PARTITION
1. Create a Partitioned Destination Table
2. EXCHANGE PARTITION
3. SPLIT PARTITION (If required to split single large partition into smaller partitions)

Partition on Exisiting Table with Millions of Record

2 Answers2