|
Oracle 11g: New Features - Extended Composite Partitioning
I will assume that you know about partitioning in general. If not, you
can read the following link:
Partitioning
On Oracle 11g, there have been lots of enhancements such as reference
partition, interval partitioning, partitioning virtual columns, and
extended composite partitioning. I will talk about Extended Composite
Partitioning.
Extended
Composite
Partitioning
You use extended composite partitioning, when you have multiple columns
that are good candidates for partitioning keys.
Assuming that you have a company-sales table that contains two columns
that are candidates for partitioning such as st_code and prod_code, and
your users query on the table filtering on both columns equally.
You can decide to LIST partition the table on product_code, as that
column has more discrete values and then subpartition on st_code, again
as list. This code sample shows how to do that:
CREATE TABLE company-sales
(
ID NUMBER,
PROD_CODE NUMBER,
ST_CODE VARCHAR2(2)
)
PARTITION BY LIST (PROD_CODE)
SUBPARTITION BY LIST (ST_CODE)
(
PARTITION P101 VALUES (101)
(
SUBPARTITION P101_CT VALUES ('CT'),
SUBPARTITION P101_NY VALUES ('NY'),
SUBPARTITION P101_DEF VALUES (DEFAULT)
),
PARTITION P201 VALUES (201)
(
SUBPARTITION P201_CT VALUES ('CT'),
SUBPARTITION P201_NY VALUES ('NY'),
SUBPARTITION P201_DEF VALUES (DEFAULT)
)
)
Good Luck!
|