Скачать или смотреть Range and List Partition on same table simultaneously Composite Partitioning

Composite partitioning = 2-level hierarchy:

Top-level (main) partition — based on one partitioning method (e.g. RANGE)

Subpartition — based on another method (e.g. LIST)

This lets Oracle first divide the table into large chunks (by range), then further divide those chunks logically (by region, status, etc.).



-- drop table transaction_data




CREATE TABLE transaction_data (
trans_id NUMBER,
region VARCHAR2(10),
trans_date DATE,
amount NUMBER(10,2),
customer_id NUMBER
)
partition by range(trans_date)
subpartition by list(region)
SUBPARTITION TEMPLATE
(
subpartition p_north values ('NORTH'),
subpartition p_south values ('SOUTH'),
subpartition p_west values ('WEST'),
subpartition p_east values ('EAST')
)
(
partition P_PREVIOUS_FY values less than ( to_date('01-APR-2025','DD-MON-YYYY') ),
partition P_APR25 values less than ( to_date('01-MAY-2025','DD-MON-YYYY') ),
partition P_MAY25 values less than ( to_date('01-JUN-2025','DD-MON-YYYY') ),
partition P_JUN25 values less than ( to_date('01-JUL-2025','DD-MON-YYYY') ),
partition P_JUL25 values less than ( to_date('01-AUG-2025','DD-MON-YYYY') ),
partition P_AUG25 values less than ( to_date('01-SEP-2025','DD-MON-YYYY') ),
partition P_SEP25 values less than ( to_date('01-OCT-2025','DD-MON-YYYY') ),
partition P_OCT25 values less than ( to_date('01-NOV-2025','DD-MON-YYYY') ),
partition P_NOV25 values less than ( to_date('01-DEC-2025','DD-MON-YYYY') ),
partition P_DEC25 values less than ( to_date('01-JAN-2026','DD-MON-YYYY') ),
partition P_JAN26 values less than ( to_date('01-FEB-2026','DD-MON-YYYY') ),
partition P_FEB26 values less than ( to_date('01-MAR-2026','DD-MON-YYYY') ),
partition P_MAR26 values less than ( to_date('01-APR-2026','DD-MON-YYYY') )
)



-- Transactions covering FY 2024-25 and FY 2025-26


BEGIN

INSERT INTO transaction_data VALUES (1, 'NORTH', TO_DATE('2024-04-10','YYYY-MM-DD'), 1200.50, 101);
INSERT INTO transaction_data VALUES (2, 'SOUTH', TO_DATE('2024-05-08','YYYY-MM-DD'), 800.00, 102);
INSERT INTO transaction_data VALUES (3, 'EAST', TO_DATE('2024-05-22','YYYY-MM-DD'), 950.75, 103);
INSERT INTO transaction_data VALUES (4, 'WEST', TO_DATE('2024-06-12','YYYY-MM-DD'), 1100.00, 104);
INSERT INTO transaction_data VALUES (5, 'NORTH', TO_DATE('2024-07-03','YYYY-MM-DD'), 1300.00, 105);
INSERT INTO transaction_data VALUES (6, 'SOUTH', TO_DATE('2024-07-22','YYYY-MM-DD'), 780.20, 106);
INSERT INTO transaction_data VALUES (7, 'EAST', TO_DATE('2024-08-14','YYYY-MM-DD'), 860.45, 107);
INSERT INTO transaction_data VALUES (8, 'WEST', TO_DATE('2024-09-05','YYYY-MM-DD'), 900.00, 108);
INSERT INTO transaction_data VALUES (9, 'NORTH', TO_DATE('2024-09-28','YYYY-MM-DD'), 1250.00, 109);
INSERT INTO transaction_data VALUES (10, 'SOUTH', TO_DATE('2024-10-18','YYYY-MM-DD'), 770.25, 110);
INSERT INTO transaction_data VALUES (11, 'EAST', TO_DATE('2024-11-09','YYYY-MM-DD'), 890.75, 111);
INSERT INTO transaction_data VALUES (12, 'WEST', TO_DATE('2024-12-02','YYYY-MM-DD'), 910.50, 112);
INSERT INTO transaction_data VALUES (13, 'NORTH', TO_DATE('2025-01-10','YYYY-MM-DD'), 1500.00, 113);
INSERT INTO transaction_data VALUES (14, 'SOUTH', TO_DATE('2025-02-06','YYYY-MM-DD'), 700.00, 114);
INSERT INTO transaction_data VALUES (15, 'EAST', TO_DATE('2025-03-12','YYYY-MM-DD'), 950.00, 115);
INSERT INTO transaction_data VALUES (16, 'WEST', TO_DATE('2025-03-25','YYYY-MM-DD'), 1020.00, 116);

-- FY 2025-26
INSERT INTO transaction_data VALUES (17, 'NORTH', TO_DATE('2025-04-15','YYYY-MM-DD'), 1400.00, 117);
INSERT INTO transaction_data VALUES (18, 'SOUTH', TO_DATE('2025-05-09','YYYY-MM-DD'), 830.00, 118);
INSERT INTO transaction_data VALUES (19, 'EAST', TO_DATE('2025-06-11','YYYY-MM-DD'), 970.00, 119);
INSERT INTO transaction_data VALUES (20, 'WEST', TO_DATE('2025-07-08','YYYY-MM-DD'), 1150.00, 120);
INSERT INTO transaction_data VALUES (21, 'NORTH', TO_DATE('2025-08-03','YYYY-MM-DD'), 1200.00, 121);
INSERT INTO transaction_data VALUES (22, 'SOUTH', TO_DATE('2025-09-02','YYYY-MM-DD'), 860.00, 122);
INSERT INTO transaction_data VALUES (23, 'EAST', TO_DATE('2025-10-04','YYYY-MM-DD'), 910.00, 123);
INSERT INTO transaction_data VALUES (24, 'WEST', TO_DATE('2025-11-15','YYYY-MM-DD'), 980.00, 124);
INSERT INTO transaction_data VALUES (25, 'NORTH', TO_DATE('2025-12-05','YYYY-MM-DD'), 1250.00, 125);
INSERT INTO transaction_data VALUES (26, 'SOUTH', TO_DATE('2026-01-10','YYYY-MM-DD'), 780.00, 126);
INSERT INTO transaction_data VALUES (27, 'EAST', TO_DATE('2026-02-12','YYYY-MM-DD'), 890.00, 127);
INSERT INTO transaction_data VALUES (28, 'WEST', TO_DATE('2026-03-05','YYYY-MM-DD'), 1010.00, 128);
INSERT INTO transaction_data VALUES (29, 'NORTH', TO_DATE('2026-03-15','YYYY-MM-DD'), 1320.00, 129);
INSERT INTO transaction_data VALUES (30, 'SOUTH', TO_DATE('2026-03-28','YYYY-MM-DD'), 820.00, 130);

COMMIT;

END;

select * from transaction_data partition( P_PREVIOUS_FY)
order by TRANS_DATE;