Condition based truncate partition [message #689423] |
Fri, 22 December 2023 04:03 |
|
ace_friends22
Messages: 9 Registered: December 2023
|
Junior Member |
|
|
Hi team,
How can we write SQL statement to truncate partition based on condition.
For example, I have a table, partitioned based on 2 columns. BRAND & YEAR.
If today is 1st JUNE or greater then truncate current year partition and given BRAND
If today is less than 1st JUNE, then truncate current year and previous year partition and Given BRAND
|
|
|
|
Re: Condition based truncate partition [message #689425 is a reply to message #689423] |
Fri, 22 December 2023 09:58 |
Solomon Yakobson
Messages: 3301 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You didn't tell if it is range/list and regular or interval/automatic but you'll have to use dynami SQL regardless. For example:
SQL> DROP TABLE TBL PURGE
2 /
Table dropped.
SQL> CREATE TABLE TBL(
2 BRAND VARCHAR2(10),
3 YEAR NUMBER
4 )
5 PARTITION BY LIST(BRAND,YEAR)
6 (
7 PARTITION BRAND1_2022 VALUES(('BRAND1',2022)),
8 PARTITION BRAND2_2022 VALUES(('BRAND2',2022)),
9 PARTITION BRAND1_2023 VALUES(('BRAND1',2023)),
10 PARTITION BRAND2_2023 VALUES(('BRAND2',2023))
11 )
12 /
Table created.
SQL> INSERT INTO TBL VALUES('BRAND1',2022)
2 /
1 row created.
SQL> INSERT INTO TBL VALUES('BRAND2',2022)
2 /
1 row created.
SQL> INSERT INTO TBL VALUES('BRAND1',2023)
2 /
1 row created.
SQL> INSERT INTO TBL VALUES('BRAND2',2023)
2 /
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> SELECT * FROM TBL PARTITION FOR('BRAND1',2023)
2 /
BRAND YEAR
---------- ----------
BRAND1 2023
SQL> EXEC EXECUTE IMMEDIATE 'ALTER TABLE TBL TRUNCATE PARTITION FOR(''BRAND1'',' || TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-5),'YYYY')) || ')'
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TBL PARTITION FOR('BRAND1',2023)
2 /
no rows selected
SQL>
SY.
|
|
|
|