Index fragmentation - use coalesce/shrink/rebuild wisely

Over time, indexes can potentially experience fragmentation due to large numbers of deletions. DBAs often wonder about the correct approach to handling the space usage by indexes, especially large ones. Oracle provides three ways to handle fragmentation within an index—rebuilding, coalescing, and shrinking an index— and each of them serves a different purpose.

Index Rebuild
You can rebuild an index either in offline mode(default) or in online mode.

Alter index rebuild online: 
During a online index rebuild, Oracle will make a snapshot log(an intermediate table) on the target table to hold DML activity, read the table in a full-table scan (read consistent), build the new index and then apply the changes from the snapshot log after the index has been rebuilt.You can create or rebuild indexes even when doing DML (INSERT/UPDATE/DELETE) statements on the base table. You can use NOLOGGING and PARALLEL with the online rebuild.Rebuilding an index online allows the database to use the index during its rebuild, thus enhancing availability.

Online Index Rebuild Features:

•  ALTER INDEX REBUILD ONLINE;
•  DMLs are allowed on the base table
•  It is comparatively Slow
•  Base table is referred for the new index
•  Base table is locked in shared mode and DDLs are not possible
•  Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Example
alter index my_idx rebuild online parallel 63 nologging; 
alter index my_idx noparallel;
alter index my_idx logging;

Alter index rebuild(offline):
During a "regular" index rebuild, an exclusive lock occurs as the existing index is read.  Hence, this command is designed for scheduled downtime periods where there is no DML activity.However, this operation can be parallelized and run in NOLOGGING mode.

Offline Index Rebuild Features:

•   ALTER INDEX REBUILD; (Default)
•   New index is created from the old index
•   No DML and DDL possible on the base table
•   Comparatively faster
•   Here base table is not referred while index is rebuild offline.
•   Does not refer the base table and the base table is exclusively  locked

Example
alter index my_idx rebuild parallel 63 nologging; 
alter index my_idx noparallel;
alter index my_idx logging;

Note:-  

Even for Online operation,a table lock is required on the index base table at the start of the CREATE or REBUILD process to guarantee DDL information. A lock at the end of the process is also required so as to merge changes into the final index structure.The time taken to complete the indexing process(rebuild or create) will increase as the indexing process will hang if there is an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required. However Oracle 11g has provided enormous improvements in the locking implications regarding creating or rebuilding indexes online. So such operation will no longer be locked and complete successfully if the indexing process has been locked out and subsequent transactions relating to the base table starts afterwards.In Oracle 11g the indexing process no longer effects other concurrent transactions on the base table.

++Even during index build you can use the CREATE INDEX ONLINE to create an index without placing an exclusive lock over the table. 
CREATE INDEX ONLINE statement can speed up the creation as it works even when reads or updates are happening on the table.

Coalesce and Shrink compact
Instead of rebuilding an index, you can shrink the space used by an index by coalescing or shrinking an index. 
Using the alter index ...shrink space compact command gets you the same results as when you execute the alter index...coalesce command. Both the shrink+ compact and coalesce commands are alternative ways to compact an index segment. Both operations achieve the same purpose and are in essence identical, but the shrink command offers more options.

*Coalescing an index doesn't release space back to the database.
The purpose of the coalesce command is to reduce fragmentation in an index. It doesn't deallocate space that has been allocated to an index segment.

*It combines adjacent leaf blocks into a single leaf block and puts the newly empty leaf blocks on the free list of the index segment.Following figure illustrate the effect of coalesce on an index.



*Coalescing an index keeps the space you allocated for the index intact; it doesn't return the unused space to the database

*Shrinking an index compacts the index segment and the database will immediately release any space that has been freed up.

*There are several problems with the analyze index ...validate structure command, including the fact that the command locks the table until the index is analyzed

*For a large index, the database may take quite a bit of time to complete the shrink operation. Therefore, Oracle lets you perform a shrink operation in two steps. If you specify the compact clause with the shrink space command, the database only performs a defragmentation of the segment space. It compacts the index but doesn't immediately release the free space. You must issue a separate alter index ...shrink space command to make the index release the free space. Since compacting an index segment may require the database to perform row movement, you must first enable row movement for a table before you specify the compact clause.

