Managing Index Space Usage

The summary of Oracle's advice was to rebuild any index that met the following two criteria: The index depth is more than 4 levels. The deleted index entries are at least 20% of the total current index entries. 

commands

analyze index <index name> validate structure;

alter index index_name rebuild

alter index index_name rebuild online

Rebuilding to Reduce Fragmentation

Rebuilding an index recreates an existing index.

You can rebuild an entire index, a partition, or a subpartition of a partitioned index without having to recreate the entire index.

In order to rebuild an index, use the alter index statement in the following way: SQL> alter index test_idx1 rebuild;

Index altered.

SQL> This statement makes the test_idx1 index unavailable for use until the rebuild operation completes.

 You can optionally rebuild an index online, as shown here: SQL> alter index test_idx1 rebuild online;

Index altered.

SQL> Rebuilding an index online allows the database to use the index during its rebuild, thus enhancing availability.

an online rebuild doesn't lead to any locking of the underlying table when you rebuild an index.

Rebuilding Reverse-Key Indexes When you rebuild an index with the reverse option, the database excludes the ROWID and stores the bytes of the index blocks in reverse order. For example, SQL> alter index test_idx1 rebuild reverse;

Index altered.

reverse key indexes are beneficial in some specific circumstances, especially in Oracle RAC environments, but have the drawback of not enabling the use of index range scans.

Reclaiming Unused Space

 DBAs sometimes rebuild indexes in order to reclaim unused space.

You can actually deallocate space from an index by executing the alter index …deallocate statement. For example, SQL> alter index test_idx1 deallocate unused;

Index altered.

SQL> When you deallocate space like this, the Oracle database deallocates unused space at the end of an index (or table) segment. Unused space within the segment caused by entries that have been deleted or moved is not released. Only space at the end of the segment is released.

Rebuilding a Partitioned Index

Several maintenance operations on tables mark any corresponding indexes or index partitions as invalid. If a local index partition is marked invalid, you must rebuild just the associated local index partition. A global index is invalidated if the rows of data in a partition are affected by DDL on that partition. Unlike in the case of a local index, you must rebuild all index partitions of a global index following a partition maintenance operation such as moving a table partition. Rebuilding Global Partitioned Indexes When dealing with global index partitions, the best strategy probably is to drop the index and recreate it because the database needs to scan the table just once when you do this. The other alternative is to individually rebuild the global indexes partitions by issuing the alter index … rebuild partition statement. Since you can rebuild multiple partitions simultaneously in parallel, this may not take as much time as it would if you perform the operation serially. Rebuilding Local Partitioned Indexes You can rebuild a local index by issuing either the alter table or alter index statement. If you want to rebuild an index regardless of whether the index is marked unusable or not, use the alter index … rebuild partition statement. This statement rebuilds a single partition or a subpartition. You can't use the alter index…rebuild statement to rebuild a composite-partitioned table. You must instead use the alter index …rebuild subpartition statement for any composite-partitioned tables. Here's an example: SQL> alter index test1
     rebuild subpartition prod_types
     tablespace tbs2 parallel (degree 8);

Use the alter table statement if you want to rebuild only those indexed partitions and subpartitions that have been marked unusable.

Here's the general syntax for the alter table statement to rebuild just the unusable partitions or subpartitions:

alter table … modify partition/subpartition … rebuild unusable local indexes This alter table syntax will rebuild all unusable indexes in a table partition or subpartition.

Specifying the UPDATE INDEXES Clause You can specify the update indexes clause with any of the alter partition statements during a partition maintenance operation, so the database can update the index while it's performing the maintenance operation on the partitions. This means that you avoid having to rebuild indexes following any maintenance operations on partitioned tables with indexes. You can specify the update indexes clause for most maintenance operations on partitioned tables. The following partition maintenance operations mark all global indexes as unusable: Add (for hash partitions) or drop partitions Coalesce (for hash partitions), merge, move partitions Split partitions Truncate partitions You can specify the update indexes clause with any of the preceding partition maintenance operations. 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.

You specify the update global indexes clause in order to automatically maintain a global index during a DDL operation. You can specify the update global indexes clause only for adding a partition to a hash partitioned table or a subpartition to a hash partitioned table. You can't specify it for adding partitions to a range partitioned table. Here's an example that shows how to specify the clause when performing a table maintenance operation on a hash partitioned table:

SQL> create table emp_hpart(
  2   empno number(4) not null,
  3   ename varchar2(10),
  4   sal number(7,2))
  5   partition by hash(sal)
  6*  (partition H1, partition H2, partition H3, partition H4)
SQL> /

Table created.

