Understanding COMPOSITE/SUB PARTITION In Oracle Database.

Understanding COMPOSITE/SUB PARTITION In Oracle Database.

composite_partition

Composite partitioning OR sub partitioning is a partitioning technique in which the table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method.

It is used when data is too huge to be maintained by single partition key.

• RANGE-HASH
• RANGE-LIST
• RANGE-RANGE

• LIST-HASH
• LIST-RANGE
• LIST-LIST

• HASH-HASH
• HASH-RANGE
• HASH-LIST

Partitions are only logical,data is actually stored in sub partitions.Segments will be actually allocated to sub partitions.

SQL> CREATE TABLE transaction_demo
  2  (
  3   tran_id INT,
  4   emp_id INT,
  5   region VARCHAR2(4),
  6   amount INT,
  7   sales_date DATE
  8  )
  9   PARTITION BY RANGE(sales_date)
 10   SUBPARTITION BY LIST(region)
 11   (
 12    PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD'))
 13       (
 14       SUBPARTITION P_2016_AP_EMEA VALUES('AP','EMEA'),
 15       SUBPARTITION P_2016_NA VALUES('NA')
 16      ),
 17    PARTITION P_2017 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD'))
 18       (
 19       SUBPARTITION P_2017_AP VALUES('AP'),
 20       SUBPARTITION P_2017_EMEA VALUES('EMEA'),
 21       SUBPARTITION P_2017_NA VALUES('NA')
 22      ),
 23    PARTITION P_2018 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD'))
 24       (
 25       SUBPARTITION P_2018_AP VALUES('AP'),
 26       SUBPARTITION P_2018_EMEA VALUES('EMEA'),
 27       SUBPARTITION P_2018_NA VALUES('NA')
 28      )
 29   );

Table created.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';

no rows selected

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2
TRANSACTION_DEMO   P_2017                  3
TRANSACTION_DEMO   P_2018                  3

SQL> EXEC dbms_stats.gather_table_stats('COURSE','TRANSACTION_DEMO');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2               0
TRANSACTION_DEMO   P_2017                  3               0
TRANSACTION_DEMO   P_2018                  3               0

