topic Re: create a sub-partition table in Operating System - HP-UX

create a sub-partition table

Edgar_8 — Thu, 16 Sep 2004 05:40:34 GMT

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!

Re: create a sub-partition table

Eric Antunes — Thu, 16 Sep 2004 06:40:12 GMT

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

Re: create a sub-partition table

Jean-Luc Oudart — Thu, 16 Sep 2004 06:43:29 GMT

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

Re: create a sub-partition table

Eric Antunes — Thu, 16 Sep 2004 06:53:24 GMT

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...?

Re: create a sub-partition table

Edgar_8 — Thu, 16 Sep 2004 08:45:34 GMT

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!

Re: create a sub-partition table

Jean-Luc Oudart — Thu, 16 Sep 2004 09:19:08 GMT

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 ?