CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-partitioningdata-partitioning
Stay Curious
Stay Curious

Reputation: 101

Partition on Exisiting Table with Millions of Record

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

Upvotes: 0

Views: 115

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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)

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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.

Upvotes: 1

Related Questions