SQL> INSERT INTO transaction_demo VALUES(1,1,'AP',100,TO_DATE('2016-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(2,2,'EMEA',200,TO_DATE('2016-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(3,3,'NA',300,TO_DATE('2016-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(4,4,'AP',400,TO_DATE('2017-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(5,5,'EMEA',500,TO_DATE('2017-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(6,6,'NA',600,TO_DATE('2017-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(7,7,'AP',700,TO_DATE('2018-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(8,8,'NA',800,TO_DATE('2018-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(9,9,'AP',900,TO_DATE('2019-03-31','YYYY-MM-DD'));
INSERT INTO transaction_demo VALUES(9,9,'AP',900,TO_DATE('2019-03-31','YYYY-MM-DD'))
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

SQL> COMMIT;

Commit complete.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';

SEGMENT_NAME       BLOCKS         MB
----------------- -------- ----------
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8

7 rows selected.

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2               0
TRANSACTION_DEMO   P_2017                  3               0
TRANSACTION_DEMO   P_2018                  3               0

SQL> EXEC dbms_stats.gather_table_stats('COURSE','TRANSACTION_DEMO');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2               3
TRANSACTION_DEMO   P_2017                  3               3
TRANSACTION_DEMO   P_2018                  3               2

SQL> SELECT * FROM transaction_demo;

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ ----------
         1          1 AP          100   31-MAR-16
         2          2 EMEA        200   31-MAR-16
         3          3 NA          300   31-MAR-16
         4          4 AP          400   31-MAR-17
         5          5 EMEA        500   31-MAR-17
         6          6 NA          600   31-MAR-17
         7          7 AP          700   31-MAR-18
         8          8 NA          800   31-MAR-18

8 rows selected.

SQL> SELECT * FROM transaction_demo PARTITION(P_2016);

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ ----------
         1          1 AP          100   31-MAR-16
         2          2 EMEA        200   31-MAR-16
         3          3 NA          300   31-MAR-16

SQL> SELECT * FROM transaction_demo PARTITION(P_2017);

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ ----------
         4          4 AP          400   31-MAR-17
         5          5 EMEA        500   31-MAR-17
         6          6 NA          600   31-MAR-17

SQL> SELECT * FROM transaction_demo PARTITION(P_2018);

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ ----------
         7          7 AP          700   31-MAR-18
         8          8 NA          800   31-MAR-18
SQL> CREATE TABLE transaction_demo
  2  (
  3   tran_id INT,
  4   emp_id INT,
  5   region VARCHAR2(4),
  6   amount INT,
  7   sales_date DATE
  8  )
  9   PARTITION BY RANGE(sales_date)
 10   SUBPARTITION BY LIST(region)
 11   (
 12    PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD'))
 13       (
 14       SUBPARTITION P_2016_AP_EMEA VALUES('AP','EMEA'),
 15       SUBPARTITION P_2016_NA VALUES('NA')
 16      ),
 17    PARTITION P_2017 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD'))
 18       (
 19       SUBPARTITION P_2017_AP VALUES('AP'),
 20       SUBPARTITION P_2017_EMEA VALUES('EMEA'),
 21       SUBPARTITION P_2017_NA VALUES('NA')
 22      ),
 23    PARTITION P_2018 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD'))
 24       (
 25       SUBPARTITION P_2018_AP VALUES('AP'),
 26       SUBPARTITION P_2018_EMEA VALUES('EMEA'),
 27       SUBPARTITION P_2018_NA VALUES('NA')
 28      ),
 29    PARTITION P_MAX_VALUE_R VALUES LESS THAN (MAXVALUE)
 30      (
 31       SUBPARTITION P_MAX_VALUE_L VALUES(DEFAULT)
 32      )
 33   );

Table created.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';

no rows selected

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2
TRANSACTION_DEMO   P_2017                  3
TRANSACTION_DEMO   P_2018                  3

SQL> EXEC dbms_stats.gather_table_stats('COURSE','TRANSACTION_DEMO');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2               0
TRANSACTION_DEMO   P_2017                  3               0
TRANSACTION_DEMO   P_2018                  3               0

SQL> INSERT INTO transaction_demo VALUES(1,1,'AP',100,TO_DATE('2016-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(2,2,'EMEA',200,TO_DATE('2016-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(3,3,'NA',300,TO_DATE('2016-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(4,4,'AP',400,TO_DATE('2017-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(5,5,'EMEA',500,TO_DATE('2017-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(6,6,'NA',600,TO_DATE('2017-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(7,7,'AP',700,TO_DATE('2018-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(8,8,'NA',800,TO_DATE('2018-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(9,9,'EMEA',900,TO_DATE('2018-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(10,10,'EMEA',1000,TO_DATE('2020-03-31','YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO transaction_demo VALUES(11,11,'AP',1100,TO_DATE('2021-03-31','YYYY-MM-DD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';

SEGMENT_NAME       BLOCKS         MB
------------------ ------ ----------
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8
TRANSACTION_DEMO     1024          8

9 rows selected.

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2               0
TRANSACTION_DEMO   P_2017                  3               0
TRANSACTION_DEMO   P_2018                  3               0
TRANSACTION_DEMO   P_MAX_VALUE_R           1               0

SQL> EXEC dbms_stats.gather_table_stats('COURSE','TRANSACTION_DEMO');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,partition_name,subpartition_count,num_rows FROM user_tab_partitions WHERE table_name='TRANSACTION_DEMO';

TABLE_NAME       PARTITION_NAME    SUBPARTITION_COUNT   NUM_ROWS
---------------- ---------------- -------------------- ----------
TRANSACTION_DEMO   P_2016                  2               3
TRANSACTION_DEMO   P_2017                  3               3
TRANSACTION_DEMO   P_2018                  3               3
TRANSACTION_DEMO   P_MAX_VALUE_R           1               2

SQL> SELECT * FROM transaction_demo;

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ -----------
         1          1 AP          100   31-MAR-16
         2          2 EMEA        200   31-MAR-16
         3          3 NA          300   31-MAR-16
         4          4 AP          400   31-MAR-17
         5          5 EMEA        500   31-MAR-17
         6          6 NA          600   31-MAR-17
         7          7 AP          700   31-MAR-18
         9          9 EMEA        900   31-MAR-18
         8          8 NA          800   31-MAR-18
        10         10 EMEA       1000   31-MAR-20
        11         11 AP         1100   31-MAR-21

11 rows selected.

SQL> SELECT * FROM transaction_demo PARTITION(P_2016);

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ -----------
         1          1 AP          100   31-MAR-16
         2          2 EMEA        200   31-MAR-16
         3          3 NA          300   31-MAR-16

SQL> SELECT * FROM transaction_demo PARTITION(P_2017);

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ -----------
         4          4 AP          400   31-MAR-17
         5          5 EMEA        500   31-MAR-17
         6          6 NA          600   31-MAR-17

SQL> SELECT * FROM transaction_demo PARTITION(P_2018);

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ -----------
         7          7 AP          700   31-MAR-18
         9          9 EMEA        900   31-MAR-18
         8          8 NA          800   31-MAR-18

SQL> SELECT * FROM transaction_demo PARTITION(P_MAX_VALUE_R);

   TRAN_ID     EMP_ID REGION     AMOUNT SALES_DATE
------------- ------- ---------- ------ -----------
        10         10 EMEA       1000   31-MAR-20
        11         11 AP         1100   31-MAR-21

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Related Topics :

composite_partition

Index Spawning Vs Index Splitting In B-Tree Index

split_partition

Understanding ONLINE Split Partition In Oracle Database 12c.

composite_partition

Understanding SET TRANSACTION In Oracle 19c

Leave a Reply

Your email address will not be published. Required fields are marked *