1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Partitioning a Table in Oracle

Discussion in 'Other Development Tools' started by Arju, Oct 25, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Partitioning a Table in Oracle

    Below is the procedure of partitioning a table in oracle.

    1) Identify the table creation script that you want to do partitioning. There are many tools to do it or you can use DBMS_METADATA package's GET_DDL procedure to do the task.

    2) Identify the index creation script of the table that you want to do partitioning. There are many tools to do it or you can use DBMS_METADATA package's GET_DDL procedure to do the task.

    3) Identify the constraints on the table that you want to do partitioning. There are many tools to do it or you can use DBMS_METADATA package's GET_DDL procedure to get the script of constraints.

    4) Create the partitioning table. An example of partitioning table version of login table is given below.
    Code (Text):

    CREATE TABLE login
    (
      ID        NUMBER(32),
      cdate     DATE                                NOT NULL,
      mac       VARCHAR2(20 BYTE)                   NOT NULL,
      remarks   VARCHAR2(250 BYTE),
      nas_name  VARCHAR2(100 BYTE),
      username  VARCHAR2(200 BYTE)
    )
    TABLESPACE isp_spc

    PARTITION BY RANGE (CDATE)
    (  
      PARTITION P102008 VALUES LESS THAN (TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        LOGGING
        TABLESPACE ISP_SPC
    ,  
      PARTITION P112008 VALUES LESS THAN (TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        LOGGING
        TABLESPACE ISP_SPC
    ,  
      PARTITION P122008 VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        LOGGING
        TABLESPACE ISP_SPC
    ,  
      PARTITION P012009 VALUES LESS THAN (TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        LOGGING
        TABLESPACE ISP_SPC
    ,  
      PARTITION P022009 VALUES LESS THAN (TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        LOGGING
        TABLESPACE ISP_SPC
    ,  
      PARTITION P032009 VALUES LESS THAN (TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    )
    5) Insert data into the partitioning table: You can wish any method to do it. Like export import , or normal insert operation, or for faster insert use /*+APPEND NOLOGGING */ hint.

    6) After data loaded into partitioned table rename the original table and make the partitioned table to desire one.

    7) Build index and constraints of the partitioned table and consider whether partition the index.

    8) Gather statistics on the partitioned table.
     
  2. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Range Partition
    Code (Text):
    SQL> create table test_R
      2  (a number,
      3  b number)
      4  partition by range(b)
      5  (partition p1 values less than (10),
      6  partition p2 values less than (maxvalue));

    Table created.
    Hash Partition
    Code (Text):
    SQL> create table test_H
      2  (a number,
      3  b number)
      4  partition by hash(b)
      5  (partition p01, partition p02, partition p03, partition p04);

    Table created.
    List Partition
    Code (Text):
    SQL> create table test_L
      2  (a number,
      3  b number)
      4  partition by list(b)
      5  (partition p1 values(1),
      6  partition p2 values(2),
      7  partition p3 values(DEFAULT));

    Table created.
     
  3. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    1)Using DBMS_METADATA.GET_DDL take the original table script.

    2)Verify that the source table is able to undergo an ONLINE redefinition using the dbms_redefinition.can_redef_table procedure.

    3)Create an empty interim table reflecting the final structure. If you want keep index, constraints same as in source table then don't add or modify constraints.

    4)Start the redefinition of the source table using the dbms_redefinition.start_redef_table procedure.

    5)Copies the dependent objects of the original table onto the interim table by the COPY_TABLE_DEPENDENTS Procedure.

    6)Finish the redefinition of the source table using the dbms_redefinition.finish_redef_table procedure.

    7)Drop the interim table.

    It is recommended to periodically synchronize the interim table with the source one when a large amount of DML is executed on the table while the
    re-organization is taking place by calling the dbms_redefinition.sync_interim_table() procedure.
     
  4. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
  5. mirza

    mirza Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    80
    Thanks a lot dear for sharing