1 Replies - 623 Views - Last Post: 25 November 2015 - 06:13 PM

#1 amture106  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 159
  • Joined: 08-September 11

Composite Partition for Oracle SQL Table

Posted 25 November 2015 - 12:34 AM

This is my first time that I'm working with composite partitions. I'm trying to arrange by list and range. My code is as follows:
CREATE TABLE CHANGE_LOG_DRIVER_COMP 
(
  LICENSENUMBER VARCHAR2(100 BYTE) 
, NAME VARCHAR2(100 BYTE) 
, TYPE VARCHAR2(100 BYTE) 
, EXPIRATIONDATE DATE 
, LASTUPDATEDDATE date 
,YEAR_MONTH_WID integer
,YEAR_WID integer
,MONTH_WID integer
, LASTUPDATEDTIME VARCHAR2(5 BYTE) 
, ACTIVE_FLAG VARCHAR2(1 BYTE) 
, ROW_WID NUMBER(*, 0) 
)

PARTITION BY LIST(YEAR_WID) SUBPARTITION BY RANGE (MONTH_WID)
(
    PARTITION YR2013 VALUES LESS THAN (2014)
    (
    SUBPARTITION Q1_2013 VALUES (201301,201302,201303),
    SUBPARTITION Q2_2013 VALUES (201304,201305,201306),
    SUBPARTITION Q3_2013 VALUES (201307,201308,201309),
    SUBPARTITION Q4_2013 VALUES (201310,201311,201312),
    ),
    PARTITION YR2014 VALUES LESS THAN (2015)
    (
    SUBPARTITION Q1_2014 VALUES (201401,201402,201403),
    SUBPARTITION Q2_2014 VALUES (201404,201405,201406),
    SUBPARTITION Q3_2014 VALUES (201407,201408,201409),
    SUBPARTITION Q4_2014 VALUES (201410,201411,201412),
    ),
    PARTITION YR2015 VALUES LESS THAN (2016)
    (
    SUBPARTITION Q1_2015 VALUES (201501,201502,201503),
    SUBPARTITION Q2_2015 VALUES (201504,201505,201506),
    SUBPARTITION Q3_2015 VALUES (201507,201508,201509),
    SUBPARTITION Q4_2015 VALUES (201510,201511,201512)
    )
);



The logic and syntax should work fine, but it doesn't even run on my PC, I would appreciate some help please.

Is This A Good Question/Topic? 0
  • +

Replies To: Composite Partition for Oracle SQL Table

#2 wannabe21  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 24-May 15

Re: Composite Partition for Oracle SQL Table

Posted 25 November 2015 - 06:13 PM

As a disclaimer, I have done next to nothing with partitions myself. But here goes...

  • I think you cannot use "LESS THAN" in a LIST partition, maybe your partition should be RANGE?
  • Your RANGE subpartition is using fixed values, maybe your subpartition should be LIST?
  • Also, your last SUBPARTITION line in each partition should not have a trailing comma.
  • A final observation, at the risk of not knowing the business logic of your table, your subpartition is by MONTH_WID, yet your quarter VALUES look more like they'd be YEAR_MONTH_WID values.

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1