Home » SQL & PL/SQL » SQL & PL/SQL » Partition (19c)
Partition [message #689394] |
Thu, 07 December 2023 09:59  |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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. 
|
|
|
Re: Partition [message #689401 is a reply to message #689398] |
Fri, 08 December 2023 15:26   |
Solomon Yakobson
Messages: 3303 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
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 22:25:00 CST 2025
|