This discussion is archived
6 Replies Latest reply: Sep 30, 2011 10:33 AM by 891335 RSS

Oracle Table partiton: hash partiton on range partition

891335 Newbie
Currently Being Moderated
I have a table abc(c_date date,
c_id varchr2(20),
c_name varchar2);
The table is already range partitoned on "c_date" column and I have lot of data in that table.
Now my task is to create the hash partions under range; hash partiton needs 16 partitons.

I dont want to create another table with range and hash partitons and migrate the data. The amount of data I have is 3 terabytes.

Thanks in Advance for the help.
  • 1. Re: Oracle Table partiton: hash partiton on range partition
    AdamMartin Pro
    Currently Being Moderated
    Why not create another table? You are going to have to rebuild the whole thing anyway. All the rows will need to move and all the indexes will need to be rebuilt. How else would you do it?
  • 2. Re: Oracle Table partiton: hash partiton on range partition
    891335 Newbie
    Currently Being Moderated
    I was looking for alter statement which can add hash for range. If I have to migrate that kind of data it takes couple of days. I cant ask for those many days of downtime.
  • 3. Re: Oracle Table partiton: hash partiton on range partition
    AliD Expert
    Currently Being Moderated
    Look for ALTER TABLE ... SET SUBPARTITION TEMPLATE and ALTER TABLE ... EXCHANGE SUBPARTITION options.
  • 4. Re: Oracle Table partiton: hash partiton on range partition
    AdamMartin Pro
    Currently Being Moderated
    If I have to migrate that kind of data it takes couple of days. I cant ask for those many days of downtime.
    You realize you would have to "migrate that kind of data" anyway, right? It's not magic. Even if you add subpartitions, don't you want to move the existing rows into them?
  • 5. Re: Oracle Table partiton: hash partiton on range partition
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user8351471 wrote:
    I have a table abc(c_date date,
    c_id varchr2(20),
    c_name varchar2);
    The table is already range partitoned on "c_date" column and I have lot of data in that table.
    Now my task is to create the hash partions under range; hash partiton needs 16 partitons.

    I dont want to create another table with range and hash partitons and migrate the data. The amount of data I have is 3 terabytes.
    I've written a short note to give you some idea. As AliD says, you need to look at the options for exchanging partitions and changing partition table defaults.

    http://jonathanlewis.wordpress.com/2011/09/30/table-rebuilds-2/

    Regards
    Jonathan Lewis
  • 6. Re: Oracle Table partiton: hash partiton on range partition
    891335 Newbie
    Currently Being Moderated
    Mr Lewis

    1)

    This is my current structure of the table which has the data....

    CREATE TABLE T_LOGS
    (
    C_id NUMBER(10) NOT NULL,
    C_DATE DATE NOT NULL,
    C_DATADEF NUMBER(10) NOT NULL,
    PACKETTYPEID NUMBER(3) DEFAULT 1 NOT NULL,
    LOGTIME DATE DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL

    )
    TABLESPACE T_LOGS_TBL_TS
    PARTITION BY RANGE (C_DATE)
    (
    PARTITION IDLOG_20110817 VALUES LESS THAN (TO_DATE('2011-08-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE IDREADINGLOGS_TBL_TS;



    2)
    Requirement is as follows.....

    CREATE TABLE T_LOGS(
    C_id NUMBER(10) NOT NULL,
    C_DATE DATE NOT NULL,
    C_DATADEF NUMBER(10) NOT NULL,
    PACKETTYPEID NUMBER(3) DEFAULT 1 NOT NULL,
    LOGTIME DATE DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL)

    TABLESPACE T_LOGS_TBL_TS
    PARTITION BY RANGE (C_DATE)
    SUBPARTITION BY HASH (C_id)
    SUBPARTITION TEMPLATE
    (SUBPARTITION IDREAD1 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD2 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD3 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD4 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD5 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD6 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD7 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD8 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD9 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD10 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD11 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD12 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD13 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD14 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD15 TABLESPACE IDREADINGLOGS_TBL_TS,
    SUBPARTITION IDREAD16 TABLESPACE IDREADINGLOGS_TBL_TS
    )
    (
         PARTITION IDLOG_BASE VALUES LESS THAN(TO_DATE('2011-08-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace IDREADINGLOGS_TBL_TS
    )
    ;


    I am trying the steps you sent me.

    Regards,
    RP

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points