Creating a Composite Hash-Hash Partitioned Table

View All Scripts Login to Run Script

Script Name Creating a Composite Hash-Hash Partitioned Table
Description Administering composite hash-hash partitioning
Area Partitioning
Referenced In Database VLDB and Partitioning Guide
Contributor Oracle
Created Thursday October 20, 2016

Statement 1

The table is created with composite hash-hash partitioning. For this example, the table has four hash partitions for department_id and each of those four partitions has eight subpartitions for course_id.

Create composite hash-hash partitioned table

CREATE TABLE departments_courses_hash (  
             department_id NUMBER(4) NOT NULL,   
             department_name VARCHAR2(30),  
             course_id NUMBER(4) NOT NULL)  
     PARTITION BY HASH(department_id)  
     SUBPARTITION BY HASH (course_id) SUBPARTITIONS 8 PARTITIONS 4

Table created.

Statement 2

Display the partitions/subpartitions in the table with this SQL query. System generated names have been assigned to the partitions and subpartitions. Note that there are 32 subpartitions (4 x 8 = 32).

Viewing the structure of the composite hash-hash partitioned table

SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32) FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='DEPARTMENTS_COURSES_HASH'


SUBSTR(TABLE_NAME,1,32) SUBSTR(PARTITION_NAME,1,32) SUBSTR(SUBPARTITION_NAME,1,32)
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4816
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4815
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4814
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4813
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4812
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4811
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4810
DEPARTMENTS_COURSES_HASH SYS_P4841 SYS_SUBP4809
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4824
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4823
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4822
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4821
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4820
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4819
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4818
DEPARTMENTS_COURSES_HASH SYS_P4842 SYS_SUBP4817
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4832
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4831
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4830
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4829
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4828
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4827
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4826
DEPARTMENTS_COURSES_HASH SYS_P4843 SYS_SUBP4825
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4840
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4839
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4838
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4837
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4836
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4835
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4834
DEPARTMENTS_COURSES_HASH SYS_P4844 SYS_SUBP4833

32 rows selected.

SUBSTR(TABLE_NAME,1,32)	SUBSTR(PARTITION_NAME,1,32)	SUBSTR(SUBPARTITION_NAME,1,32)
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4816
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4815
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4814
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4813
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4812
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4811
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4810
DEPARTMENTS_COURSES_HASH	SYS_P4841	SYS_SUBP4809
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4824
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4823
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4822
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4821
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4820
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4819
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4818
DEPARTMENTS_COURSES_HASH	SYS_P4842	SYS_SUBP4817
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4832
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4831
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4830
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4829
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4828
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4827
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4826
DEPARTMENTS_COURSES_HASH	SYS_P4843	SYS_SUBP4825
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4840
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4839
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4838
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4837
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4836
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4835
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4834
DEPARTMENTS_COURSES_HASH	SYS_P4844	SYS_SUBP4833

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database