Home » SQL & PL/SQL » SQL & PL/SQL » Partition (19c)
Partition [message #689394] Thu, 07 December 2023 09:59 Go to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

I have requirement to create yearly partition and sub-parition every quarter. looking for syntax.

Appreciate your help

thanks  

CREATE TABLE TEST_1
( order_id      NUMBER NOT NULL
, order_date    DATE NOT NULL
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (order_date)
( PARTITION p_2023 VALUES LESS THAN (TO_DATE('31-DEC-2023','DD-MON-YYYY'))
	( 
	SUBPARTITION sp_q1_2023 VALUES LESS THAN (TO_DATE('31-MAR-2023','DD-MON-YYYY')),
    SUBPARTITION sp_q2_2023 VALUES LESS THAN (TO_DATE('30-JUN-2023','DD-MON-YYYY')),
    SUBPARTITION sp_q3_2023 VALUES LESS THAN (TO_DATE('30-SEP-2023','DD-MON-YYYY')),
    SUBPARTITION sp_q4_2023 VALUES LESS THAN (TO_DATE('31-DEC-2023','DD-MON-YYYY'))
  	),
  PARTITION p_2022 VALUES LESS THAN (TO_DATE('30-DEC-2022','DD-MON-YYYY'))
	( 
	SUBPARTITION sp_q1_2022 VALUES LESS THAN (TO_DATE('31-MAR-2022','DD-MON-YYYY')),
    SUBPARTITION sp_q2_2022 VALUES LESS THAN (TO_DATE('30-JUN-2022','DD-MON-YYYY')),
    SUBPARTITION sp_q3_2022 VALUES LESS THAN (TO_DATE('30-SEP-2022','DD-MON-YYYY')),
    SUBPARTITION sp_q4_2022 VALUES LESS THAN (TO_DATE('31-DEC-2022','DD-MON-YYYY'))
 	)
 )
Re: Partition [message #689395 is a reply to message #689394] Thu, 07 December 2023 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68598
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE TEST_1
  2  ( order_id      NUMBER NOT NULL
  3  , order_date    DATE NOT NULL
  4  )
  5  PARTITION BY RANGE (order_date)
  6  SUBPARTITION BY RANGE (order_date)
  7  (
  8    PARTITION p_2022 VALUES LESS THAN (TO_DATE('01/01/2023','DD/MM/YYYY'))
  9     (
 10     SUBPARTITION sp_q1_2022 VALUES LESS THAN (TO_DATE('01/04/2022','DD/MM/YYYY')),
 11      SUBPARTITION sp_q2_2022 VALUES LESS THAN (TO_DATE('01/07/2022','DD/MM/YYYY')),
 12      SUBPARTITION sp_q3_2022 VALUES LESS THAN (TO_DATE('01/10/2022','DD/MM/YYYY')),
 13      SUBPARTITION sp_q4_2022 VALUES LESS THAN (TO_DATE('01/01/2023','DD/MM/YYYY'))
 14     ),
 15    PARTITION p_2023 VALUES LESS THAN (TO_DATE('01/01/2024','DD/MM/YYYY'))
 16     (
 17     SUBPARTITION sp_q1_2023 VALUES LESS THAN (TO_DATE('01/04/2023','DD/MM/YYYY')),
 18      SUBPARTITION sp_q2_2023 VALUES LESS THAN (TO_DATE('01/07/2023','DD/MM/YYYY')),
 19      SUBPARTITION sp_q3_2023 VALUES LESS THAN (TO_DATE('01/10/2023','DD/MM/YYYY')),
 20      SUBPARTITION sp_q4_2023 VALUES LESS THAN (TO_DATE('01/01/2024','DD/MM/YYYY'))
 21     )
 22   )
 23  /

Table created.
Re: Partition [message #689397 is a reply to message #689395] Thu, 07 December 2023 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68598
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also play in the following way to ease the maintenance:
SQL> CREATE TABLE TEST_3
  2  ( order_id      NUMBER NOT NULL
  3  , order_date    DATE NOT NULL
  4  , order_year    INTEGER AS (EXTRACT(YEAR FROM order_date))
  5  , order_month   INTEGER AS (EXTRACT(MONTH FROM order_date))
  6  )
  7  PARTITION BY RANGE (order_year) INTERVAL (1)
  8  SUBPARTITION BY LIST (order_month)
  9  SUBPARTITION TEMPLATE (
 10      SUBPARTITION q1 VALUES (1,2,3),
 11      SUBPARTITION q2 VALUES (4,5,6),
 12      SUBPARTITION q3 VALUES (7,8,9),
 13      SUBPARTITION q4 VALUES (10,11,12)
 14  )
 15  (
 16    PARTITION p_2022 VALUES LESS THAN (2023),
 17    PARTITION p_2023 VALUES LESS THAN (2024)
 18  )
 19  /

Table created.

SQL> col partition_name format a14
SQL> col high_value format a20
SQL> SELECT partition_name, high_value
  2  FROM DBA_TAB_PARTITIONS
  3  WHERE table_name = 'TEST_3'
  4  ORDER BY 1
  5  /
PARTITION_NAME HIGH_VALUE
-------------- --------------------
P_2022         2023
P_2023         2024

2 rows selected.

SQL> break on partition_name dup skip 1
SQL> col subpartition_name format a17
SQL> SELECT partition_name, subpartition_name, high_value
  2  FROM DBA_TAB_SUBPARTITIONS
  3  WHERE table_name = 'TEST_3'
  4  ORDER BY 1,2
  5  /
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
-------------- ----------------- --------------------
P_2022         P_2022_Q1         1, 2, 3
P_2022         P_2022_Q2         4, 5, 6
P_2022         P_2022_Q3         7, 8, 9
P_2022         P_2022_Q4         10, 11, 12

P_2023         P_2023_Q1         1, 2, 3
P_2023         P_2023_Q2         4, 5, 6
P_2023         P_2023_Q3         7, 8, 9
P_2023         P_2023_Q4         10, 11, 12


8 rows selected.
Re: Partition [message #689398 is a reply to message #689397] Thu, 07 December 2023 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68598
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Now if you insert a row for a partition that does not exist Oracle automatically creates one with its sub-partitions:
SQL> insert into test_3 (order_id,order_date) values(1000, to_date('10/05/2024','DD/MM/YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> cl break
breaks cleared
SQL> col partition_name format a14
SQL> col high_value format a20
SQL> SELECT partition_name, high_value
  2  FROM DBA_TAB_PARTITIONS
  3  WHERE table_name = 'TEST_3'
  4  ORDER BY 1
  5  /
PARTITION_NAME HIGH_VALUE
-------------- --------------------
P_2022         2023
P_2023         2024
SYS_P3121      2025

3 rows selected.

SQL> break on partition_name dup skip 1
SQL> col subpartition_name format a17
SQL> SELECT partition_name, subpartition_name, high_value
  2  FROM DBA_TAB_SUBPARTITIONS
  3  WHERE table_name = 'TEST_3'
  4  ORDER BY 1,2
  5  /
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
-------------- ----------------- --------------------
P_2022         P_2022_Q1         1, 2, 3
P_2022         P_2022_Q2         4, 5, 6
P_2022         P_2022_Q3         7, 8, 9
P_2022         P_2022_Q4         10, 11, 12

P_2023         P_2023_Q1         1, 2, 3
P_2023         P_2023_Q2         4, 5, 6
P_2023         P_2023_Q3         7, 8, 9
P_2023         P_2023_Q4         10, 11, 12

SYS_P3121      SYS_SUBP3117      1, 2, 3
SYS_P3121      SYS_SUBP3118      4, 5, 6
SYS_P3121      SYS_SUBP3119      7, 8, 9
SYS_P3121      SYS_SUBP3120      10, 11, 12


12 rows selected.
Unfortunately Oracle is not smart enough to name them as we'd want. Sad
Re: Partition [message #689401 is a reply to message #689398] Fri, 08 December 2023 15:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
I don't see any advantage in partitioning by year and subpartitioning by quarter against simply partitioning by quarter. In fact all I see is complications like having to pre-create partitions/subpartitions or adding calculated columns. And using calculated columns is affecting partition pruning:

SQL> insert into test_3 (order_id,order_date) values(1000, to_date('10/05/2024','DD/MM/YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST_3')

PL/SQL procedure successfully completed.

SQL> explain plan for select * from test_3 where order_date = date '2024-05-10';

Explained.

SQL> set linesize 95
SQL> select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2948859815

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    18 |   275   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|        |     1 |    18 |   275   (1)| 00:00:01 |     1 |1048575|
|   2 |   PARTITION LIST ALL|        |     1 |    18 |   275   (1)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL| TEST_3 |     1 |    18 |   275   (1)| 00:00:01 |     1 |1048575|
----------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ORDER_DATE"=TO_DATE(' 2024-05-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.

SQL>
As you can see no partition pruning was done. Now we are forced to change queries to use calculated columns to take advantage of partition pruning:

SQL> explain plan for select * from test_3 where order_year = 2024 and order_month = 5;

Explained.

SQL> select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3310717041

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    18 |   275   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|        |     1 |    18 |   275   (1)| 00:00:01 |     3 |     3 |
|   2 |   PARTITION LIST SINGLE|        |     1 |    18 |   275   (1)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL   | TEST_3 |     1 |    18 |   275   (1)| 00:00:01 |    10 |    10 |
-------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ORDER_YEAR"=2024 AND "ORDER_MONTH"=5)

15 rows selected.

SQL>
SY.

[Updated on: Fri, 08 December 2023 15:27]

Report message to a moderator

Re: Partition [message #689408 is a reply to message #689394] Tue, 12 December 2023 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68598
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Partition [message #689454 is a reply to message #689408] Sun, 07 January 2024 12:26 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


I'm all set. thank you for all your valuable input.

thanks.
Previous Topic: Create Partition and Sub-Partition
Next Topic: plsql writen test questions
Goto Forum:
  


Current Time: Thu Feb 29 13:49:06 CST 2024