OCM exam topic – Administer partitioned tables and indexes using appropriate methods and keys – part 1
In this and next consequence posts, I will be talking in more detail about creating different type of partitioned tables and how to manage them. I will try to skip basics and show different partition creation and management methods that I covered in OCM 11g Study Guide book
So here’s the topics we’re going to cover regarding the partition creation:
– Creating Range partitioned table
– Creating List partitioned table
– Creating Hash partitioned table
– Creating Interval partitioned table
– Creating Reference partitioned table
– Creating Composite partitioned table
– Using multicolumn partitioned keys
– Creating Virtual Column based partitioning
As a reference, please use the following documentation and metalink notes:
– Database VLDB and Partitioning Guide -> 4 Partition Administration -> Creating Partitions
– Examples For Creating Partitioned table With Different Partitioning Strategies (Doc ID 733311.1)
– Master Note for Partitioning (Doc ID 1312352.1)
– Information Center: Using Partitioning in the Oracle Database (Doc ID 1551512.2)
– 11g Partitioning Enhancements (Doc ID 452447.1)
And here’s the topics regarding the partition maintenance operations that we are going to cover in the next posts:
– Adding a new partition
– Dropping a partition
– Coalescing partitions
– Exchange partitions
– Merging partitions
– Renaming partitions
– Moving partitions
– Splitting partitions
– Truncating partitions
Creating Range partitioned table
If you have configured OEM (during the OCM exam, you should have already configured the OEM, it’s the mandatory task before the partitioning section), I would highly recommend to use OEM to create a partitioned table. I’m not going to show how to create it, as it is very easy – but will show the brief step.
To create a range partitioned table, open EM, switch to the GRIDDB database and click on Schema link. From the Database objects section, select Tables link and click Create button. Select Standard (Heap Organized) option and click Continue.
You should see Partitions tab, click on it, select the Range partition from the list and click Continue.
Provide the partition column, specify the range
On the next screen, provide “1” for the “Number of partitions” value. Check “Use MAXVALUE” option, uncheck “Automatically generate partition values” option and click Next.
Provide the tablespace name for the partitions to be stored as it is required in the OCM task and click Next.
On the last screen, click on “Insert Before” button and provide 4 partitions with different values
PART_2015 will contain all rows with DATE value before 2016 January 1: to_date(‘01-JAN-2016’,’dd-MON-yyyy’)
PART_JAN_2016 will contain all rows with DATE value for 2016 January: to_date(‘01-FEB-2016’,’dd-MON-yyyy’)
PART_FEB_2016 will contain all rows with DATE value for 2016 February:to_date(‘01-MAR-2016’,’dd-MON-yyyy’)
PART_REST will contain all rows that doesn’t match any criteria: MAXVALUE
Then click Finish and “Show SQL” button to see the SQL command of partition creation
To get the name of all partitions of the specific table, query DBA_TAB_PARTITIONS view:
SQL> SELECT partition_name FROM dba_tab_partitions WHERE table_name=’TBL_RANGE_PART’; PARTITION_NAME ------------------------------ PART_2015 PART_JAN_2016 PART_FEB_2016 TBL_RANGE_PART_P1
Creating multi column range partition
You might be asked to create a multi column range partition during an exam. In the following example, we will specify 3 columns as a partitioning key:
CREATE TABLE TBL_RANGE_PART2 ( id NUMBER, name VARCHAR2 (10), surname VARCHAR2 (10), year NUMBER, month NUMBER, day NUMBER ) PARTITION BY RANGE (year, month) ( PARTITION JAN_2016 VALUES LESS THAN (2016,02), PARTITION FEB_2016 VALUES LESS THAN (2016,03))
Creating List partitioned table
By using PARTITION BY LIST clause of the CREATE TABLE command, you can create a list partitioned table by specifying distinct values for the partitioning key for each partition. List partition is used to group an unsorted data. Only one single column can be specified as a key partition and the same values can’t be specified in the same or different partition. To create a List partitioned table, create a new table (TBL_LIST_PART) from EM with 4 columns with following columns:
Id: NUMBER Name: VARCHAR2(20) Surname: VARCHAR2(20) Course: VARCHAR2(20)
From the partitioning columns section, select “Course” column and click Next
Provide “1” for “Number of partitions” value and click Next. Make sure “Use DEFAULT” option is selected.
Then provide name of the tablespace and click Next.
Create four partitions and provide value for each partition.
Creating Hash partitioned table
If the data doesn’t have any logical range and can’t be presented in a list, the hash partitioned can be used. If hash partition is used, the rows are mapped into corresponding partitions based on the hash value of the partitioning key.
Use PARTITION BY HASH syntax to provide the partitioning key and use PARTITIONS clause to specify number of partitions.
Creating HASH partition is from OEM is a simple process. Create a table, switch to Partitions tab and click on Create button. Select HASH partition and click Next. Provide the number of partitions and provide the tablespaces if required.
If you are asked to create a hash partitioned table without GUI, use an examples provided under “Creating a Hash Partitioned Table” topic in the documentation provided above.
Below, we create a hash partitioned table with 8 partitions based on DBA_OBJECTS view as follows:
SQL> CREATE TABLE tbl_hash_part PARTITION BY HASH (object_id) PARTITIONS 8 AS SELECT * FROM dba_objects; Table created. SQL>
Query DBA_TAB_PARTITIONS view to get the list of all partitions:
SQL> SELECT table_name, partition_name FROM dba_tab_partitions WHERE table_name = ‘TBL_HASH_PART’; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ TBL_HASH_PART SYS_P41 TBL_HASH_PART SYS_P42 TBL_HASH_PART SYS_P43 TBL_HASH_PART SYS_P44 TBL_HASH_PART SYS_P45 TBL_HASH_PART SYS_P46 TBL_HASH_PART SYS_P47 TBL_HASH_PART SYS_P48 8 rows selected. SQL>
If you are asked to store partitions on different tablespaces, use STORE IN syntax
SQL> CREATE TABLE tbl_hash_part1 PARTITION BY HASH (object_id) PARTITIONS 8 store in (tbs_part1, tbs_part2, tbs_part3, tbs_part4) AS SELECT * FROM dba_objects; Table created. SQL>
Use the following syntax to store specific partition in specific tablspace:
SQL> CREATE TABLE tbl_hash_part2 PARTITION BY HASH (object_id) (partition part1 tablespace tbs_part1, partition part2 tablespace tbs_part2) AS SELECT * FROM dba_objects; Table created. SQL>
If you want to verify the data distribution among partitions, gather the table statistics and query DBA_TAB_PARTITIONS view as follows:
SQL> EXECUTE dbms_stats.gather_table_stats(‘SYS’,’TBL_HASH_PART2’) PL/SQL procedure successfully completed. SQL> SELECT table_name, partition_name, partition_position, num_rows FROM user_tab_partitions WHERE table_name = ‘TBL_HASH_PART2’; TABLE_NAME PARTITION_NAME PARTITION_POSITION NUM_ROWS ------------------------- ------------------------- ------------------ ---------- TBL_HASH_PART2 PART1 1 37750 TBL_HASH_PART2 PART2 2 37438 SQL>
In the next part, we will talk about how to create an Interval partitioned, Reference partitioned, System partitioned, Virtual Column based partitioned tables, Composite partitioned like List-List, List-Interval, Interval-Range, Interval-hash partitioned tables and etc.