Composite Partitioned Table in oracle 10g

====================================================
Composite Partitioned Table - By Range And hash
====================================================

CREATE TABLE composite_rng_hash (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
amount_sold NUMBER(10,2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY HASH(cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE users,
SUBPARTITION sp2 TABLESPACE users,
SUBPARTITION sp3 TABLESPACE users,
SUBPARTITION sp4 TABLESPACE users) (
PARTITION sales_pre05
VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY')),
PARTITION sales_2007
VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY')),
PARTITION sales_2008
VALUES LESS THAN(TO_DATE('01/01/2009','DD/MM/YYYY')),
PARTITION sales_future
VALUES LESS THAN(MAXVALUE));

set linesize 121
col table_name format a20

SELECT table_name, partitioned, secondary
FROM user_tables;

desc user_tab_partitions


SELECT table_name, partition_name, composite, subpartition_count SPC, high_value
FROM user_tab_partitions;

desc user_tab_subpartitions


SELECT table_name, partition_name, subpartition_name, subpartition_position
FROM user_tab_subpartitions;

desc user_subpartition_templates


SELECT subpartition_name, tablespace_name, high_bound
FROM user_subpartition_templates;



GRANT select ON emp TO uwclass;
GRANT select on dept TO uwclass;

INSERT INTO composite_rng_hash
SELECT c.deptno, c.dname || ' ' || c.dname,
s.sal, s.hiredate
FROM emp s, dept c
WHERE s.deptno = c.deptno
AND rownum < 250001; commit; exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_PRE05'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2005'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2006'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2007'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2008'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_FUTURE'); SELECT table_name, partition_name, num_rows FROM user_tab_partitions; SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions; exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', GRANULARITY=>'ALL');

SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions;

set long 1000000
select dbms_metadata.get_ddl('TABLE', 'COMPOSITE_RNG_HASH');



====================================================
Composite Partitioned Table - By Range And List
====================================================

CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE users,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE users,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE users) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));

desc composite_rng_list

SELECT table_name, partition_name, composite, high_value
FROM user_tab_partitions
where table_name='COMPOSITE_RNG_LIST';


SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
where table_name='COMPOSITE_RNG_HASH';