SQL> create index emp_global_HASH_idx on emp_hpart(ename)
  2  global partition by range (ename)
  3  (partition p1  values less than ('N') ,
  4* partition p2 values less than (maxvalue))
SQL> /

Index created.

SQL> insert into emp_hpart values (1,'AAA',100);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table emp_hpart add partition q5
  2* update global indexes
SQL> /

Table altered.

SQL>

SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
  2  part_name , status
  3  from dba_ind_partitions
  4* where index_name= 'EMP_GLOBAL_HASH_IDX' order by partition_name
SQL> /

INDEX_NAME               PART_NAME          STATUS
---------------------    -------------      --------
EMP_GLOBAL_HASH_IDX      P1                 USABLE

EMP_GLOBAL_HASH_IDX      P2                 USABLE

SQL> Notice that if you add a partition to the table, the indexes become unusable if you don't specify the update global indexes clause in your add partition statement. SQL> alter table emp_hpart add partition q7;

Table altered.

SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
  2  part_name , status
  3  from dba_ind_partitions
  4* where index_name= 'EMP_GLOBAL_HASH_IDX' order by partition_name
SQL> /

INDEX_NAME                PART_NAME             STATUS
--------------------      -----------          -----------
EMP_GLOBAL_HASH_IDX       P1                   UNUSABLE
EMP_GLOBAL_HASH_IDX       P2                   UNUSABLE

SQL> Rebuilding Indexes Frequently Oracle itself has changed its stand on the advisability of rebuilding indexes.

Until recently, Oracle Support used to offer a standard script to identify candidates for an index rebuild.

The script included an analyze index ...validate structure statement to populate the INDEX_STATS view.

Once the indexes are analyzed, you use two simple criteria to identify the indexes that could potentially benefit from a rebuild.

The summary of Oracle's advice was to rebuild any index that met the following two criteria: The index depth is more than 4 levels. The deleted index entries are at least 20% of the total current index entries. 

Note The database implements an index update internally by performing a delete first and then an insert. Many practitioners still rely on these outmoded and wrong rebuild criteria to determine whether they should rebuild their indexes. The latest Oracle documentation continues to recommend regular rebuilding of indexes. It urges you to “develop a history of average efficiency of index usage” by frequently running the validate index …analyze structure command and rebuilding indexes based on the results of the analyze command. It further recommends you to rebuild or coalesce an index “when you find that index space usage drops below its average.” These recommendations are definitely at odds with those currently offered on the Oracle support site (MOSC). On that site, Oracle no longer advises frequent index rebuilds, notwithstanding the latest version of Oracle's documentation (Performance Tuning Manual for Oracle Database 11.2) which still contains the older advice to run the analyze ... validate statement to identify index rebuild candidates. The following sections examine in detail the INDEX_STATS view and the analyze index…validate structure command that are at the heart of the whole rebuilding strategy that Oracle still half-heartedly recommends.

The Role of the INDEX_STATS View in Index Rebuilds

The Role of the INDEX_STATS View in Index Rebuilds

The INDEX_STATS view by default has no rows. You populate this view by executing the analyze index... validate structure command.

The key columns you need to pay attention to are the following: HEIGHT: Height of the index, which begins at 1 for root only index.

 BLOCKS: Number of blocks allocated to the index.

LF_ROWS: Number of leaf row entries (includes deleted row entries). DEL_LF_ROWS: Number of deleted leaf row entries not yet cleaned out. USED_SPACE: Total space used within the index (includes deleted entries). PCT_USED: Percentage of space used within the index (includes deleted entries). This is derived by the following formula: (USED_SPACE/BTREE_SPACE)*100.

BTREE_SPACE: Total size of the index (includes deleted entries).

You can estimate the non-deleted rows in an index by subtracting the DEL_LF_ROWS value from the LF_ROWS value.

 You can estimate the percentage of space used by the non-deleted rows of an indexed by using the following formula: ((USED_SPACE - DEL_LF_ROWS_LEN)/BTREE_SPACE) * 100

The following example shows how the optimizer is always aware of the deleted rows in a table and makes the correct choice, even when you delete a large percentage of a table's rows.

Let's create a simple table with 100,000 rows and create an index on it.

SQL> create table test as select rownum id, 'Sam' text from dual
  2* connect by level <=100000
SQL> /

Table created.

SQL> create index test_idx1 on test(id);

Index created.
SQL> Run the analyze index validate structure statement to check the number of lf_rows and lf_blks. SQL> analyze index test_idx1 validate structure

SQL> /

Index analyzed. Query the INDEX_STATS view to check the number of deleted leaf rows. SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;

   LF_ROWS    LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
    100000        222           0
SQL> Delete a large number of rows from the table and run the analyze index validate structure command again. SQL> delete test where id <=99999;

