Skip to Main Content
  • Questions

  • Drop a partition of reference partitioned tables with the UPDATE INDEXES clause

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Brian.

Asked: November 28, 2016 - 6:43 pm UTC

Last updated: November 30, 2016 - 1:45 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

I have a set of 3 tables that are reference partitioned (a parent and two child tables). The parent table is range partitioned by date (TST_ADM_DTE) and contains 1,152 partitions, ranging in value from 3/1/14 to 12/31/16. The first seven (7) partitions in the table are monthly partitions, all the remaining partitions are daily partitioned. I am going to delete the partitions that contain 2014 data. I have 2 questions:

I was going to use one of the following commands against the parent table.

ALTER TABLE IBTRES.APNTMT DROP PARTITION Y14M08D02
UPDATE GLOBAL INDEXES PARALLEL 4;

or

ALTER TABLE IBTRES.APNTMT DROP PARTITION Y14M08D02
UPDATE INDEXES PARALLEL 4;

Does it make a difference which one I use if I am doing a DROP PARTITION operation?

The second question is how much overhead is incurred to keep the indexes USABLE? I am guessing that for partitioned
indexes, the appropriate partition is dropped. What is done for the global indexes?

I found the following statement but am not sure how to interpret it.

You can update indexes on table during this operation using the update_index_clauses.
Updates to global indexes are metadata-only and the index entries for records that are
dropped by the drop operation will continue to be physically stored in the index. You
can remove these orphaned index entries by specifying COALESCE CLEANUP in the
ALTER INDEX statement or in the modify_index_partition clause.

Thanks.

and Connor said...

Unfortunately the asynchronous index maintenance is a 12c feature. On 11g, updating the indexes (global or otherwise) is more or less equivalent to a large delete, ie, you drop the table partition and the index entries are "manually" deleted by the database.

So expect a large resource impact on redo and undo.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

2nd Question Answered

Brian Blades, November 29, 2016 - 1:19 pm UTC

Connor, thanks for the information. That answers the 2nd question about resource consumption. What about the 1st question? Are the two versions of the DROP partition command (UPDATE INDEXES and UPDATE GLOBAL INDEXES) equivalent.
Connor McDonald
November 30, 2016 - 1:45 am UTC

For DROP and TRUNCATE, no difference because the only things need to be updated are global/global partitioned indexes. But for other operations, there is a difference


SQL> create table t (x int, y int, z int )
  2  partition by range (x)
  3  (
  4    partition p1 values less than (4000),
  5    partition p2 values less than (8000)
  6  );

Table created.

SQL>
SQL> insert into t select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level <= 7 );

604877 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> create index ix_local on t ( y ) local;

Index created.

SQL> create index ix_global on t ( z ) ;

Index created.

SQL>
SQL> alter table t split partition p2 at (6000)
  2    into ( partition p2a, partition p2b )
  3    update global indexes;

Table altered.

SQL>
SQL> select index_name, partition_name, status
  2  from user_ind_partitions
  3  where index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IX_LOCAL                       P1                             USABLE
IX_LOCAL                       P2A                            UNUSABLE
IX_LOCAL                       P2B                            UNUSABLE

3 rows selected.

SQL>
SQL> select index_name, status
  2  from user_indexes
  3  where index_name like 'IX%';

INDEX_NAME                     STATUS
------------------------------ --------
IX_GLOBAL                      VALID
IX_LOCAL                       N/A

2 rows selected.

SQL>
SQL> alter index ix_local rebuild partition P2A;

Index altered.

SQL> alter index ix_local rebuild partition P2B;

Index altered.

SQL>
SQL> alter table t split partition p1 at (2000)
  2    into ( partition p1a, partition p1b )
  3    update indexes;

Table altered.

SQL>
SQL> select index_name, partition_name, status
  2  from user_ind_partitions
  3  where index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IX_LOCAL                       P1A                            USABLE
IX_LOCAL                       P1B                            USABLE
IX_LOCAL                       P2A                            USABLE
IX_LOCAL                       P2B                            USABLE

4 rows selected.

SQL>
SQL> select index_name,  status
  2  from user_indexes
  3  where index_name like 'IX%';

INDEX_NAME                     STATUS
------------------------------ --------
IX_LOCAL                       N/A
IX_GLOBAL                      VALID

2 rows selected.

SQL>
SQL> alter table t drop partition p1a update indexes;

Table altered.

SQL>
SQL> select index_name, partition_name, status
  2  from user_ind_partitions
  3  where index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IX_LOCAL                       P1B                            USABLE
IX_LOCAL                       P2A                            USABLE
IX_LOCAL                       P2B                            USABLE

3 rows selected.

SQL>
SQL> select index_name,  status
  2  from user_indexes
  3  where index_name like 'IX%';

INDEX_NAME                     STATUS
------------------------------ --------
IX_LOCAL                       N/A
IX_GLOBAL                      VALID

2 rows selected.

SQL>
SQL> alter table t drop partition p2a update global indexes;

Table altered.

SQL>
SQL> select index_name, partition_name, status
  2  from user_ind_partitions
  3  where index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IX_LOCAL                       P1B                            USABLE
IX_LOCAL                       P2B                            USABLE

2 rows selected.

SQL>
SQL> select index_name,  status
  2  from user_indexes
  3  where index_name like 'IX%';

INDEX_NAME                     STATUS
------------------------------ --------
IX_LOCAL                       N/A
IX_GLOBAL                      VALID

2 rows selected.

SQL>
SQL>


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.