*If your goal is merely to defragment an index, coalesce the index instead of shrinking it;

*Coalescing has an advantage over shrinking an index because the database never locks the index during the index coalescing operation (coalesce is always an online operation), while it does lock the table briefly during a shrink operation to release the free space.

*Large indexes sometimes get fragmented over time and you may have a valid reason to reduce the fragmentation. Whether you employ the coalesce or rebuild operation depends on exactly how the index
is getting fragmented. If you have an index on a sequence, for example, and the rows are deleted from an older part of the index, then you may have a case where the deleted space does become deadwood in the sense that it can't be used by new index entries that are being inserted into the newer, rightmost part of the index. In a case such as this, where the deletions are all occurring from a small portion of the index, a rebuild is overkill in general; you're better off with a coalesce (or shrink operation). The coalesce operation will just go through the small portion of the index that's fragmented and leave the rest of the index alone; it takes far less time and resources to get the job down via coalescing the index in this case. However, if there are numerous deletions through the index, and not from a specific part of the index structure, you are better off rebuilding the index completely. Rebuilding is far more efficient because, unlike a coalesce operation, it has to perform only a single scan of the index structure in order to create a new structure. Since the deletions are large and are all over the index structure, coalescing an index is far less efficient, as it has to keep moving the same index blocks through multiple leaf blocks to defragment them.

*Unlike in the case of a table, you can't move an index by using a "move index" command. You move an index to a different tablespace by rebuilding the index.

SQL>alter index test_idx1 rebuild parallel 12 nologging tablespace dev_oim

Here is a demonstration- 

C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Mar 10 18:43:12 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn test/test
Connected.

First we test Coalesce-

SQL> DROP TABLE test;

Table dropped.

SQL> CREATE TABLE test (id NUMBER, name VARCHAR2(20));

Table created.

SQL> INSERT INTO test SELECT rownum, 'INDEX TEST' FROM dual CONNECT BY LEVEL <= 1000000;

1000000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX test_idx ON test(id);

Index created.

SQL>

SQL> col segment_name for a20

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';

SEGMENT_NAME                MEG
-------------------- ----------
TEST_IDX                     18

SQL>

Delete very small percentage of the table data-

SQL> DELETE test WHERE mod(id,2) = 1 and id <= 100000;

50000 rows deleted.

SQL>  COMMIT;

Commit complete.

SQL>  ANALYZE INDEX test_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2226          5       50000    17848160         90

The PCT_USED used 90% and we need to get rid off 50,000 deleted leaf rows.

SQL> col name for a25
SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB
------------------------- ----------
redo size                      61259

SQL>

Now coalesce the index-

SQL> ALTER INDEX test_idx COALESCE;

Index altered.

SQL>  SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 

size';

NAME                         REDO_KB
------------------------  ----------
redo size                      64039

SQL> select 64039-61259 from dual;

64039-61259
-----------
       2780

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2116          5           0    16968160         90

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';

SEGMENT_NAME                MEG
-------------------- ----------
TEST_IDX                     18

SQL>

After coalesce leaf blocks has been changed by 110 block and the overall size of the index unchanged.

Now SHRINK SPACE COMPACT-

SQL>  DROP TABLE test;

Table dropped.

SQL> CREATE TABLE test (id NUMBER, name VARCHAR2(20));

Table created.

SQL> INSERT INTO test SELECT rownum, 'INDEX TEST' FROM dual CONNECT BY LEVEL <= 1000000;

1000000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX test_idx ON test(id);

Index created.

SQL> DELETE test WHERE mod(id,2) = 1 and id <= 100000;

50000 rows deleted.

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2226          5       50000    17848160         90

SQL> col name for a25
SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB
------------------------- ----------
redo size                     125298

SQL> ALTER INDEX test_idx SHRINK SPACE COMPACT;

Index altered.

SQL>  SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB
------------------------- ----------
redo size                     129995

SQL>
SQL> select 129995-125298 from dual;

129995-125298
-------------
         4697

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2116          5           0    16968160         90

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';

SEGMENT_NAME                MEG
-------------------- ----------
TEST_IDX                     18

