OCM Exam Topic – Creating composite partitions (part 1)

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>

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>