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.

Comments ( 2 )

  1. Replynormen
    Hi, First of all thank you for your usefull posts about OCM 11g preparation. I bought your book too to prepare to my OCM exam. In this article you made a typo in "Creating multi column range partition" section. You wrote 3 columns as partition key but you define only 2 (year, month) in the create statement. Regards, normen
    • ReplyKamran Agayev
      Thank you Normen for the correction. Will be more attentive in the next release. Appreciate your feedback and good luck to you in the exam!

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>