Index in the same state as after the Coalesce with leaf blocks down by 110 blocks but at nearly double the costs of the redo.

SHRINK SPACE

SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB
------------------------- ----------
redo size                     129996

SQL> ALTER INDEX test_idx SHRINK SPACE;

Index altered.

SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB
------------------------- ----------
redo size                     130030

SQL> select 130030 - 129996 from dual;

130030-129996
-------------
           34

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';

SEGMENT_NAME                MEG
-------------------- ----------
TEST_IDX                16.9375

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2168       2116          5           0    16968160         90

Block size also reduced by 2168(and thus index overall size) with an additional redo of just 34 KB.

Finally we Rebuild

SQL>  DROP TABLE test;

Table dropped.

SQL> CREATE TABLE test (id NUMBER, name VARCHAR2(20));

Table created.

SQL>  INSERT INTO test SELECT rownum, 'INDEX TEST' FROM dual CONNECT BY LEVEL <= 1000000;

1000000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX test_idx ON test(id);

Index created.

SQL> DELETE test WHERE mod(id,2) = 1 and id <= 100000;

50000 rows deleted.

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';

SEGMENT_NAME                MEG
-------------------- ----------
TEST_IDX                     18

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2304       2226          5       50000    17848160         90

SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB
------------------------- ----------
redo size                     191288

SQL> ALTER INDEX test_idx REBUILD;

Index altered.

SQL> SELECT n.name, round(s.value/1024) redo_kb FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = 'redo 
size';

NAME                         REDO_KB
------------------------- ----------
redo size                     208457

SQL> select 208457 - 191288 from dual;

208457-191288
------------
        17169

SQL> ANALYZE INDEX test_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space,pct_used FROM index_stats where name='TEST_IDX';

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS DEL_LF_ROWS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------- ----------
         3       2176       2116          5           0    16968160         90

SQL> select segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' and segment_name='TEST_IDX';

SEGMENT_NAME                MEG
-------------------- ----------
TEST_IDX                     17

SQL>

Index rebuild also reduced the leaf block by 110,at the same time it also reduced the number of block assigned to the index segment. But,the index rebuild operation generated much more redo than the other operations.

As discussed earlier,if there are numerous deletions through the index then rebuild is the right choice rather than coalesce/Shrink.Because if you tried to defragment a index which has been gone through a massive deletion using coalesce/shrink it will generate huge amount of redo to rearrange the leaf blocks than would have been generated by rebuild command. So if PCT_USED  falls below 25% it's always better to use REBUILD command to reallocate the unused space within the index.In that case rebuild will also helps to reduce the height of the index.

Reference:- 

http://www.dba-oracle.com/t_rebuild_index_online.htm

https://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/

Ebook Expert Indexing in Oracle Database 11g

Oracle nologging - does it generate redo?

Some SQL statements and operations support the use of a NOLOGGING clause. This does not mean that all operations against the object will be performed without generating redo log, just that some very specific operations will generate significantly less redo then normal.

All operations will generate some redo ‐ all data dictionary operations will be logged regardless of the logging mode.

Remember,It is not possible to roll forward through a point in time when an NOLOGGING operation has taken place. This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, or an NOLOGGING table load. Let's say you created a table using nologging, using CREATE TABLE AS SELECT NOLOGGING and that table is started using by the application now. Here if the disk that the table is on fails we can't do a media recovery even if our database is running in archivelog mode. The problem here is the table was created using nologging option and it was not logged, is not recoverable from the archived redo log. So after doing any nologging operation DBA has to take a full backup immediately.

Note:- 

NOLOGGING does not prevent redo from being generated by all subsequent operations. In the above example, I did not create a table that is never logged. Only the single, individual operation of creating the table was not logged. All subsequent ʹnormalʹ operations such as INSERTs, UPDATEs, and DELETEs will be logged. Other special operations such as a direct path load using SQLLDR, or a direct path insert using the INSERT /*+ APPEND */ syntax will not be logged.

After performing NOLOGGING operations in an ARCHIVELOG mode database, you must take a new baseline backup of the affected data files as soon as possible.

There are two ways to use the NOLOGGING option.
1.by embedding the keyword NOLOGGING in the SQL command itself at the appropriate location.

