Home » SQL & PL/SQL » SQL & PL/SQL » Composite Partitioning (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Composite Partitioning [message #666110] Mon, 16 October 2017 03:19 Go to next message
Revathi.orafaq
Messages: 21
Registered: September 2017
Junior Member
Hi All,

As per our business requirement we need to create the Composite Partitioning on one table .
Main Partitioning is LIST on branch column
and sub Partitioning is INTERVAL(DAY) on entry date column

I am able to create the LIST-RANGE(with out any interval) successfully .
But how to create the LIST -INTERVAL ?


Example Code :
DROP TABLE LIST_RANGE;
CREATE TABLE LIST_RANGE
  (
    BRANCH     VARCHAR2(5),
    ENTRY_DATE DATE
  )
  PARTITION BY LIST
  (
    BRANCH
  )
  SUBPARTITION BY RANGE
  (
    ENTRY_DATE 
  ) 
  SUBPARTITION TEMPLATE
  (
    SUBPARTITION DT_15SEP2017 VALUES LESS THAN (TO_DATE(' 2017-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
    SUBPARTITION DT_15OCT2017 VALUES LESS THAN (TO_DATE(' 2017-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  )
  
  (
    PARTITION ABC VALUES
    (
      'ABC'
    )
  );

If it is simple INTERVAL Partitioning its working fine.
CREATE TABLE LIST_INTERVAL
  (
    BRANCH     VARCHAR2(5),
    ENTRY_DATE DATE
  )
  PARTITION BY RANGE
  (
    ENTRY_DATE
  )
  INTERVAL
  (
    NUMTODSINTERVAL(1, 'DAY')
  )
  (
    PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2019-06-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  );
DROP TABLE LIST_INTERVAL;
CREATE TABLE LIST_INTERVAL
  (
    BRANCH     VARCHAR2(5),
    ENTRY_DATE DATE
  )
  PARTITION BY LIST
  (
    BRANCH
  )
  SUBPARTITION BY RANGE
  (
    ENTRY_DATE 
  )
 INTERVAL
  (
    NUMTODSINTERVAL(1, 'DAY')
  )
  SUBPARTITION TEMPLATE
  (
    SUBPARTITION DT_15SEP2017 VALUES LESS THAN (TO_DATE(' 2017-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
    SUBPARTITION DT_15OCT2017 VALUES LESS THAN (TO_DATE(' 2017-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  )
  
  (
    PARTITION ABC VALUES
    (
      'ABC'
    )
  );


Can we use the INTERVAL in composite partitioning ?
Either in main partitioning or Sub partitioning can we use the INTERVAL ?

Please help me

Thanks
Revathi.T

Re: Composite Partitioning [message #666115 is a reply to message #666110] Mon, 16 October 2017 06:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oracle supports interval partitioning. Interval sub-partitioning isn't supported.

SY.
Re: Composite Partitioning [message #666119 is a reply to message #666115] Mon, 16 October 2017 07:02 Go to previous message
Revathi.orafaq
Messages: 21
Registered: September 2017
Junior Member

Thanks Solomon for your valuable information .

Thanks
Revathi.T
Previous Topic: Combination of more/less and brackets cause TRUE or FALSE (merged 2)
Next Topic: How to extract out huge result set in xml format as .xml file
Goto Forum:
  


Current Time: Thu Mar 28 09:57:10 CDT 2024