Understanding COMPOSITE/SUB PARTITION In Oracle Database.

Understanding COMPOSITE/SUB PARTITION In Oracle Database.

PRAGMA EXCEPTION_INIT

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 :

split_partition

Understanding ONLINE Split Partition In Oracle Database 12c.

PRAGMA EXCEPTION_INIT

Understanding INTERVAL PARTITION In Oracle Database.

iot

Index Organized Tables In Oracle

8 thoughts on “Index Organized Tables In Oracle”

  1. Good day! Would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would really appreciate your content. Please let me know. Cheers

    Reply

    1. Thank you for you response.

      Please share your twitter account so that i can start sharing my content, also requesting you to please ask your friend & colleagues to subscribe my blog site for all latest updates.

      Thank You…

      Reply

  2. You could definitely see your enthusiasm in the work you write. The world hopes for more passionate writers like you who are not afraid to say how they believe. At all times follow your heart. brist på järn lleme.prizsewoman.com/map11.php

    Reply

  5. I have read somewhere similar point of view and I totally agree with what you said. However, there are also some other things could be mentioned on this topic, but overall I like what you described.
    In this website there is also a lot of interesting and useful information:

    Reply

Leave a Reply

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