Reference:
11g new feature: Extended Composite Partitioning (Overview, Example and Use) (Doc ID 943567.1)
Examples For Creating Partitioned table With Different Partitioning Strategies (Doc ID 733311.1)
11g Partitioning Enhancements (Doc ID 452447.1)
To get more detailed information and SQL syntax related with composite partitioning tables, refer to the documentation that is provided in the beginning of the chapter and go to “Creating Composite Partitioned Tables” section. Check the following documentation to get more detailed information about the composite partitioning:
Oracle® Database VLDB and Partitioning Guide -> Creating Partitions
Sometimes, defining only one partition is not enough. By creating a composite partition, the table is firstly partitioned by the first data distribution method, and then each partition is subpartitioned by the second data distribution method.
In this and next post, we will talk about the following composite partitioned tables:
- Range-hash partitioning
- Range-list partitioning
- Range-range partitioning
- List-range partitioning
- List-hash partitioning
- List-list partitioning
- Hash-hash partitioning
- Interval-list partitioning
- Interval-range partitioning
- Interval-hash partitioning
Note: Only Range-Hash and Range-List composite partitions can be created from OEM.
Range-Hash partitioning
To create a range-hash partitioned table, use PARTITION BY RANGE SUBPARTITION BY HASH syntax as follows:
SQL> CREATE TABLE tbl_range_hash_part PARTITION BY RANGE (object_id) SUBPARTITION BY HASH (namespace) SUBPARTITIONS 4 ( PARTITION part1 VALUES LESS THAN (1000), PARTITION part2 VALUES LESS THAN (10000), PARTITION part3 VALUES LESS THAN (30000), PARTITION part4 VALUES LESS THAN (maxvalue)) AS SELECT * FROM dba_objects; Table created. SQL>
Gather the statistics of the table to update the statistics at DBA_TAB_PARTITIONS:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘sys’,’tbl_range_hash_part’); PL/SQL procedure successfully completed.
To get the list of range partitions and the data distribution, query DBA_TAB_PARTITIONS view as follows:
SQL> SELECT table_name, partition_name, subpartition_count, num_rows, blocks FROM dba_tab_partitions WHERE table_name = ‘TBL_RANGE_HASH_PART’; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS BLOCKS ------------------------- ---------------------- ---------------- ---------- --------- TBL_RANGE_HASH_PART PART1 4 942 12 TBL_RANGE_HASH_PART PART2 4 8763 113 TBL_RANGE_HASH_PART PART3 4 19802 287 TBL_RANGE_HASH_PART PART4 4 45740 666
As you see, each partition has 4 has subpartitions. To get the list of all subpartitions of this table, query DBA_TAB_SUBPARTITIONS view as follows:
SQL> SELECT table_owner, table_name, partition_name, subpartition_name FROM dba_tab_subpartitions WHERE table_name = ‘TBL_RANGE_HASH_PART’; TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ---------------- -------------------- -------------- -------------- SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP61 SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP62 SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP63 SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP64 SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP65 SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP66 SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP67 SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP68 SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP69 SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP70 SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP71 SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP72 SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP73 SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP74 SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP75 SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP76 16 rows selected. SQL>
Range-list partitioning
In some cases, you may have a huge range partitioned table that can also be subpartitioned with a list partition. Use PARTITION BY RANGE SUBPARTITION BY LIST syntax to create a range-list partition as follows:
SQL> CREATE TABLE tbl_range_list_part PARTITION BY RANGE (object_id) SUBPARTITION BY LIST (object_type) (PARTITION part1 VALUES LESS THAN (100) ( SUBPARTITION part1_sub_index VALUES (‘INDEX’), SUBPARTITION part1_sub_table VALUES (‘TABLE’)), PARTITION part2 VALUES LESS THAN (200) ( SUBPARTITION part2_sub_index VALUES (‘INDEX’), SUBPARTITION part2_sub_table VALUES (‘TABLE’)), PARTITION part3 VALUES LESS THAN (maxvalue)) AS SELECT * FROM dba_objects where object_type in (‘INDEX’,’TABLE’); Table created. SQL>
You can get count of rows in any subpartition as follows:
SQL> SELECT COUNT (1) FROM tbl_range_list_part SUBPARTITION (part1_sub_index); COUNT(1) ---------- 56 SQL> SELECT COUNT (1) FROM tbl_range_list_part SUBPARTITION (part2_sub_table); COUNT(1) ---------- 40
Range-range partitioning
To create a range-range partitioned table, use PARTITION BY RANGE SUBPARTITION BY RANGE syntax as follows:
SQL> CREATE TABLE tbl_range_range_part PARTITION BY RANGE (object_id) SUBPARTITION BY RANGE (namespace) (PARTITION part1 VALUES LESS THAN (1000) ( SUBPARTITION part1_sub1 VALUES LESS THAN (4), SUBPARTITION part1_sub2 VALUES LESS THAN (MAXVALUE)), PARTITION part2 VALUES LESS THAN (maxvalue) ( SUBPARTITION part2_sub1 VALUES LESS THAN (10), SUBPARTITION part2_sub2 VALUES LESS THAN (MAXVALUE))) AS SELECT * FROM dba_objects; Table created. SQL>
Now query DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS view to get list of partitions and subpartitions:
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT FROM dba_tab_partitions WHERE table_name = ‘TBL_RANGE_RANGE_PART’; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT ------------------------------ ------------------------------ ------------------ TBL_RANGE_RANGE_PART PART1 2 TBL_RANGE_RANGE_PART PART2 2 SQL> SELECT table_name, partition_name, subpartition_name FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = ‘TBL_RANGE_RANGE_PART’; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ ------------------------ TBL_RANGE_RANGE_PART PART1 PART1_SUB1 TBL_RANGE_RANGE_PART PART1 PART1_SUB2 TBL_RANGE_RANGE_PART PART2 PART2_SUB1 TBL_RANGE_RANGE_PART PART2 PART2_SUB2 SQL> SELECT COUNT (1) FROM tbl_range_range_part SUBPARTITION (part2_sub2); COUNT(1) ---------- 1327 SQL>