create a sub-partition table

[Edgar_8]

Hi All,

Does anyone know how to create a partitioned subpartitioned table. For example with have daily tables which
is partitioned by a date column per hour. What would the sql be to create additional subpartions within the
hourly partitions?

Thanks in advance!

[Eric Antunes]

Hi Edgar,

I don't think you should do this because you'll get to many partitions: more than 8000 per year...

And I don't see any command to do that.

Eric

[Jean-Luc Oudart]

Edgar,

You can partition a table (with sub-partition) by rang /hash or range / list.
For the later, I'm not to sure it works with older oracle versions (what is your Oracle version ?)
Form the documentation (10G),we have this example :
CREATE TABLE quarterly_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE ts4
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
)
);



Therefore you can have a range partition paer date and subpartition, list each hour.
What about that ?

Regards,
Jean-Luc

[Eric Antunes]

I see that it is possible to create a partition of another partition on 10g. But on Oracle 8 it is not. I don't know if it is on 9i...?

[Edgar_8]

Hi Jean-Luc,

Thanks for the info, we running Oracle 9.2.0.4. Please advise as to what the syntax would be for subpartitions in date/time format.

Thanks in advance!

[Jean-Luc Oudart]

Edgar,

Regarding the following link this is already available with oracle9i release2.
http://www.oracle.com/oramag/oracle/02-jan/o12part.html

I don't have the syntax ready yet, I believe you can write it from the example.

Saying that, it seems you weant to use same field for partition & sub-partition.
Have a go with the To_char functionm and let us know ?


Regards,
Jean-Luc

PS : if you use the same field for partition and sub-partition , why would you require sub-partitions ?