The problem
Typical problem with partition tables is partition pruning and problems with global non partitioned index on them. From the theory, if you truncate partition when you have global non partitioned index, it will become INVALID and prevent normal functioning of the application. And when you have INVALID global non partitioned index, its rebuild may be very costly operation (especially on DWH systems), while in the same time if you do not use ONLINE option, rebuild put locks that also can prevent normal application execution against those table. However ONLINE option means put lock in very small moment of time, but you must have double the size of index in tablespace, what also can be sometimes impossible to achieved.But there is a workaround to prevent this.
The problem
Here is brief short demo that will explain all in practical case.I will create one range partition table with 5 partitions,where partition will be based on number column.
DROP TABLE PURGE_PART_DEMO;
CREATE TABLE PURGE_PART_DEMO (
A_NUMBER NUMBER ,
A_NAME VARCHAR2(32)
)
SEGMENT CREATION IMMEDIATE
NOCOMPRESS
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 8
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
PARTITION BY RANGE (A_NUMBER)
(
PARTITION A_NUMBER_1 VALUES LESS THAN (1)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 32M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION A_NUMBER_2 VALUES LESS THAN (2)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 32M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION A_NUMBER_3 VALUES LESS THAN (3)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 32M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION A_NUMBER_4 VALUES LESS THAN (4)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 32M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION A_NUMBER_5 VALUES LESS THAN (5)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 32M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION A_NUMBER_MAXVALUE VALUES LESS THAN (MAXVALUE)
LOGGING
NOCOMPRESS
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 32M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
)
PARALLEL 1;
Block occupation of empty table:
select bytes/1024/1024 MB, blocks, segment_name, partition_name from dba_segments s where segment_name='PURGE_PART_DEMO' ORDER BY PARTITION_NAME;
MB BLOCKS SEGMENT_NAME PARTITION_NAME
---------- ---------- --------------------------------------------------------------------------------- ------------------------------
1 128 PURGE_PART_DEMO A_NUMBER_1
1 128 PURGE_PART_DEMO A_NUMBER_2
1 128 PURGE_PART_DEMO A_NUMBER_3
1 128 PURGE_PART_DEMO A_NUMBER_4
1 128 PURGE_PART_DEMO A_NUMBER_5
1 128 PURGE_PART_DEMO A_NUMBER_MAXVALUE
6 rows selected.
Insert in each partition 30.0000 records:
begin
for i in 1..30000 loop
insert into PURGE_PART_DEMO (a_number,a_name) values (dbms_random.value (low=>0, high=>1) ,dbms_random.string(opt=>'u', len =>32)); --part A_NUMBER_1
insert into PURGE_PART_DEMO (a_number,a_name) values (dbms_random.value (low=>1, high=>2) ,dbms_random.string(opt=>'u', len =>32)); --part A_NUMBER_2
insert into PURGE_PART_DEMO (a_number,a_name) values (dbms_random.value (low=>2, high=>3) ,dbms_random.string(opt=>'u', len =>32)); --part A_NUMBER_3
insert into PURGE_PART_DEMO (a_number,a_name) values (dbms_random.value (low=>3, high=>4) ,dbms_random.string(opt=>'u', len =>32)); --part A_NUMBER_4
insert into PURGE_PART_DEMO (a_number,a_name) values (dbms_random.value (low=>4, high=>5) ,dbms_random.string(opt=>'u', len =>32)); --part A_NUMBER_5
insert into PURGE_PART_DEMO (a_number,a_name) values (dbms_random.value (low=>5, high=>99),dbms_random.string(opt=>'u', len =>32)); --part A_NUMBER_MAXVALUE
end loop;
commit;
end;
/
Checking again segment occupation:
select bytes/1024/1024 MB, blocks, segment_name, partition_name from dba_segments s where segment_name='PURGE_PART_DEMO' ORDER BY PARTITION_NAME;
MB BLOCKS SEGMENT_NAME PARTITION_NAME
---------- ---------- --------------------------------------------------------------------------------- ------------------------------
33 4224 PURGE_PART_DEMO A_NUMBER_1
33 4224 PURGE_PART_DEMO A_NUMBER_2
33 4224 PURGE_PART_DEMO A_NUMBER_3
33 4224 PURGE_PART_DEMO A_NUMBER_4
33 4224 PURGE_PART_DEMO A_NUMBER_5
33 4224 PURGE_PART_DEMO A_NUMBER_MAXVALUE
6 rows selected.
Make stat on table to have full data vie for partition:
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'Q383211'
,TabName => 'PURGE_PART_DEMO'
,Estimate_Percent => 100
,Block_sample => TRUE
,Method_Opt => 'FOR ALL COLUMNS SIZE 1'
,Degree => NULL
,Cascade => DBMS_STATS.AUTO_CASCADE
,No_Invalidate => DBMS_STATS.AUTO_INVALIDATE
,Force => FALSE);
END;
/
col HIGH_VALUE for A8
SELECT PARTITION_POSITION, PARTITION_NAME,
HIGH_VALUE, TABLESPACE_NAME, NUM_ROWS, LAST_ANALYZED
,AVG_SPACE, BLOCKS, EMPTY_BLOCKS
FROM sys.user_TAB_PARTITIONS
WHERE TABLE_NAME = 'PURGE_PART_DEMO'
ORDER BY PARTITION_POSITION;
PARTITION_POSITION PARTITION_NAME HIGH_VAL TABLESPACE_NAME NUM_ROWS LAST_ANA AVG_SPACE BLOCKS EMPTY_BLOCKS
------------------ ------------------------------ -------- ------------------------------ ---------- -------- ---------- ---------- ------------
1 A_NUMBER_1 1 USERS 30000 04.12.19 0 247 0
2 A_NUMBER_2 2 USERS 30000 04.12.19 0 252 0
3 A_NUMBER_3 3 USERS 30000 04.12.19 0 252 0
4 A_NUMBER_4 4 USERS 30000 04.12.19 0 252 0
5 A_NUMBER_5 5 USERS 30000 04.12.19 0 252 0
6 A_NUMBER_MAXVALUE MAXVALUE USERS 30000 04.12.19 0 252 0
6 rows selected.
Create 2 indexes, one loacl and one global non partitioned one:
CREATE INDEX PURGE_PART_DEMO_IX1 on PURGE_PART_DEMO (a_number) local; --partitioned one! CREATE INDEX PURGE_PART_DEMO_IX2 on PURGE_PART_DEMO (a_name) ; --global non partitioned indexLet me run stat again with histogram on columns with indexes (not needed for this case):
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => user
,TabName => 'PURGE_PART_DEMO'
,Estimate_Percent => 100
,Block_sample => TRUE
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE 254'
,Degree => NULL
,Cascade => DBMS_STATS.AUTO_CASCADE
,No_Invalidate => DBMS_STATS.AUTO_INVALIDATE
,Force => FALSE);
END;
/
Histogram looks like:
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM LAST_ANALYZED SAMPLE_SIZE DENSITY
------------ -------------------- ------------------------------ ------------ ----------- --------------- ------------------- ----------- ----------
Q383211 PURGE_PART_DEMO A_NAME* 180000 254 H BALANCED 04.12.2019 08:20:44 180000 5,5556E-06
A_NUMBER* 180000 254 H BALANCED 04.12.2019 08:20:44 180000 5,5556E-06
* indexed column
** uq indexed column
Check index status:
select index_name, status from all_indexes where owner=(SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual) and index_name in ('PURGE_PART_DEMO_IX1','PURGE_PART_DEMO_IX2');
INDEX_NAME STATUS
------------------------------ --------
PURGE_PART_DEMO_IX1 N/A --partition index ALWAYS VALID
PURGE_PART_DEMO_IX2 VALID --global non partitioned index (THIS ONE COULD MAKE A PROBLEMS)
Now let me show you ...ordinary truncate partition (a wrong way!!)
ALTER TABLE PURGE_PART_DEMO TRUNCATE PARTITION (A_NUMBER_3) drop storage;Let us check indexes:
INDEX_NAME STATUS ------------------------------ -------- PURGE_PART_DEMO_IX1 N/A PURGE_PART_DEMO_IX2 UNUSABLEObviously, global non partitioned one become invalid-expected Oracle behavior.
The solution
Now let us rebuild index so it is back to normal state for next presentation purpose.alter index PURGE_PART_DEMO_IX2 REBUILD; Index altered.Check indexes status again:
select index_name, status from all_indexes where owner=(SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual) and index_name in ('PURGE_PART_DEMO_IX1','PURGE_PART_DEMO_IX2');
INDEX_NAME STATUS
------------------------------ --------
PURGE_PART_DEMO_IX1 N/A
PURGE_PART_DEMO_IX2 VALID
And here is how we can solve this problem with a little different approach....can be fully avoiod...fully!
Idea is to remove all records in involved partitions (that we want to truncate) and then truncate that partition in a classic way.
This approach what will not put global non partitioned indexes in invalid state!
DELETE FROM PURGE_PART_DEMO partition (A_NUMBER_2) ; 30000 rows deleted. commit; Commit complete.Check indexes status again:
select index_name, status from all_indexes where owner=(SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual) and index_name in ('PURGE_PART_DEMO_IX1','PURGE_PART_DEMO_IX2');
INDEX_NAME STATUS
------------------------------ --------
PURGE_PART_DEMO_IX1 N/A
PURGE_PART_DEMO_IX2 VALID
And we should truncate now that partition, when there is no records...Oracle is smart enough to understand that should not touch global non partitioned index and it remains VALID!
ALTER TABLE PURGE_PART_DEMO TRUNCATE PARTITION (A_NUMBER_2) drop storage; Table truncated.Check indexes status again:
select index_name, status from all_indexes where owner=(SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual) and index_name in ('PURGE_PART_DEMO_IX1','PURGE_PART_DEMO_IX2');
INDEX_NAME STATUS
------------------------------ --------
PURGE_PART_DEMO_IX1 N/A
PURGE_PART_DEMO_IX2 VALID
Only important thing is to have INITIAL part of each partition to only 1 MB, so when you truncate it it will fall to minimum size.
select bytes/1024/1024 MB, blocks, segment_name, partition_name from dba_segments s where segment_name='PURGE_PART_DEMO' ORDER BY PARTITION_NAME;
MB BLOCKS SEGMENT_NAME PARTITION_NAME
---------- ---------- --------------------------------------------------------------------------------- ------------------------------
33 4224 PURGE_PART_DEMO A_NUMBER_1
1 128 PURGE_PART_DEMO A_NUMBER_2
1 128 PURGE_PART_DEMO A_NUMBER_3
33 4224 PURGE_PART_DEMO A_NUMBER_4
33 4224 PURGE_PART_DEMO A_NUMBER_5
33 4224 PURGE_PART_DEMO A_NUMBER_MAXVALUE
6 rows selected.
The final
It is very important that you put storage initial parameter of each partition to 1M, so you can really have smallest amount of space used when partition is truncated. In this way later, when you have time frame for full downtime, you can drop partitions that are previously truncated.But wait a minute, can we drop empty partition now and not disturb indexes? Let us try!
alter table PURGE_PART_DEMO drop partition A_NUMBER_2;
Table altered.
alter table PURGE_PART_DEMO drop partition A_NUMBER_3;
Table altered.
select bytes/1024/1024 MB, blocks, segment_name, partition_name from dba_segments s where segment_name='PURGE_PART_DEMO' ORDER BY PARTITION_NAME;
MB BLOCKS SEGMENT_NAME PARTITION_NAME
---------- ---------- --------------------------------------------------------------------------------- ------------------------------
33 4224 PURGE_PART_DEMO A_NUMBER_1
33 4224 PURGE_PART_DEMO A_NUMBER_4
33 4224 PURGE_PART_DEMO A_NUMBER_5
33 4224 PURGE_PART_DEMO A_NUMBER_MAXVALUE
select index_name, status from all_indexes where owner=(SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual) and index_name in ('PURGE_PART_DEMO_IX1','PURGE_PART_DEMO_IX2');
INDEX_NAME STATUS
------------------------------ --------
PURGE_PART_DEMO_IX1 N/A
PURGE_PART_DEMO_IX2 VALID
Seems yeeess!
All exposed here was successfully tested on Oracle >= 11.1 version and was tested against ranged partitioned and sub partitioned tables.
Hope this helps someone.
Cheers!
