| Partitioned to Composite Partitioned [message #162606] |
Sat, 11 March 2006 22:08  |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
Given a partitioned table with 100M data in it:
CREATE TABLE sample_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state_id varchar2(2))
PARTITION BY RANGE (txn_date)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY')),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY')),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY')),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
);
How do I create a subpartition by hash (state_id) with count 4?
[Updated on: Sun, 12 March 2006 06:25] Report message to a moderator |
|
|
|
| Re: Partitioned to Composite Partitioned [message #162679 is a reply to message #162606] |
Mon, 13 March 2006 00:48  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
What is your reason for sub-partitioning by STATE_ID? Is it just because each partition is too big? Instead of partitioning by Quarter, why not partition by Month?
Smaller date-based partitions are more likely to be beneficial to your SQL statements that select a significant number of rows:
eg.SELECT *
FROM sample_regional_sales
WHERE txn_date between TO_DATE('20050501','YYYYMMDD')
AND TO_DATE('20050531','YYYYMMDD')
If you're still convinced that you want to sub-partition, take a look at the doco.
To sub-partition an existing table, the easiest way is to get your CREATE TABLE statement the way you want it (with partitions. sub-partitions, etc) and then put "AS SELECT * FROM table_name" after it. Obviously you would have to rename the old table first.
_____________
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