2.Second method allows operations to be performed implicitly in a
NOLOGGING mode. For example, I can alter a table to be NOLOGGING by default. This means that subsequent direct path loads and direct path inserts performed which affect this table, will not be logged.
Remember If you use the APPEND hint and place the table in nologging mode, redo will be bypassed. 

3.It is always recommenced to schedule any nologging operation(such as alter table...move partition + alter index...rebuild) to take place immediately before a backup occurs

The operations that may be performed in a NOLOGGING mode are:

DML:

Direct-path INSERT (serial or parallel) resulting either from an INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.

Direct Loader (SQL*Loader)

DDL:

CREATE TABLE … AS SELECT

CREATE TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS

ALTER TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS(to specify logging of newly created LOB columns)

ALTER TABLE … modify_LOB_storage_clause … modify_LOB_parameters … NOCACHE| CACHE READS (to change logging of existing LOB columns)

ALTER TABLE … MOVE

ALTER TABLE … (all partition operations that involve data movement)

ALTER TABLE … ADD PARTITION (hash partition only)

ALTER TABLE … MERGE PARTITIONS

ALTER TABLE … SPLIT PARTITION

ALTER TABLE … MOVE PARTITION

ALTER TABLE … MODIFY PARTITION … ADD SUBPARTITION

ALTER TABLE … MODIFY PARTITION … COALESCE SUBPARTITION

CREATE INDEX

ALTER INDEX … REBUILD

ALTER INDEX … REBUILD [SUB]PARTITION

ALTER INDEX … SPLIT PARTITION

local vs global index - a study note

LOcal partitioned Index

In a local partitioned index, the number of index partitions will match the number of partitions in the base table. Local indexes are partitioned indexes which are easy to manage and each index partition is associated with its respective table partitions. Local indexes are most suited for data warehousing or DSS applications.Local indexes are more flexible when it comes to partition maintenance operations..When we take any action(MERGE, SPLIT,EXCHANGE etc) on local partition, it impacts only that partition and other partition will be available.For example, If the DBA decides to move a table partition, only the associated local index partition needs to be rebuilt. With a global index all index partitions must be rebuilt.

The major advantage of local indexes is there will not be any index invalidation when you perform alter table exchange, drop, add partitions. When you perform above mention operations on the underlying table, the index remain valid.

Come to the table reorganization part, the local indexes gives the advantage of making individual partitions of a table and indexes offline for the maintenance. So it won’t affect the other partitions in the table or index. The maintenance would be easy for the local indexes.

The index partition gets deleted when the underlying table partition dropped. We can create bitmap on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. Bitmap indexes cannot be with Global indexes.

SQL> CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
      PARTITION BY RANGE (department_id)
      (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE USERS,
      PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE USERS,
      PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE USERS);

Table created.

SQL> col PARTITION_NAME for a33
SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME                      NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3

SQL> declare
 v_no number :=1;
 begin
 delete employees;
 for i in 1..10 loop
 insert into employees values(v_no,'name...',v_no);
 v_no := v_no+1;
 end loop;
end;
/  2    3    4    5    6    7    8    9   10

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>  select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME                      NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3

SQL> select * from EMPLOYEES;

EMPLOYEE_ID LAST_NAME  DEPARTMENT_ID
----------- ---------- -------------
          1 name...                1
          2 name...                2
          3 name...                3
          4 name...                4
          5 name...                5
          6 name...                6
          7 name...                7
          8 name...                8
          9 name...                9
         10 name...               10

10 rows selected.

SQL> create index idx_local on employees(last_name) local;

Index created.

SQL> select locality from user_part_indexes where index_name='IDX_LOCAL';

LOCALI
------
LOCAL

SQL> col index_name for a20
SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='IDX_LOCAL';

INDEX_NAME           INDEX_TYPE                  PAR GLO
-------------------- --------------------------- --- ---
IDX_LOCAL            NORMAL                      YES NO

SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL';

INDEX_NAME           PARTITION_NAME         STATUS
-------------------- ---------------------- --------
IDX_LOCAL            EMPLOYEES_PART1        USABLE
IDX_LOCAL            EMPLOYEES_PART2        USABLE
IDX_LOCAL            EMPLOYEES_PART3        USABLE
SQL>


SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME                      NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1                            9
EMPLOYEES_PART2                            1
EMPLOYEES_PART3                            0

SQL>

That is, When you create a local index on a partitioned table, it automatically creates index partitions as many as in the table partitions. Similarly when we add a new partition to the table, Oracle automatically adds one index partition to the index as well.

Note:- If you specify the partition information when building a local partitioned index, the number of partitions must match the number of partitions in the table on which the partitioned index is built.

In a local partitioned index, the number of index partitions will match the number of partitions in the base table.
This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.Droping a partition from base table will delete the corresponding index partition

SQL> ALTER TABLE EMPLOYEES drop partition EMPLOYEES_PART2;

Table altered.

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME                      NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1                            9
EMPLOYEES_PART3                            0

SQL>  select INDEX_NAME,PARTITION_NAME from DBA_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL';

INDEX_NAME                        PARTITION_NAME
--------------------------------- ---------------------------------
IDX_LOCAL                         EMPLOYEES_PART3
IDX_LOCAL                         EMPLOYEES_PART1

SQL>

You can't drop partition from a local partioned index.

SQL> alter index IDX_LOCAL drop partition EMPLOYEES_PART3;
alter index IDX_LOCAL drop partition EMPLOYEES_PART3
            *
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid
for local partitioned index
SQL>

Disadvantage:- we can't add partition manually to the local index.

Locally partitioned indexes can be created as prefixed or non-prefixed

Local prefixed indexes - When you create a prefixed locally-partitioned index, it means that the partitioning column(s) for the table are on the leading edge of the index For example, if a table is range partitioned on the TIMESTAMP column, a local prefixed index on that table would have TIMESTAMP as the first column in its column list.The example that we discussed above falls under local prefixed index.

Local non-prefixed indexes - If the partitioning column(s) of the table are not on the leading edge, it is regarded as a non-prefixed
index. In earlier versions of Oracle, having a local index as prefixed offered performance advantages over its non-prefixed counterpart. With later versions of Oracle, including version 11gR2, the advantages of creating local indexes as prefixed have diminished. However, if your database environment is an OLTP
system, it still benefits query performance to have local prefixed indexes over non-prefixed indexes, as the optimizer will potentially scan less index partitions in order to retrieve the data for a query. Also The index may or may not contain the partition key columns of the table. Local non-prefixed indexes does not support partition pruning,

For example, Assuming the INVOICES table is range partitioned on INVOICE_DATE, the following example is of a local non-prefixed index. The indexed column does not match the partition key.

CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL
 (PARTITION invoices_q1 TABLESPACE users,
  PARTITION invoices_q2 TABLESPACE users,
  PARTITION invoices_q3 TABLESPACE users,
  PARTITION invoices_q4 TABLESPACE users);

Global Index

Globally partitioned indexes essentially mean that the index has a different partitioning scheme and ispartitioned based on a different column or set of columns than the table. Global index partitions are mainly used for OLTP applications where fewer index probes are required than with local partitioned indexes.For  Global indexes the DBA has the choice to create the partitions as many as required. In Global partition scheme the index is harder to maintain since the index may span partitions in the base table.

You can create the following types of globally partitioned indexes:
• Range
• Hash

You can create a globally partitioned index on a partitioned table or a non-partitioned table

If we drop one of the index partition as part of reorganization the entire global index will be affected.

The following operations on a heap-organized table render a global index unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE

For range-based globally partitioned indexes, there must always be a maximum specified for the index, with a high value of MAXVALUE. 

Global indexes can be range partitioned using a different partition scheme from the underlying table. i.e. on a table (containing two columns id and start_date) that is hash partitioned by id, the global index on start_date can be range partitioned.

There are two types of global indexes.They are,

1. Global non-paritioned indexes (similar to the normal non-partitioned indexes)

2. Global partitioned indexes.

You can add partitions to the global partition index by splitting the MAXVALUE partition using “alter index drop partition” .You can drop a empty global index partition.

If you try to drop a non-empty global index partion will cause the next highest partition to be marked unusable. I.e. You cannot drop the highest partition in a global index.

