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'))
 	)
 )

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.

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.

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.

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>

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>