Understanding COMPOSITE/SUB PARTITION In Oracle Database.

Understanding COMPOSITE/SUB PARTITION In Oracle Database.

Cursor

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.

Cursor

Understanding RANGE PARTITION In Oracle Database.

Understanding UNUSABLE Index

8 thoughts on “Understanding UNUSABLE Index”

  5. Great to see another voice sharing experiences and wisdom about Oracle Database. Thanks for taking the time to do this, Shoumadip!

    Reply

  6. Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements with high selectivity, often are characterized by the use of index access paths. Decision support systems, on the other hand, tend to use partitioned tables and perform full scans of the relevant partitions.

    Reply

Leave a Reply

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