Unlike locally partitioned indexes that can be defined as prefixed or non-prefixed, all globally
partitioned indexes must be created as prefixed; that is, the partitioning column must be on the leading
edge of the index.

Example of Global Non-partitioned index.
------------------------------------------

SQL> CREATE INDEX employees_global_idx ON employees(employee_id);

or --> CREATE INDEX employees_global_idx ON employees(employee_id) GLOBAL;

SQL> select locality from dba_part_indexes where index_name='EMPLOYEES_GLOBAL_IDX';

no rows selected

SQL>

No rows selected means,its a Global Non-partitioned index.

SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';

INDEX_NAME                        INDEX_TYPE                  GLO
--------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_IDX              NORMAL                      YES

SQL>

Lets drop this index and create a partioned global index

SQL> drop index EMPLOYEES_GLOBAL_IDX;

Index dropped.
SQL>

Here is an example for a range-based globally partitioned index,

SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
 GLOBAL PARTITION BY RANGE(employee_id)
  2    3   (PARTITION p1 VALUES LESS THAN(3),
  4   PARTITION p2 VALUES LESS THAN(6),
  5   PARTITION p3 VALUES LESS THAN(11),
  6   PARTITION p4 VALUES LESS THAN(20),
  7   PARTITION p5 VALUES LESS THAN(MAXVALUE));

Index created.

SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        INDEX_TYPE                  GLO
--------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX         NORMAL                      NO

SQL> select locality from dba_part_indexes where index_name='EMPLOYEES_GLOBAL_PART_IDX';

LOCALI
------
GLOBAL

SQL> select INDEX_NAME,PARTITION_NAME from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME
--------------------------------- ---------------------------------
EMPLOYEES_GLOBAL_PART_IDX         P1
EMPLOYEES_GLOBAL_PART_IDX         P2
EMPLOYEES_GLOBAL_PART_IDX         P3
EMPLOYEES_GLOBAL_PART_IDX         P4
EMPLOYEES_GLOBAL_PART_IDX         P5

SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL>  select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        INDEX_TYPE                  GLO
--------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX         NORMAL                      YES

SQL>

Lets remove a empty partition :- 

SQL> select count(*) from employees where employee_id between 12 and 20;

  COUNT(*)
----------
         0
SQL>  ALTER index employees_global_part_idx drop partition p4;

Index altered.

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                    STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX         P1                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P2                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P3                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P5                                USABLE

SQL>

Now we will drop the partition P3 and see status. When we drop this partition, it should invalidate the next highest partition. Here, P5 is next highest partition.

SQL> alter index employees_global_part_idx drop partition p3;

Index altered.

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                    STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX         P5                                UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX         P2                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P1                                USABLE

SQL>

SQL> alter index employees_global_part_idx rebuild;
alter index employees_global_part_idx rebuild
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL>  alter index employees_global_part_idx rebuild partition p5;

Index altered.

SQL>  select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                    STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX         P5                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P2                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P1                                USABLE

SQL>

if you drop a partition from the base table all index  partition become invalidated -

SQL> alter table EMPLOYEES drop partition EMPLOYEES_PART1;

Table altered.

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                    STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX         P5                                UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX         P2                                UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX         P1                                UNUSABLE

SQL>

SQL> alter index employees_global_part_idx rebuild partition p5;

Index altered.

SQL> alter index employees_global_part_idx rebuild partition p2;

Index altered.

SQL> alter index employees_global_part_idx rebuild partition p1;

Index altered.

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                    STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX         P1                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P2                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P5                                USABLE

SQL>
In case you're using a global partitioned index, you can specify the update global indexes clause to prevent the global index from being marked as unusable. The two big advantages in specifying
the update global indexes clause is that the index remains online and available during the maintenance operation and you don't have to rebuild it after the maintenance operation.

SQL>  alter table EMPLOYEES drop partition EMPLOYEES_PART2 update global indexes;

Table altered.

SQL> select partition_name, num_rows from user_tab_partitions  where table_name = 'EMPLOYEES' order by partition_position;

PARTITION_NAME                      NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART3                            0

SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                    STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX         P5                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P2                                USABLE
EMPLOYEES_GLOBAL_PART_IDX         P1                                USABLE

