Wednesday, December 4, 2019

Truncate (or drop!) partition without disturbing global non partitioned indexes

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 index
Let 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            UNUSABLE
Obviously, 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!

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign