20 March, 2019

Partitioning -- 13d : TRUNCATE and DROP Partitions and Global Indexes

A TRUNCATE or DROP Partition makes Global Indexes on a Partitioned Table UNUSABLE.

You may be lucky if the target partition was empty, resulting in Oracle maintaining Global Indexes as valid.  However, the accepted rule is that you either (a) use the UPDATE INDEXES clause [resulting in the TRUNCATE or DROP taking longer to run, effectively locking the table partitions] OR  (b) do a REBUILD of the Indexes that become UNUSABLE after the TRUNCATE or DROP.

12c has introduced what it calls Asynchronous Global Index Maintenance.  With this feature present, the TRUNCATE or DROP runs much faster as a DDL without actually removing the target rows from the Global Indexes [but still requires the UPDATE INDEXES clause to be specified]

So, now in my 12.2 database I have these two Indexes on SALES_DATA :

SQL> select index_name, partitioned, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
SALES_DATA_PK                  NO  VALID
SALES_DATA_LCL_NDX_1           YES N/A

SQL> 


I then TRUNCATE a non-empty Partition and check the Indexes

SQL> alter table sales_data truncate partition P_2015 update indexes;

Table truncated.

SQL>
SQL> select index_name, partitioned, status, orphaned_entries
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS   ORP
------------------------------ --- -------- ---
SALES_DATA_PK                  NO  VALID    YES
SALES_DATA_LCL_NDX_1           YES N/A      NO

SQL> 


The ORPHANED_ENTRIES column indicates that SALES_DATA_PK is subject to Asynchronous Index Maintenance.

This is the job that will do the Index Maintenance at 2am  :

SQL> l
  1  select owner, job_name, last_start_date, next_run_Date
  2  from dba_scheduler_jobs
  3* where job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB'
SQL> /

OWNER
---------------------------------------------------------------------------
JOB_NAME
---------------------------------------------------------------------------
LAST_START_DATE
---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SYS
PMO_DEFERRED_GIDX_MAINT_JOB
20-MAR-19 10.18.51.215433 AM UTC
21-MAR-19 02.00.00.223589 AM UTC


SQL> !date
Wed Mar 20 20:05:24 SGT 2019

SQL> 


So, I could
(1) wait for the next run of the job OR
(2) manually trigger the job (which will scan the entire database for all indexes that require such maintenance) OR
(3) Execute  DBMS_PART.CLEANUP_GIDX  to initiate the maintenance for the specific index OR
(4) Execute an ALTER INDEX REBUILD to make the Index USABLE again.

SQL> execute dbms_part.cleanup_gidx('HEMANT','SALES_DATA');

PL/SQL procedure successfully completed.

SQL> select index_name, partitioned, status, orphaned_entries
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS   ORP
------------------------------ --- -------- ---
SALES_DATA_PK                  NO  VALID    NO
SALES_DATA_LCL_NDX_1           YES N/A      NO

SQL> 


Note that the argument to CLEANUP_GIDX is the *Table Name*, not an Index Name.


Here I have demonstrated a TRUNCATE Partition, but the same method would be usable for a DROP Partition.




12 March, 2019

Partitioning -- 13c : Merging Partitions

The reverse of SPLITting a Partition is to MERGE two adjacent partitions.

I reverse the SPLIT that I did in the previous blog post.

SQL> l
  1  select partition_name, tablespace_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4* order by partition_position
SQL> /

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016                         ARCHIVE_SALES_DATA             TO_DATE(' 2017-01-01 00:00
P_2017                         TBS_YEAR_2017                  TO_DATE(' 2018-01-01 00:00
P_2018                         TBS_YEAR_2018                  TO_DATE(' 2019-01-01 00:00
P_2019_H1                      TBS_YEAR_2019                  TO_DATE(' 2019-07-01 00:00
P_2019_H2                      TBS_YEAR_2019                  TO_DATE(' 2020-01-01 00:00
P_2020                         TBS_YEAR_2020                  TO_DATE(' 2021-01-01 00:00
P_MAXVALUE                     USERS                          MAXVALUE

7 rows selected.

SQL> 
SQL> alter table sales_data                                                  
  2  merge partitions P_2019_H1, P_2019_H2
  3  into partition P_2019
  4  update indexes
  5  /

Table altered.

SQL> 
SQL> select partition_name, tablespace_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016                         ARCHIVE_SALES_DATA             TO_DATE(' 2017-01-01 00:00
P_2017                         TBS_YEAR_2017                  TO_DATE(' 2018-01-01 00:00
P_2018                         TBS_YEAR_2018                  TO_DATE(' 2019-01-01 00:00
P_2019                         HEMANT                         TO_DATE(' 2020-01-01 00:00
P_2020                         TBS_YEAR_2020                  TO_DATE(' 2021-01-01 00:00
P_MAXVALUE                     USERS                          MAXVALUE

6 rows selected.

SQL> 


But, we find that the new Partition was created in the default "HEMANT"  tablespace !  So, we have to be careful about specifying target tablespace(s).

Let me reverse the action and try again.

SQL> alter table sales_data
  2  split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
  3  into (partition P_2019_H1 tablespace TBS_YEAR_2019, partition P_2019_H2 tablespace TBS_YEAR_2019)
  4  update indexes
  5  /

Table altered.

SQL> 
SQL> select partition_name, tablespace_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016                         ARCHIVE_SALES_DATA             TO_DATE(' 2017-01-01 00:00
P_2017                         TBS_YEAR_2017                  TO_DATE(' 2018-01-01 00:00
P_2018                         TBS_YEAR_2018                  TO_DATE(' 2019-01-01 00:00
P_2019_H1                      TBS_YEAR_2019                  TO_DATE(' 2019-07-01 00:00
P_2019_H2                      TBS_YEAR_2019                  TO_DATE(' 2020-01-01 00:00
P_2020                         TBS_YEAR_2020                  TO_DATE(' 2021-01-01 00:00
P_MAXVALUE                     USERS                          MAXVALUE

7 rows selected.

SQL> alter table sales_data
  2  merge partitions P_2019_H1, P_2019_H2
  3  into partition P_2019 tablespace TBS_YEAR_2019
  4  update indexes
  5  /

Table altered.

SQL> select partition_name, tablespace_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016                         ARCHIVE_SALES_DATA             TO_DATE(' 2017-01-01 00:00
P_2017                         TBS_YEAR_2017                  TO_DATE(' 2018-01-01 00:00
P_2018                         TBS_YEAR_2018                  TO_DATE(' 2019-01-01 00:00
P_2019                         TBS_YEAR_2019                  TO_DATE(' 2020-01-01 00:00
P_2020                         TBS_YEAR_2020                  TO_DATE(' 2021-01-01 00:00
P_MAXVALUE                     USERS                          MAXVALUE

6 rows selected.

SQL> 


So, when running Table Partition DDL, be careful about the intended and actual target Tablespace(s).  (What about Index Partitions ?  The UPDATE INDEXES clause can specify target tablespaces for each Index Partition of each Index as well ... something like : (this is from the documentation on the ALTER TABLE command)
UPDATE INDEXES (cost_ix (PARTITION c_p1 tablespace tbs_02, 
                           PARTITION c_p2 tablespace tbs_03))

13 January, 2019

Partitioning -- 13b : Splitting a Partition

Let's say the business anticipates growing sales volume in 2019 and new reporting requirements.  IT analyses the requirements and decides that the SALES_DATA Table that is currently Partitioned by YEAR, needs to be Partitioned by HALF-YEAR from 2019 onwards.

SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2016                         ARCHIVE_SALES_DATA
P_2017                         TBS_YEAR_2017
P_2018                         TBS_YEAR_2018
P_2019                         TBS_YEAR_2019
P_2020                         TBS_YEAR_2020
P_MAXVALUE                     USERS

6 rows selected.

SQL> 
SQL> alter table sales_data
  2  split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
  3  into (partition P_2019_H1, partition P_2019_H2)
  4  update indexes
  5  /

Table altered.

SQL> 
SQL> col high_value format a26 trunc
SQL> select partition_name, tablespace_name, high_value
  2  from user_tab_partitions
  3  where table_name  = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016                         ARCHIVE_SALES_DATA             TO_DATE(' 2017-01-01 00:00
P_2017                         TBS_YEAR_2017                  TO_DATE(' 2018-01-01 00:00
P_2018                         TBS_YEAR_2018                  TO_DATE(' 2019-01-01 00:00
P_2019_H1                      TBS_YEAR_2019                  TO_DATE(' 2019-07-01 00:00
P_2019_H2                      TBS_YEAR_2019                  TO_DATE(' 2020-01-01 00:00
P_2020                         TBS_YEAR_2020                  TO_DATE(' 2021-01-01 00:00
P_MAXVALUE                     USERS                          MAXVALUE

7 rows selected.

SQL> 


I used the UPDATE INDEXES clause to ensure that all (specifically Global) Indexes affected by the SPLIT are updated so that they don't go into an UNUSABLE state.

I could have optionally used a TABLESPACE clause for each of the two new Partitions P_2019_H1 and P_2019_H2


(Also see a previous BlogPost on using SPLIT PARTITION to add a new Partition at the "end" of the table by splitting the last Partition)



16 December, 2018

Partioning -- 13a : Relocating a Partition

When you want to / need to move a Partition to a different Tablespace (e.g. as part of a LifeCycle Management Policy), you may need downtime to relocate the Partition.  However, version 12cRelease1 allows Online Relocation of a Partition.

Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :

SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2016                         TBS_YEAR_2016
P_2017                         TBS_YEAR_2017
P_2018                         TBS_YEAR_2018
P_2019                         TBS_YEAR_2019
P_2020                         TBS_YEAR_2020
P_MAXVALUE                     USERS

6 rows selected.

SQL> 
SQL> select index_name, tablespace_name, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  /

INDEX_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
SYS_C0017514                   HEMANT                         VALID
SALES_DATA_LCL_NDX_1                                          N/A
SALES_DATA_LCL_NDX_2                                          N/A

SQL>
SQL> l
  1  select index_name, partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name in
  4  (select index_name
  5   from user_indexes
  6   where table_name = 'SALES_DATA')
  7* order by index_name, partition_position
SQL> /

INDEX_NAME                     PARTITION_NA TABLESPACE_NAM STATUS
------------------------------ ------------ -------------- --------
SALES_DATA_LCL_NDX_1           P_2016       TBS_YEAR_2016  USABLE
SALES_DATA_LCL_NDX_1           P_2017       TBS_YEAR_2017  USABLE
SALES_DATA_LCL_NDX_1           P_2018       TBS_YEAR_2018  USABLE
SALES_DATA_LCL_NDX_1           P_2019       TBS_YEAR_2019  USABLE
SALES_DATA_LCL_NDX_1           P_2020       TBS_YEAR_2020  USABLE
SALES_DATA_LCL_NDX_1           P_MAXVALUE   USERS          USABLE
SALES_DATA_LCL_NDX_2           P_2016       TBS_YEAR_2016  USABLE
SALES_DATA_LCL_NDX_2           P_2017       TBS_YEAR_2017  USABLE
SALES_DATA_LCL_NDX_2           P_2018       TBS_YEAR_2018  USABLE
SALES_DATA_LCL_NDX_2           P_2019       TBS_YEAR_2019  USABLE
SALES_DATA_LCL_NDX_2           P_2020       TBS_YEAR_2020  USABLE
SALES_DATA_LCL_NDX_2           P_MAXVALUE   USERS          USABLE

12 rows selected.

SQL> 


I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :

SQL> alter table SALES_DATA
  2  move partition P_2016
  3  tablespace ARCHIVE_SALES_DATA
  4  /

Table altered.

SQL> 
SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NA TABLESPACE_NAME
------------ ------------------
P_2016       ARCHIVE_SALES_DATA
P_2017       TBS_YEAR_2017
P_2018       TBS_YEAR_2018
P_2019       TBS_YEAR_2019
P_2020       TBS_YEAR_2020
P_MAXVALUE    USERS

6 rows selected.

SQL> 
SQL> select index_name, tablespace_name, partitioned, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by index_name
  5  /

INDEX_NAME                     TABLESPACE_NAME    PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1                              YES N/A
SALES_DATA_LCL_NDX_2                              YES N/A
SYS_C0017514                   HEMANT             NO  UNUSABLE

SQL> alter index SYS_C0017514 rebuild ;   

Index altered.

SQL> select index_name, tablespace_name, partitioned, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by index_name
  5  /

INDEX_NAME                     TABLESPACE_NAME    PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1                              YES N/A
SALES_DATA_LCL_NDX_2                              YES N/A
SYS_C0017514                   HEMANT             NO  VALID

SQL> 
SQL> l
  1  select index_name, partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name in
  4  (select index_name
  5  from user_indexes
  6  where table_name = 'SALES_DATA')
  7* order by index_name, partition_position
SQL> /

INDEX_NAME                     PARTITION_NA TABLESPACE_NAME    STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1           P_2016       TBS_YEAR_2016      UNUSABLE
SALES_DATA_LCL_NDX_1           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_1           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_1           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_1           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_1           P_MAXVALUE   USERS              USABLE
SALES_DATA_LCL_NDX_2           P_2016       TBS_YEAR_2016      UNUSABLE
SALES_DATA_LCL_NDX_2           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_2           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_2           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_2           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_2           P_MAXVALUE   USERS              USABLE

12 rows selected.

SQL> 
SQL> alter index SALES_DATA_LCL_NDX_1
  2  rebuild partition P_2016
  3  tablespace ARCHIVE_SALES_DATA
  4  /

Index altered.

SQL> 
SQL> alter index SALES_DATA_LCL_NDX_2
  2  rebuild partition P_2016
  3  tablespace ARCHIVE_SALES_DATA
  4  /

Index altered.

SQL> 
SQL> l
  1  select index_name, partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name in
  4  (select index_name
  5  from user_indexes
  6  where table_name = 'SALES_DATA')
  7* order by index_name, partition_position
SQL> /

INDEX_NAME                     PARTITION_NA TABLESPACE_NAME    STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1           P_2016       ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_1           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_1           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_1           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_1           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_1           P_MAXVALUE   USERS              USABLE
SALES_DATA_LCL_NDX_2           P_2016       ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_2           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_2           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_2           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_2           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_2           P_MAXVALUE   USERS              USABLE

12 rows selected.

SQL> 


When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed.  So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE".  These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.

Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.

I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.


Preview Popular Posts