SQL>

The second type of globally partitioned that can be created is the hash partitioned index. This is typically done for performance reasons and keeps the index more evenly spread between the partitions.

example- 

SQL> CREATE INDEX employees_ih1
2 ON employees (department_id)
3 GLOBAL
4 PARTITION BY HASH(department_id) partitions 4;
Index created.


My database is in 12c so what will happen when you do drop/truncate partion along with update global indexes? 

Oracle 12c: Global Index maintenance is now asynchronous
--------------------------------------------------------
Maintaining global indexes were always a expensive task in Oracle, particularly in the context of partitioned tables, where dropping or truncating a table partition could cause a global index to become UNUSABLE/INVALID unless specified to update the indexes during the drop/truncate operation. However, updating the index entries during partition drop/truncate can eventually slow down the actual drop/truncate operation.

With Oracle 12c, a drop or truncate table partition (with update indexes clause) is optimized by deferring the maintenance of associated global indexes, while still leaving the indexes in VALID state. 

Prior to 12c, a drop/truncate table partition (with update indexes clause) would cause a synchronous maintenance for the associated global indexes and thereby would delay the actual drop/truncate operation. However, with 12c this global index maintenance operation is performed asynchronously which optimizes the drop or truncate partition operation.


SQL>  select INDEX_NAME,PARTITION_NAME,num_rows,orphaned_entries from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                        PARTITION_NAME                      NUM_ROWS ORP
--------------------------------- --------------------------------- ---------- ---
EMPLOYEES_GLOBAL_PART_IDX         P5                                         1 YES
EMPLOYEES_GLOBAL_PART_IDX         P2                                         0 YES
EMPLOYEES_GLOBAL_PART_IDX         P1                                         0 YES

SQL>

As we can observe, the global indexes are still in VALID/USABLE state. However, this time the new column ORPHANED_ENTRIES is showing a value YES. 

The actual index maintenance is performed at a later time, triggered by one of the following.

The SYS.PMO_DEFERRED_GIDX_MAINT_JOB job is scheduled to run at 02:00 every day.
The SYS.PMO_DEFERRED_GIDX_MAINT_JOB job can be run manually using the DBMS_SCHEDULER.RUN_JOB procedure.
Run the DBMS_PART.CLEANUP_GIDX procedure.
Run the ALTER INDEX REBUILD [PARTITION] command.
Run the ALTER INDEX [PARTITION] COALESCE CLEANUP command.

Now how oracle make sure that these index will return the correct result as it contains Orphaned entry? .Oracle internally maintains the list of all orphaned entries associated with a global index. We can view the orphaned entries by querying SYS.INDEX_ORPHANED_ENTRY$ or SYS.INDEX_ORPHANED_ENTRY_V$ views as shown below.

SQL>select * from SYS.INDEX_ORPHANED_ENTRY_V$;

When we query data (using index) from a truncated/dropped table partition, Oracle scans through this list of orphaned index entries to avoid/ignore querying data from the respective table partitions (which is/are truncated or dropped).

Oracle will periodically clean up the orphan index entries by means of a scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB which is scheduled to run at 2:00 AM every day as shown below.

SQL>select owner,job_name,program_name,next_run_date,state,enabled from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';

We can also clean up the orphan index entries on demand either by running the scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB or by calling the procedure DBMS_PART.CLEANUP_GIDX as shown below.

SQL> exec dbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB');

PL/SQL procedure successfully completed.

SQL>  select INDEX_NAME,PARTITION_NAME,num_rows,orphaned_entries from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                 PARTITION_NAME                      NUM_ROWS ORP
-------------------------- --------------------------------- ---------- ---
EMPLOYEES_GLOBAL_PART_IDX  P5                                         1 NO
EMPLOYEES_GLOBAL_PART_IDX  P2                                         0 NO
EMPLOYEES_GLOBAL_PART_IDX  P1                                         0 NO

SQL>

Reference:- 
http://www.oraclebuffer.com/oracle/oracle-12c-global-index-maintenance-is-now-asynchronous/

http://myorastuff.blogspot.com/2008/08/local-index-versus-global-index-on.html