99999 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze index test_idx1 validate structure;

Index analyzed.

SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;

LF_ROWS    LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
    100000        222       99999
SQL> Gather statistics on both the table and the index. SQL>_execute dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'TEST',
cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>   Query the DBA_INDEXES view. SQL> select index_name, num_rows, leaf_blocks from dba_indexes where
     index_name = 'TEST_IDX1'
SQL> /

OWNER    INDEX_NAME                   NUM_ROWS   LEAF_BLOCKS
-------  -----------------------      ---------- -----------
HR       TEST_IDX1                       1           1
SH       TEST_IDX1                       0           0

SQL> The DBA_INDEXES views shows that only one leaf block is being utilized by the index to host the single column value that remains in the table. The optimizer correctly chooses the index, as expected. SQL> set autotrace traceonly explain
SQL> select * from test where id > 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2624864549
--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     7 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">10)

SQL> Once your rebuild the index and analyze the index (validate structure), this is what you get: SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;

LF_ROWS              LF_BLKS       DEL_LF_ROWS
---------- -         ---------     ---------------------
         1                 1                 0
SQL> Problems with the INDEX_STATS view 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. The real problem with using the analyze index …validate structure command to determine whether you should rebuild an index is that Oracle does automatically reuse deleted space in an index in most cases. The following is a simple example that illustrates this fact. Create a test table. SQL> create table reb_test (cust_id number, cust_code number, cust_name
varchar2(20));

Table created.
SQL> Insert a few test rows (nine rows in this example). SQL> insert into reb_test select rownum, rownum, 'Groucho Marx' from dual
  2  connect by level < 10;

9 rows created.

SQL> commit;

Commit complete.
SQL> Create an index on the CUST_ID column. SQL> create index reb_test_cust_id_idx on reb_test(cust_id);

Index created.
SQL> Delete four of the nine rows from the table. SQL> delete from reb_test where cust_id in (1,2,3,4);

4 rows deleted.

SQL> commit;

Commit complete.
SQL> Analyze the index with the analyze index …validate structure command. SQL> analyze index reb_test_cust_id_idx validate structure;

Index analyzed.
SQL> Query the INDEX_STATS view to find the value of the DEL_PCT column, which shows the percentage of the deleted leaf rows in the index. SQL> select lf_rows,del_lf_rows,del_lf_rows/lf_rows*100 del_pct from index_stats;

      LF_ROWS           DEL_LF_ROWS                  DEL_PCT
     --------            -----------                             ----------
         9                       4                                 44.4444444
SQL> As expected, the DEL_PCT column has a value of a little over 44%. Insert a single row into the table. SQL> insert into reb_test values (999,1,'Franco Marx');

1 row created.

SQL> commit;

Commit complete.
SQL> Analyze the index again. SQL> analyze index reb_test_cust_id_idx validate structure;

Index analyzed.
SQL> Check the percentage of the deleted rows now.

 SQL> select lf_rows,del_lf_rows,del_lf_rows/lf_rows*100 del_pct from index_stats;

   LF_ROWS                  DEL_LF_ROWS                              DEL_PCT
  --------------            ----------------------            --------------
      6                              0                                0

SQL> As you can see, the deleted percentage of rows, which was about 44%, is now zero. The reason for this is that while you've inserted a single row, that is still a quarter of the four rows initially deleted. If you insert a small number of rows into a large table after deleting a high percentage of rows, don't expect to see Oracle immediately reclaim the deleted space. The real point we're trying to make here is that the Oracle database does in most cases utilize the space left free by deleted rows for inserting fresh rows; the space doesn't necessarily end up as deadwood. If you're rebuilding indexes simply based on an arbitrary cutoff point for the percentage of deleted space in an index, you may not see any real gains over time, since the index itself may very well reuse all of the so-called wasted space. The final size of the index may very well grow back to its “natural" state anyway. In addition, depending on the percentage of rows currently marked as deleted based on the DEL_IF_ROWS statistic from the INDEX_STATS view means that you may actually miss potential opportunities for a valid rebuild. This is so because under some circumstances, the DEL_IF_ROWS statistic actually vastly underestimates the actual percentage of deleted rows in an index. Blindly using the DEL_IF_ROWS statistic as an index-rebuilding criterion means that you may both be rebuilding indexes that don't need a rebuild and missing out on real opportunities to gain from rebuilding an index. You really must make the rebuild decision based on the nature of the data in a column (sequence based, for example) and the pattern of deletes and inserts. We also recommend that you actually test the performance before and after a rebuild to see if it proves beneficial to you.

INDEX_STATS stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.

Note:

The ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE statement must be used in order to collect statistics

=======================================================================

HEIGH