All About Indexes


Introduction
Partitioned Indexes
Index-Only Tables (IOT)
Bitmap Indexes
Reverse Key Indexes
Function-Based Indexes
Descending Indexes
When to Rebuild Indexes
Monitoring Index Usage
Index Usage without "alter index.... monitoring usage"
Get Index Information


Introduction

THE COST OF INDEXES
Every time we change the data in a table, we may have to modify some related index information. The more indexes we create on a table, the more work we may have to do as we modify the table. Clearly we need to be careful when we define the set of indexes we want on a table. If we are too extravagant with creating indexes, we may pay a huge penalty as we load and modify the data – and it doesn't matter how quickly our queries can run if we can’t get the data into the system in time. But it's not simply the increased cost of the DML that worries us when we have indexes on our tables. The very act of changing the data could make our indexes less effective, and this is an issue that needs a little consideration. Fortunately, many indexes have a 'natural' steady state, so we often need only look out for special cases.

Type of Indexes

1- Partitioned Indexes

Like tables, indexes can also be partitioned; but with indexes you have a more options because the underlying table might or might not also be partitioned. The objective of this type of index is to separate the index into smaller partitions, just as we do now for a database table. There are essentially two different types of partitioned indexes available:

Global Indexes

To create a global partitioned index, use the CREATE INDEX parameter GLOBAL. This specifies that the index will be a global index. Further partitioning of the index is accomplished by using the following parameters:
·        GLOBAL--This parameter specifies a global partitioned index.
·        PARTITION part_name--This parameter is used to identify the partition. If you do not specify the partition name, a default name will be provided. It is not usually necessary to provide the partition name.
·        VALUES LESS THAT--This parameter is used to specify the range that is allocated for that particular partition in the same way as the partition was specified in the CREATE TABLE statement (discussed yesterday).
NOTE: The last partition should contain the keyword MAXVALUE for its range.

Creating a partitioned index.
CREATE INDEX "ETW".dogs_ix1 ON DOGS(ID)
PARTITION BY RANGE (ID)
PARTITION pt1 VALUES LESS THAN (`1000') TABLESPACE ts1,
PARTITION pt2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2);

ANLYSIS:
This create two partitions, the first holding values of ID that are less than 1,000, the second holding the remaining values of ID. If you do not specify the partition name, as is the case here, a default name will be provided.
While this does offer a great deal of flexibility, it is not without cost.  Since global partitioned indexes are not directly related to any single table partition, operations that affect any partition in the table can render all global partitioned indexes unusable.  Specifically the operations:
ADD (HASH) - COALESCE (HASH) – DROP – EXCHANGE – MERGE  - MOVE  -SPLIT - TRUNCATE

Local Indexes

In contrast to the global index, a local partitioned index is individually created on each partition. If you specify a local partitioned index, Oracle automatically maintains the index's partitioning along with that of the underlying table.
Local partitioned indexes are created through the use of the LOCAL parameter with the CREATE INDEX statement. It is unnecessary to provide partitioning information because the underlying table partitioning will be used. A local index can be created with the following syntax:

CREATE INDEX dogs_ix1 ON  DOGS (ID) LOCAL;

Because the index is local, all partition changes to the table will be automatically reflected on the index partitions as well.
Local partitioned indexes have some inherent advantages that are similar to the advantages you get from partitioned tables. These advantages include the following:
•    Because the index exists entirely on one partition, any maintenance operations affect only that one partition.
•    The Oracle optimizer can use the local index to generate better query plans based on the fact that a local index is used.
•    If a partition is lost and must be recovered, only the data and index for that particular partition needs to be recovered. With a global index, the entire index would need recovery.

 

2- Index-Only Tables (IOT)

Many systems contain several small tables (1 to 3 columns) where all of the elements form the primary key. However, there exists an extremely efficient way to create such tables, by using a B*-Tree structure. An index-organized table, that is a table with the same physical structure as an index, allows us to do exactly that. In an index-organized table, the database engine will place the data values in a « table » segment, but with a B*-tree structure. An index-only table is similar to an index, but whereas an index contains the primary key value and a ROWID pointing to where the data is kept, the index-only table stores the column data in the leaf block of the index.
Because the leaf blocks of the Oracle index are traditionally very small and tightly packed, there can be some drawbacks to having large rows stored there. Oracle has developed a way to compensate for this: If rows become too large (by a set threshold), the row data is stored in an overflow area as specified in the CREATE TABLE statement. This creates storage more like the traditional index and table relationship.
An index-only table contains the same structure as the Oracle B*-tree index. Only the leaf blocks have changed. Index-only tables have many of the attributes of both indexes and tables, but there are a few exceptions:
•    Because it is part index and part table, no other indexes can be added to the index-only table.
•    The UNIQUE constraint is not allowed on an index-only table.
•    A trigger can be added to the index-only table.
•    An index-only table cannot be stored in a cluster.
•    Replication is not supported at this time.
As you can see, there are some restrictions on index-only tables, but there is also a great deal of benefits.
When performing inserts, there is a performance improvement, since only a single segment needs to be updated, which is the table itself. Data retrieval is also faster since the optimizer processes the table just as if it were an index. Furthermore, one read operation is saved since ORACLE doesn't have to read the index prior to performing the read in the table.

When to Use Index-Only Tables
Index-only tables are very useful whenever data is always accessed via the primary key index. If this is the case with your data, the index-only table will cut down on the space used by both the index and the table (by combining them) and improve performance. Performance is improved because, by the time the ROWID would have been retrieved, you have the data.
Tables that are not accessed via the primary key value are not good candidates for index-only tables. Also, tables whose primary key values are updated and tables that have frequent insertions are not good candidates for index-only tables.

How to Create Index-Only Tables
Index-only tables are created with the CREATE TABLE command; the ORGANIZATION INDEXED qualifier is used to identify the table as index-only. The following qualifiers are used in creating index-only tables:
•    ORGANIZATION INDEXED--This qualifier specifies an index-only table organization.
•    OVERFLOW TABLESPACE ts_name--This qualifier specifies the overflow tablespace name.
•    PCTTHRESHOLD threshold--This qualifier specifies the percent of a block that a row must be larger than in order to be offloaded to the overflow tablespace.

Creating an index-only table with the CREATE TABLE command.
CREATE TABLE DOGS (
ID NUMBER,
NAME VARCHAR2(40),
OWNER_ID NUMBER,
BREED_ID NUMBER,
RANK NUMBER NULL,
NOTES VARCHAR2(80)
PRIMARY KEY(ID) )
ORGANIZATION INDEXED PCTTHRESHOLD 40
OVERFLOW TABLESPACE DOGS2
TABLESPACE DOGS;

ANLYSIS:
This specifies that the index-only tablespace be created on the DOGS tablespace, whereas the overflow tablespace used is DOGS2.
NOTE: It is necessary to specify the PRIMARY KEY value when creating an index-only table. This is the value on which the index is created.

Unfortunately, there are also some disadvantages to using index-organized tables. For example, it is not possible to create additional indexes since the occurrences of this type of table do not have a ROWID. It is also impossible to add new columns or to modify existing columns. The message « ORA-25182 feature not currently available for index-organized table » is used to alert us to these restrictions. Another factor to consider is the case where updating key values is allowed. ORACLE must delete the old record and do an insert based on the new key value, that is relocate a leaf of the B*-tree to another location in the B*-tree. The net result is 2 SQL operations. Therefore, one should weight the advantages and disadvantages before choosing this type of table, even though it is relatively easy to convert an index-organized table back to a conventional format using create table as and then doing an Import/Export to that table.



3- Bitmap Indexes
Another type of index available the bitmap index. With the traditional index you saw earlier, Oracle uses a B*-tree method to traverse the index to find the leaf block. With a bitmap index, a bitmap of ROWIDs is kept; this bitmap indicates which rows correspond to the index item. If the bit is set, this indicates that the corresponding row contains the key value; if the bit is not set, the opposite is true.
As you can probably tell, bitmap indexes can probably be quite useful under the right circumstances and useless overhead otherwise.

When to Use Bitmap Indexes
As you can probably guess, the bitmap index works well on items with low cardinality. Low cardinality means there is a small amount of variance in the possible values stored in that column. For example, the column representing the sex of the dog is said to have low cardinality because only two values are possible. Other column types that might have low cardinality include
•    Marital status
•    Account status (good or bad)
•    Sales region (if there are only a few)
•    Rank (if there are only a few)
•    Special notes (whether there is a note)
With columns that have low cardinality, the bitmap index can greatly improve performance. Columns with high cardinality are not candidates for bitmap indexes.
Locking issues affect data manipulation operations in Oracle. As a result, bitmapped indexes are not appropriate for OLTP applications that have a high level of concurrent insert, update and delete operations. Concurrency is usually not an issue in a data-warehousing environment where the data is maintained by bulk loads, inserts and updates.
In addition, bitmapped index maintenance is deferred until the end of the bulk DML operation. If 100 rows are inserted into a table, the inserted rows are placed into a sort buffer and the updates of all 100-index entries are applied as a group. As a result, bitmapped indexes are appropriate for most decision support applications (even those that have bulk updates applied on a regular basis).
Mass updates, inserts and delete will run faster if you drop the bitmapped indexes, execute the DML and recreate the bitmapped indexes when the DML completes. Run timings using the straight DML and compare it to the total time consumed by the drop bitmapped index/execute DML/recreate bitmapped index process.

How to Create Bitmapped Indexes
A bitmap index is created with the CREATE INDEX command with the BITMAP qualifier. To create a bitmap index on the SEX field in the DOGS table, you can use the following syntax:
CREATE BITMAP INDEX dogs_bx1 ON  DOGS (SEX);
This simple statement will create the bitmap index on the column specified.



4- Reverse Key Indexes
When a column that contains an incrementally increasing value (like those provided by sequences) is indexed, there is a very poor distribution of that value across the B-tree, and hot spots can be introduced into the index.  Reverse-key indexes can help eliminate these hot spots by providing a much wider distribution of values than would occur in a regular B-tree index. In this situation, simultaneous inserts requested by the users are done in the same leaf of the index. This will generate contention on the same Oracle block buffer. This problem is exacerbated when several tasks are initiated in parallel, all processing updates against the database. Another problem is the phenomena of « sliding » when the system deletes older numbers and inserts new ones. In this case, the index becomes increasingly large as the space occupied by the deleted index entries never gets reused.
The answer to this situation is the reverse-key index whereby different index leafs is used, even if the key sequence is chronological.
There are a few cases where reverse key indexes can help to improve performance. One is in RAC environments. If you have a column populated by an increasing sequence the new entries come to the same blocks when you are inserting rows. If you have many concurrent sessions inserting rows from different RAC instances then you will have a contention for the same index blocks between nodes. If you use reverse key indexes in this case then the new index entries will go to different blocks and contention will be reduced.
In single instance databases there is also a case where reverse key indexes can be helpful. If you have a column populated by an increasing sequence, you delete some old rows from the table and you do not do range scans on that column and you have contention issues on index blocks, reverse key indexes can be considered. The reverse key index will scatter the entries across different blocks during inserting and your many concurrent sessions will not have index block contention issues.
If you are deleting some old rows, the blocks from a normal index on that column will have some used and some empty space in them, but they will not be put on the freelist because they are not completely free. That empty space will not be used because the sequence values are always increasing and they will not go to those old blocks because of that. You will be able to use that space for different values with reverse key indexes.
One of the things to be careful about reverse key indexes is that you cannot perform range scans on them. Because the entries are stored as reversed you lose the capability to range scan on that index.
To change an existing index as a reverse key index you can use the alter index statement.
alter index indexname rebuild reverse;
After this change the index size was reduced down to 16MB from 250MB. This change got rid of the buffer busy waits on the index blocks. The program run time was reduced from about 40 minutes to about 25 minutes.

Example
Let’s say you are using a sequence to populate a primary key field.  Sequences generate numbers like 1, 2, 3, 4, 5, .....  Everyone is hitting the same side of the index -- from every node.  You have a terribly hot spot there where all insertions need the same part of the index to work on.
A reverse key index is an attempt to spread the load out over the entire index structure so people are hitting the left, the right, the middle, all over the index -- not just adding an increasing number to the one side.
Before the index entry is added to the index structure, all of its bytes are "reversed" -- so yes, this affects all elements of the index structure.
It is as simple as that -- instead of all inserts hitting one side of the index, producing mass contention on one side of the index -- a reverse key index will tend to distribute the inserts throughout the entire structure more evenly.

How to Create Reverse Key Indexes
CREATE INDEX my_index ON my_table (my_fields) REVERSE;

To make an existing index a reverse index use:
ALTER INDEX index_name REBUILD REVERSE;

Substitute no reverse if you want to rebuild you index as a non-reversed one.
No options are without a certain cost.  While reverse-key indexes can help in the distribution of data across an index, this very strength can be a weakness.  Since the actual (non-reversed) values of the index are not stored sequentially in the index, index range-scans cannot be performed.  Only fetch-by-key and full index scans can be performed on a reverse-key index.  Therefore, it is not a good idea to build a reverse-key index on a column that might use range-scans.


5- Function Based Indexes
Function based indexes are really going to improve the performance of all those queries which use functions such as SUM, AVG in the select or when the where clause includes some kind of other function such as some mathematical calculation on two columns. Suppose you have an application that routinely selects the sum of the two columns SALARY and BONUS from the PAYMENTS table. In the past, such a select may have been fairly slow, because of the calculation being performed on the select. Now, however, the calculation is stored as part of the index, making the overall selection process that much quicker.

CREATE INDEX my_ind ON payments (salary + bonus);

So, this query would now utilize the index:
SELECT employee_id
FROM payments
WHERE (salary + bonus) > 1000;

You need to enable function-based indexes before you can use them, as follows:
ALTER SESSION SET QUERY_REWRITE_ENABLED = true
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = trusted
Or
QUERY_REWRITE_INTEGRITY = TRUSTED
QUERY_REWRITE_ENABLED = TRUE

Also, to use a function-based index:
    They must be analyzed before it can be used
    The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.


6- Descending Indexes
Descending indexes are a special type of function-based index, in which index entries are sorted in descending order.  Using the DESC clause does this. When creating an index, as in the example below:
create index ix_desc on phonebook2(zip desc);
   
There are a couple special considerations when using descending indexes:
•    Descending indexes cannot be used on reverse indexes
•    The DESC option is ignored if used in the creation of bitmap indexes




When to Rebuild Indexes??
Oracle allows you to rebuild your indexes while online. In the past, creating or rebuilding the index required a full lock on the table. On a large table, this could mean that an application is unusable for several hours. Now, however, Oracle allows you to create or rebuild the index while users can still perform the full range of data processes. To do this, Oracle creates the index structure before populating it. While populating, all changes to the table are recorded in a journal table. As the index is completed, the journal table changes are then built in.
In order to understand what we must do with the index, we must first get an idea of the current state of the index. This can be accomplished by using the ANALYZE INDEX VALIDATE STRUCTURE command. Normally, the ANALYZE INDEX command creates either computed or estimated statistics for the index that can be seen in the DBA_INDEXES view. This action may produce unintentional side effects, especially if the index has not previously been analyzed.
The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.
If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX REBUILD command. Although not necessarily recommended, this command could be executed during normal operating hours. Rebuilding the index uses the existing index as a basis. The alternative is to drop and re-create the index. Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation. Below is an example of ANALYZE INDEX VALIDATE STRUCTURE and sample output from INDEX_STATS:

NOTE: validate structure tries to lock the table, and fails with Oracle error 54 (resource busy and acquire with NOWAIT specified) if anyone is currently updating the table. If the validation takes a long time, anyone who then tries to update the table is locked out.

ANALYZE INDEX PK_shopping_basket VALIDATE STRUCTURE;
SELECT name, height, lf_rows, lf_blks, del_lf_rows, distinct_keys, used_space,
       (del_lf_rows/lf_rows)*100 as ratio
   FROM INDEX_STATS;

NAME                      HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROW DISTINCT_K USED_SPACE RATIO
------------------------- --------- ---------- ---------- ---------- ---------- ---------- ------
PK_SHOPPING_BASKET                2          1          3          1          1         65     10

The following INDEX_STATS columns are especially useful:
height refers to the maximum number of levels encountered within the index. An index could have 90 percent of the nodes at three levels, but excessive splitting and spawning in one area of the index with heavy DML operations could make nodes in that area to have more than three levels.
lf_rows refers to the total number of leafs nodes in the index.
del_lf_rows refers to the number of leaf rows that have been marked deleted as a result of table DELETES.


An index should be considered for rebuilding under any of the following conditions:
- The deleted leaf rows should be less than 25% of the total number of leaf rows. An excessive number of deleted leaf rows indicates that a high number of deletes or updates have occurred to the index column(s). The index should be rebuilt to better balance the tree. The INDEX_STATS table can be queried to determine if there are excessive deleted leaf rows in relation to the total number of leaf rows. i.e. if DEL_LF_ROWS / LF_ROWS > 0.25.
- If the 'HEIGHT' is greater than 3. For most indexes, the height of the index will be quite low, i.e. one or two. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. This can lead to unnecessary database block reads of the index.
- If the number of rows in the index ('LF_ROWS') is significantly smaller than 'LF_BLKS' this can indicate a large number of deletes, indicating that the index should be rebuilt.
- BLOCK_GETS greater than 5

Let’s look at an example:
ANALYZE INDEX PK_item_basket VALIDATE STRUCTURE;
SELECT name, height, lf_rows, del_lf_rows,
       (del_lf_rows/lf_rows)*100 as ratio
  FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- ----------
PK_ITEM_BASKET                          1        235         74 31.4893617

In this example, the ratio of deleted leaf rows to total leaf rows is clearly above 25%. This is a good candidate for rebuilding. Let’s rebuild the index and examine the results.

ALTER INDEX pk_item_basket REBUILD online;
While the new index is being built, it will exist simultaneously with the old index in the database. Therefore, there must be enough space available to store both the old index and the new index in the database in order to use this command. When the index gets rebuilt, the new index becomes available and the old index gets dropped. Its space is reclaimed by the database.

There are some options available with this command. I usually use the following syntax:
ALTER INDEX pk_item_basket REBUILD parallel nologging compute statistics tablespace INDX;
 The PARALLEL clause causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances multiplied by the value of the PARALLEL_THREADS_PER_CPU initialization parameter. The default value of PARALLEL_THREADS_PER_CPU is 2 for Oracle 8i. So, if your database server is a four-CPU box, your index will be rebuilt by eight parallel processes, which would lead to a performance gain.
The NOLOGGING clause allows you to rebuild the index faster because operations will not be logged in the redo log file.
The COMPUTE STATISTICS clause enables you to collect statistics at relatively little cost during the rebuilding of an index.
The TABLESPACE clause specifies the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it.

ANALYZE INDEX pk_item_basket VALIDATE STRUCTURE;
SELECT name, height, lf_rows, del_lf_rows,
      (del_lf_rows/lf_rows)*100 as ratio
 FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- ----------
PK_ITEM_BASKET                          1        161          0          0

The index is rebuilt and validated once again. Examining the INDEX_STATS table shows that the 74 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 235 to 161, which is a difference of 74 leaf rows. This index should provide better performance for the application.



How Indexes Become Fragmented
The advantages of indexing do not come without a cost. As database objects, indexes are created for tables only and they must be in sync with them: indexes must be updated by the database with every data manipulation language (DML) operation—INSERT, DELETE, or UPDATE. When a row is deleted from a table, the Oracle database will not reuse the corresponding index space until you rebuild the index.
Therefore, indexes are always growing and can become very fragmented over time, especially in tables with dynamic data. As indexes grow, two things happen: splitting and spawning.
Splitting happens when an index node becomes full with keys and a new index node is created at the same level as a full node. This widens the B*-tree horizontally and may degrade performance.
Spawning is the process of adding a new level to an index. As a new index is populated, it begins life as a single-level index. As keys are added, a spawning takes place and the first-level node reconfigures itself to have pointers to lower-level nodes. Spawning takes place at specific points within the index, not for the entire index. For example, a three-level index might have a node that experiences heavy INSERT activity. This node could spawn a fourth level without the other level-three nodes spawning new levels. That makes the index unbalanced.


A PL/SQL Procedure to Detect and Rebuild Out-of-Balance Indexes
This procedure (called RebuildUnbalancedIndexes) takes care of both global and local indexes. Global index information is extracted into the csrGlobalIndexes cursor from the USER_INDEXES view WHERE value in Partitioned column is 'NO':
cursor csrGlobalIndexes is
   select index_name, tablespace_name
      from user_indexes
      where partitioned = 'NO';
Local indexes are created for partitioned tables. They are equi_partitioned with the table they are based on. That means that for each index entry residing in partition CUSTOMER_K, there is a row of the table CUSTOMER that also resides in that same partition CUSTOMER_K. Local index information is extracted into the csrLocalIndexes cursor from the USER_IND_PARTITIONS view WHERE value in Status column is 'USABLE':
cursor csrLocalIndexes is
   select index_name, partition_name, tablespace_name
      from user_ind_partitions
      where status = 'USABLE';
If local indexes are UNUSABLE as a result of TRUNCATE or MOVE partition operations, they need to be rebuilt. Even though it's not a topic of this 10-Minute Solution, and it's not part of our procedure, here is the syntax for that case, for your reference:
alter table Customer
   modify partition Customer_K
   rebuild unusable local indexes;
For each index (global or local), the procedure generates statistics using the ANALYZE INDEX command with the VALIDATE STRUCTURE clause; extracts it from the INDEX_STATS view; checks for the index height and number of deleted leaf rows; and decides whether to rebuild the index or not.

Here is the COMPLETE code:
Create sequence S_AA_DB_MAINTENANCE
  Start with    1
  Increment by  1
/

create table AA_DB_MAINTENANCE
(
    Actionid   number,
    StartTime  date,
    EndTime    date,
    Action     varchar2(1000)
)
/


CREATE OR REPLACE
Procedure print_date_diff(p_dte1 IN DATE, p_dte2 IN DATE, v_result OUT varchar2)
IS
  v_diff NUMBER := 0;
  v_hrs NUMBER := 0;
  v_min NUMBER := 0;
  v_sec NUMBER := 0;
BEGIN
  v_diff := ABS(p_dte2 - p_dte1);
  v_hrs := TRUNC(v_diff, 0)*24;  -- start with days portion if any
  v_diff := (v_diff - TRUNC(v_diff, 0))*24;  -- lop off whole days, convert to hrs
  v_hrs := v_hrs + TRUNC(v_diff, 0);  -- add in leftover hrs if any
  v_diff := (v_diff - TRUNC(v_diff, 0))*60;  -- lop off hrs, convert to mins
  v_min := TRUNC(v_diff, 0);  -- whole mins
  v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);  -- lop off mins, convert to secs

  v_result := TO_CHAR(v_hrs) || ' HRS ' ||
              TO_CHAR(v_min) || ' MIN ' ||
              TO_CHAR(v_sec) || ' SEC';
  DBMS_OUTPUT.put_line(v_result);

END print_date_diff;
/

CREATE OR REPLACE
Procedure           DB_REBUILD_INDEXES
is
-- *****************************************
-- PURPOSE: Rebuild Indexes that are Fragmented
--          This Procedure is called by DB_MAINTENANCE_WEEKLY
--          This script will analyze all the indexes under the current Schema
--          After this the dynamic view INDEX_STATS is consulted to see if an index
--          is a good candidate for a rebuild.
--          Rebuild the index when :
--          - Deleted entries represent 25% or more of the current entries
--          - The index depth is more then 3 levels (defined in variable v_MaxHeight)

-- AUTHOR:      Diego Pafumi
-- ******************************************
   v_MaxHeight       integer := 3;
   v_MaxLeafsDeleted integer := 25;
   v_Count           integer := 0;
   v_actionid        integer;
   v_date            date;
   v_error_message varchar2(255);
   v_error_code    number(8);
   v_error_message2     varchar2(255);
   v_finaldiff varchar2(40);

   --Cursor to Manage NON-Partitioned Indexes
    --Select the Indexes, excluding the ones in the Recycle Bin
   cursor cur_Global_Indexes is
      select index_name, tablespace_name
         from user_indexes
         where table_name not like 'BIN$%'
           and partitioned = 'NO';

   --Cursor to Manage Current Index
   cursor cur_IndexStats is
      select name, height, lf_rows as leafRows, del_lf_rows as leafRowsDeleted
         from index_stats;
   v_IndexStats cur_IndexStats%rowtype;

   --Cursor to Manage Partitioned Indexes
--   cursor cur_Local_Indexes is
--      select index_name, partition_name, tablespace_name
--         from user_ind_partitions
--         where status = 'USABLE';

begin
   select S_AA_DB_MAINTENANCE.nextval into v_actionid from dual;
   select sysdate into v_date from dual;
   delete from AA_DB_MAINTENANCE
      where StartTime < sysdate - 20;
   insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
      values (v_actionid, v_date, 'DB_REBUILD_INDEXES Procedure Started');
   commit;

   DBMS_OUTPUT.ENABLE(1000000);
   /* Global or Standard Indexes Section */
   for v_IndexRec in cur_Global_Indexes
   loop
      begin
dbms_output.put_line('before analyze ' || v_IndexRec.index_name);
         execute immediate 'analyze index ' || v_IndexRec.index_name || ' validate structure';
dbms_output.put_line('After analyze ');
         open cur_IndexStats;
         fetch cur_IndexStats into v_IndexStats;
         if cur_IndexStats%found then
            if (v_IndexStats.height > v_MaxHeight) OR
               (v_IndexStats.leafRows > 0 AND v_IndexStats.leafRowsDeleted > 0 AND
               (v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

               begin
                  dbms_output.put_line('*****************************************************************');
                  dbms_output.put_line('Rebuilding index ' || v_IndexRec.index_name || ' with '
                                      || to_char(v_IndexStats.height) || ' height and '
                                      || to_char(trunc(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows)) || ' % LeafRows');

                  --- Line for Oracle 9i
                  -- execute immediate 'alter index ' || v_IndexRec.index_name ||
                  --                  ' rebuild parallel nologging online tablespace ' || v_IndexRec.tablespace_name;

                  --- Line for Oracle 10g
                  --  On 10g Oracle now automatically collects statistics during index creation and rebuild
                  execute immediate 'alter index ' || v_IndexRec.index_name ||
                           ' rebuild' ||
                           ' parallel nologging compute statistics' ||
                           ' tablespace ' || v_IndexRec.tablespace_name;

                  insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
                                       values (v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || ' has been rebuilt');
                  dbms_output.put_line('*****************************************************************');
                  v_Count := v_Count + 1;

               exception
                  when OTHERS then
                     dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');
                     insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
                                        values (v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');
                     commit;
               end;
            end if;
         end if;
         close cur_IndexStats;
       exception
         when OTHERS then
           dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');
           insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
                                values (v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');
           commit;

       end;
   end loop;

   print_date_diff(v_date, sysdate, v_finaldiff);
   insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
         values (v_actionid, sysdate, 'DB_REBUILD_INDEXES Finished in ' || v_finaldiff);
   commit;



/*
   dbms_output.put_line('Global or Standard Indexes Rebuilt: ' || to_char(v_Count));
   v_Count := 0;

   --Local indexes Section
   for v_IndexRec in cur_Local_Indexes
   loop
      execute immediate 'analyze index ' || v_IndexRec.index_name ||
                        ' partition (' || v_IndexRec.partition_name ||
                        ') validate structure';

      open cur_IndexStats;
      fetch cur_IndexStats into v_IndexStats;
      if cur_IndexStats%found then
         if (v_IndexStats.height > v_MaxHeight) OR
         (v_IndexStats.leafRows > 0 and v_IndexStats.leafRowsDeleted > 0 AND
          (v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

            v_Count := v_Count + 1;
            dbms_output.put_line('Rebuilding Index ' || v_IndexRec.index_name || '...');
--            execute immediate 'alter index ' || v_IndexRec.index_name ||
--                           ' rebuild' ||
--                           ' partition ' || v_IndexRec.partition_name ||
--                           ' parallel nologging compute statistics' ||
--                           ' tablespace ' || v_IndexRec.tablespace_name;

         end if;
      end if;
      close cur_IndexStats;
   end loop;
   dbms_output.put_line('Local Indexes Rebuilt: ' || to_char(v_Count));
*/
--
  EXCEPTION
     WHEN OTHERS THEN
       --Show Error on specific line
       --10g Only      
       DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
       v_error_code := SQLCODE;
       v_error_message := SQLERRM;
       --10g Only      
       v_error_message2 := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;
       --10g Only      
       DBMS_OUTPUT.PUT_LINE('Error: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
      
       dbms_output.put_line('Error: '||TO_CHAR(v_error_code)||' - '||v_error_message);

       insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)
         values (v_actionid, sysdate, '*** ERRORS on DB_REBUILD_INDEXES:' || TO_CHAR(v_error_code)||' - '||v_error_message);
       commit;
end;
/



-- Run it Weekly on Sundays at 5AM
VARIABLE v_jobnum NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:v_jobnum,'DB_MAINTENANCE_WEEKLY;', TO_DATE('2309200705','DDMMYYYYHH24'), 'trunc(sysdate,''hh'') + 7');
EN;
/

This is Just the PL/SQL Version of it
spool Rebuild_Indexes.txt
-- *****************************************
-- PURPOSE: Rebuild Indexes that are Fragmented
--          This is the PL/SQL Code version
--          This script will analyze all the indexes under the current Schema
--          After this the dynamic view INDEX_STATS is consulted to see if an index
--          is a good candidate for a rebuild.
--          Rebuild the index when :
--          - Deleted entries represent 25% or more of the current entries
--          - The index depth is more then 3 levels (defined in variable v_MaxHeight)

-- AUTHOR:      Diego Pafumi
-- ******************************************

set serveroutput on
declare
   v_MaxHeight       integer := 3;
   v_MaxLeafsDeleted integer := 25;
   v_Count           integer := 0;
   v_date            date;
   v_error_message varchar2(255);
   v_error_code    number(8);
   v_error_message2     varchar2(255);
   v_finaldiff varchar2(40);

   --Cursor to Manage NON-Partitioned Indexes
    --Select the Indexes, excluding the ones in the Recycle Bin
   cursor cur_Global_Indexes is
      select index_name, tablespace_name
         from user_indexes
         where table_name not like 'BIN$%'
           and partitioned = 'NO';

   --Cursor to Manage Current Index
   cursor cur_IndexStats is
      select name, height, lf_rows as leafRows, del_lf_rows as leafRowsDeleted
         from index_stats;
   v_IndexStats cur_IndexStats%rowtype;

   --Cursor to Manage Partitioned Indexes
--   cursor cur_Local_Indexes is
--      select index_name, partition_name, tablespace_name
--         from user_ind_partitions
--         where status = 'USABLE';

begin
   select sysdate into v_date from dual;

   DBMS_OUTPUT.ENABLE(1000000);
  
   /* Global or Standard Indexes Section */
   for v_IndexRec in cur_Global_Indexes
   loop
      begin
dbms_output.put_line('before analyze ' || v_IndexRec.index_name);
         execute immediate 'analyze index ' || v_IndexRec.index_name || ' validate structure';
dbms_output.put_line('After analyze ');
         open cur_IndexStats;
         fetch cur_IndexStats into v_IndexStats;
         if cur_IndexStats%found then
            if (v_IndexStats.height > v_MaxHeight) OR
               (v_IndexStats.leafRows > 0 AND v_IndexStats.leafRowsDeleted > 0 AND
               (v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

               begin
                  dbms_output.put_line('*****************************************************************');
                  dbms_output.put_line('Rebuilding index ' || v_IndexRec.index_name || ' with '
                                      || to_char(v_IndexStats.height) || ' height and '
                                      || to_char(trunc(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows)) || ' % LeafRows');

                  --- Line for Oracle 9i
                  -- execute immediate 'alter index ' || v_IndexRec.index_name ||
                  --                  ' rebuild parallel nologging online tablespace ' || v_IndexRec.tablespace_name;

                  --- Line for Oracle 10g
                  --  On 10g Oracle now automatically collects statistics during index creation and rebuild
                  execute immediate 'alter index ' || v_IndexRec.index_name ||
                           ' rebuild' ||
                           ' parallel nologging compute statistics' ||
                           ' tablespace ' || v_IndexRec.tablespace_name;

                  dbms_output.put_line('The index ' || v_IndexRec.index_name || ' has been rebuilt');
                  dbms_output.put_line('*****************************************************************');
                  v_Count := v_Count + 1;

               exception
                  when OTHERS then
                     dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');
                     commit;
               end;
            end if;
         end if;
         close cur_IndexStats;
       exception
         when OTHERS then
           dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');
           commit;

       end;
   end loop;

   print_date_diff(v_date, sysdate, v_finaldiff);
   dbms_output.put_line ('Rebuild INDEXES Finished in ' || v_finaldiff);
   dbms_output.put_line('# of Indexes Rebuilt: ' || to_char(v_Count));


/*
   v_Count := 0;

   --Local indexes Section
   for v_IndexRec in cur_Local_Indexes
   loop
      execute immediate 'analyze index ' || v_IndexRec.index_name ||
                        ' partition (' || v_IndexRec.partition_name ||
                        ') validate structure';

      open cur_IndexStats;
      fetch cur_IndexStats into v_IndexStats;
      if cur_IndexStats%found then
         if (v_IndexStats.height > v_MaxHeight) OR
         (v_IndexStats.leafRows > 0 and v_IndexStats.leafRowsDeleted > 0 AND
          (v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

            v_Count := v_Count + 1;
            dbms_output.put_line('Rebuilding Index ' || v_IndexRec.index_name || '...');
--            execute immediate 'alter index ' || v_IndexRec.index_name ||
--                           ' rebuild' ||
--                           ' partition ' || v_IndexRec.partition_name ||
--                           ' parallel nologging compute statistics' ||
--                           ' tablespace ' || v_IndexRec.tablespace_name;

         end if;
      end if;
      close cur_IndexStats;
   end loop;
   dbms_output.put_line('Local Indexes Rebuilt: ' || to_char(v_Count));
*/
--
  EXCEPTION
     WHEN OTHERS THEN
       --Show Error on specific line
       --10g Only      
       DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
       v_error_code := SQLCODE;
       v_error_message := SQLERRM;
       --10g Only      
       v_error_message2 := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;
       --10g Only      
       DBMS_OUTPUT.PUT_LINE('Error: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
      
       dbms_output.put_line('Error: '||TO_CHAR(v_error_code)||' - '||v_error_message);
end;
/
spool off



And Another Method of Index Rebuilder with Job Scheduler:
An index should be considered for rebuilding under any of the following conditions:
- The percentage of deleted rows exceeds 30% of the total, i.e. if DEL_LF_ROWS / LF_ROWS > 0.3.
- If the 'HEIGHT' is greater than 3.
- If the number of rows in the index ('LF_ROWS') is significantly smaller than 'LF_BLKS' this can indicate a large number of deletes,
   indicating that the index should be rebuilt.
- BLOCK_GETS greater than 5

We considered job scheduler for index rebuilding.
Steps:
1. Create a table TEMP_INDEX_STAT, which is similar to INDEX_STATS
2. Create a procedure GEN_INDEX_STAT. It will analyze an index and store that data in TEMP_INDEX_STAT
3. Create a job class LOW_PRIORITY_CLASS with LOW_GROUP resource consumer group, so that the job might not hamper production transaction.
4. Create a job GET_INDEX_STAT_JOB
5. Create a table RUNNING_CMDS
6. Create a procedure REBUILD_INDEX, which will REBUILD the indexes
7. Create another job REBUILD_INDEX_JOB. This job will call the above procedure to rebuild the indexes.
Please be sure that the load in the database is not at the peak. If yes, you may get resource timeout error during the job execution period and the job may be failed.

Step 1. Create table TEMP_INDEX_STAT
CREATE TABLE TEMP_INDEX_STAT AS SELECT * FROM INDEX_STATS WHERE 1=2;

Step 2. Create the Procedure GEN_INDEX_STAT
CREATE OR REPLACE PROCEDURE GEN_INDEX_STAT (PRM_SCHEMA VARCHAR2)
IS
  CURSOR INDEX_CUR IS SELECT INDEX_NAME
                        FROM DBA_INDEXES d
                        WHERE OWNER = prm_schema
                          AND NOT EXISTS (SELECT 1
                                            FROM temp_index_stat b
                                            WHERE d.index_Name = b.NAME
                                         )
                          AND INDEX_TYPE = 'NORMAL';

   v_str VARCHAR2(500);

BEGIN
   FOR INDEX_REC IN INDEX_CUR LOOP
      v_str := 'ANALYZE INDEX '||PRM_SCHEMA||'.'||INDEX_REC.INDEX_NAME||' VALIDATE STRUCTURE ';
      EXECUTE IMMEDIATE v_str;
      v_str := 'insert into TEMP_INDEX_STAT select * from index_stats';
      EXECUTE IMMEDIATE v_str;
      COMMIT;
   END LOOP; --
END GEN_INDEX_STAT;
/


Step 3. Create a Job Class with low Pirority
begin
dbms_scheduler.create_job_class(
   job_class_name => 'LOW_PRIORITY_CLASS',
   resource_consumer_group => 'LOW_GROUP',
   logging_level => DBMS_SCHEDULER.LOGGING_FULL,
   log_history => 60,
   comments => 'LOW PRIORITY JOB CLASS');
end;
/


Step 4. Create a Job to Call Pricedure
You may set START_DATE if you want to schedule the following job.
BEGIN
dbms_scheduler.create_job
(
job_name=> 'GEN_INDEX_STAT_JOB',
job_type=> 'PLSQL_BLOCK',
job_action=>'begin GEN_INDEX_STAT(''SCHEMA_NAME''); end;',
START_DATE=>NULL,
REPEAT_INTERVAL=>NULL,
ENABLED=>TRUE,
AUTO_DROP=>FALSE,
COMMENTS=>'Generate Index Stat',
job_class=>'LOW_PRIORITY_CLASS'
);
END;


Step 5. Create table RUNNING_CMDS
Upto this point, we have collected statistics for all indexes in our schema.
Now we need to REBUILD the indexes according to the conditions described above.
This table will store the commands so that we could easily identify which index is in rebuild process during the running of the following job.
CREATE TABLE RUNNING_CMDS CMD VARCHAR2(200);


Step 6. Create the Procedure to Rebuild Reported Indexes
CREATE OR REPLACE PROCEDURE REBUILD_INDEX(PRM_SCHEMA VARCHAR2)
IS
  CURSOR CUR IS
      SELECT NAME
        FROM TEMP_INDEX_STAT a
        WHERE (HEIGHT >= 4
               OR (del_lf_rows/lf_rows > 0.3 AND lf_rows > 0)
               OR (blks_gets_per_access > 4)
               )
          AND EXISTS (SELECT 1
                       FROM dba_indexes d
                       WHERE a.NAME = D.index_name
                       AND d.index_type <> 'LOB'
                     );

BEGIN
   execute immediate 'truncate table RUNNING_CMDS';
   FOR REC IN CUR LOOP
      v_str := 'ALTER INDEX '||PRM_SCHEMA||'.'||REC.NAME||' REBUILD ONLINE';
      INSERT INTO RUNNING_CMDS VALUES(V_STR);
      COMMIT;
      EXECUTE IMMEDIATE v_str;
   END LOOP;
END REBUILD_INDEX;
/



Step 7. Create a Job to call the previous procedure
You may set START_TIME according to your requirement.
BEGIN
dbms_scheduler.create_job
(
job_name=> 'REBUILD_INDEX_JOB',
job_type=> 'PLSQL_BLOCK',
job_action=>'begin REBUILD_INDEX(''SCHEMA_NAME''); end;',
START_DATE=>NULL,
REPEAT_INTERVAL=>NULL,
ENABLED=>TRUE,
AUTO_DROP=>FALSE,
COMMENTS=>'Rebuild Index',
job_class=>'LOW_PRIORITY_CLASS'
);
END;


Rebuilding Indexes
Beginning in 9i, you can rebuild your indexes online and compute statistics at the same time. Online index rebuild is also available for reverse key indexes, function-based indexes and key compressed indexes on both regular tables and index organized tables (including secondary indexes). In previous releases, you could either issue one of the following:
    alter index index_name rebuild online
 or
    alter index index_name rebuild compute statistics

However it was not possible to combine these statements into one operation. In 9i, you can issue:
    alter index index_name rebuild compute statistics online;

This allows your current users to be able to access the original index while it is being rebuilt and having statistics get generated against it. When you rebuild your indexes online, the new index is built within your index tablespace while the old index is still being used. The online option specifies that DML operations on the table or partition are allowed during rebuilding of the index. After the new index is completed the old index is dropped. When you rebuild your indexes and do not specify the online option, the database locks the entire table from being used while the index is being rebuilt. This option can also be combined with the nologging attribute to avoid generating redo when rebuilding your indexes (ex: alter index index_name rebuild compute statistics online nologging;)




Monitoring Index Usage
By using the 'alter index … monitoring usage' statement you can see if a particular index is being used.
Query v$object_usage as the owner of those indexes to see if the index has been used and during what time period it was monitored, it will show you just whether the index has been used or not; it will not show you how many times the index was used or when it was last used.
To stop monitoring an index, type:
ALTER INDEX index_name NOMONITORING USAGE;

This script activates index monitoring on an entire schema (must be run as System)
set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
   from dba_indexes
   where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool monitor_idx.log
@monitor_idx.tmp
spool off

A sample query on V$OBJECT_USAGE confirms that monitoring has been activated (must be run as the OWNER of indexes)
set linesize 92
set pagesize 9999
select substr(index_name,1,25) index_name,
       substr(table_name,1,15) table_name,
       MONITORING, USED, START_MONITORING, END_MONITORING
from v$object_usage
order by used;
INDEX_NAME      TABLE_NAME            MON USED   START_MONITORING    END_MONITORING
-------------   --------------------  --- ----   ------------------- --------------
ITEM_ORDER_IX   ORDER_ITEMS           YES   NO   08/15/2001 11:23:10
INVENTORY_PK    INVENTORIES           YES  YES   08/15/2001 16:51:32
PROD_NAME_IX    PRODUCT_DESCRIPTIONS  YES   NO   08/15/2001 16:50:32
ORDER_P         ORDERS                YES  YES   08/15/2001 17:10:32
PRD_DESC_PK     PRODUCT_DESCRIPTIONS  YES  YES   08/15/2001 17:13:32


Another useful index, is this one, to check ALL the indexes on the DB
select u.name "OWNER",
       io.name "INDEX_NAME",
       t.name "TABLE_NAME",
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING",
       decode(bitand(nvl(ou.flags,0), 1), 0, 'NO', 'YES') "USED",
       ou.start_monitoring "START_MONITORING",
       ou.end_monitoring "END_MONITORING"
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
where t.obj# =i.bo#
  and io.owner# = u.user#
  and io.obj# = i.obj#
  and u.name not in ('SYS','SYSTEM','XDB','WMSYS','ORDSYS','OUTLN','MDSYS','CTXSYS')
  and i.obj# =ou.obj#(+)
order by u.name, t.name, io.name;


This script stops index monitoring on an entire schema.(must be run as System)
set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool stop_monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
   from dba_indexes
   where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool stop_monitor_idx.log
@stop_monitor_idx.tmp
spool off


Index Usage without "alter index.... monitoring usage"
To get a solution for this, there is one pre requirement-AWR. AWR (Automatic Workload Repository), which is part of "Tuning and Diagnostic Packs", must be installed, what require additional licensing. AWR is snapshot repository of important database activity, so AWR should able to keep data with retention of minimum of 30 days (46 days are perfect) and those snapshots must be taken in appropriate interval.
After that you are able to use this script that shows all indexes in schema (defined with first parameter-&&1) which are bigger then a value (defined with scripts's second parameter-&&2). Remember that this will work over your "AWR Monitor Period". Save the file to some location.

/* ---------------------------------------------------------------------------
 Purpose : Shows index usage by execution (find problematic indexes)
 Date    : 22.01.2008.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Run as privileged user
           Must have AWR run because sql joins data from there works on 10g >       
           
           @index_usage SCHEMA MIN_INDEX_SIZE
           
 Changes (DD.MM.YYYY, Name, CR/TR#):         
          25.11.2010, Damir Vadas added index size as parameter
          30.11.2010, Damir Vadas fixed bug in query
--------------------------------------------------------------------------- */
ACCEPT OWNER PROMPT 'Schema_Name: '
ACCEPT SIZE_MB PROMPT 'SIZE in MB: '

set linesize 140
set pagesize 160

clear breaks
clear computes

break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB
compute sum of NR_EXEC on TABLE_NAME SKIP 2
compute sum of MB on TABLE_NAME SKIP 2

SET TIMI OFF
set linesize 140
set pagesize 10000
col OWNER noprint
col TABLE_NAME for a20 heading 'Table name'
col INDEX_NAME for a25 heading 'Index name'
col INDEX_TYPE for a10 heading 'Index|type'
col INDEX_OPERATION for a21 Heading 'Index operation'
col NR_EXEC for 9G999G990 heading 'Executions'
col MB for 999G990 Heading 'Index|Size MB' justify  right
        WITH Q AS (
                SELECT
                       S.OWNER                  A_OWNER,
                       TABLE_NAME               A_TABLE_NAME,
                       INDEX_NAME               A_INDEX_NAME,
                       INDEX_TYPE               A_INDEX_TYPE,
                       trunc(SUM(S.bytes) / 1048576)   A_MB
                  FROM DBA_SEGMENTS S,
                       DBA_INDEXES  I
                 WHERE S.OWNER =  upper('&&OWNER')
                   AND I.OWNER =  upper('&&OWNER')
                   AND INDEX_NAME = SEGMENT_NAME
                 GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
                HAVING SUM(S.BYTES) > 1048576 * &&SIZE_MB
        )
        SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
               A_OWNER                                    OWNER,
               A_TABLE_NAME                               TABLE_NAME,
               A_INDEX_NAME                               INDEX_NAME,
               A_INDEX_TYPE                               INDEX_TYPE,
               A_MB                                       MB,
               DECODE (OPTIONS, null, '       -',OPTIONS) INDEX_OPERATION,
               COUNT(OPERATION)                           NR_EXEC
         FROM  Q, DBA_HIST_SQL_PLAN d
         WHERE D.OBJECT_OWNER(+)= q.A_OWNER
           AND D.OBJECT_NAME(+) = q.A_INDEX_NAME
        GROUP BY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB,
               DECODE (OPTIONS, null, '       -',OPTIONS)
        ORDER BY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB DESC, NR_EXEC DESC;

 
PROMPT Showed only indexes in &OWNER schema whose size > &SIZE_MB MB in period:
SET HEAD OFF;
select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')
       || '-' ||
       to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY')
from dba_hist_snapshot;
 
SET HEAD ON
SET TIMI ON

This will show:
                                                                          Index
Table name           Index name                type        Size MB Index operation       Executions
-------------------- ------------------------- ---------- -------- --------------------- ----------
EXCEPTIONITEM        IN_EXCEPITEM_IMPDATE_INFS NORMAL           13        -                       0
                     OURID
******************** ************************* ********** --------                       ----------
sum                                                             13                                0

ISSUEDATA            IN_ISSUEDATA_IMPORTEDDATE NORMAL           43        -                       0
                     PK_ISSUEDATA              NORMAL           22        -                       0
                     UN_ISSUEDATA_BANKIDACCTSE NORMAL          265        -                       0
                     RIAL
******************** ************************* ********** --------                       ----------
sum                                                            330                                0

ISSUEDATA_ARCH       IN_ISSUEDATAARCH_ISSUEID  NORMAL        1,984        -                       0
******************** ************************* ********** --------                       ----------
sum                                                          1,984                                0

Showed only indexes in FG836_PNC_PROD schema whose size > 10 MB in period:
16.08.2011-24.08.2011


What is interesting here (keep in mind that monitoring period is 46 days!):
1- All indexes with "Executions" value=0 has never been used in monitored period. Reason may be poor quality (against underlying data) or change of CBO plans. These indexes are highly on the list for purge-especially if they are big or they are under table with big use (on other indexes i.e. NSO_PROD_I)!
2- Big indexes with small number of executions, especially those with just one execution, are really candidate to be purged and created/dropped for each execution
3- Indexes where FULL SCAN is using mode are candidates for rebuild or checking it's quality*
4- Tables which have many indexes with small index usage are candidates for relation model redesign
5- Indexes with huge number of executions should be observed a lot. Rebuild it when usage in next period decrease for more then 15%-25%.



Get Index Information
The idea of this script is to measure index quality.
If index has bad ratio of index_clustering_factor/table_rows it's usage may be worse then full table scan!
There are mainly two reasons why index has bad quality (without deeper coverage any of these terms):

/**********************************************************************
 * File:        Index_Info.sql
 * Type:        SQL*Plus script
 * Author:      Dan Hotka
 * Date:        04-16-2009
 *
 * Description:
 *      SQL*Plus script to display Index Statistics in relation to clustering factor
 *      Script originated from Jonathan Lewis
 * Modifications:
 *
 *********************************************************************/
set linesize 300
set pagesize 200
spool Index_Info.txt
SELECT i.table_name, i.index_name, t.num_rows, t.blocks, i.clustering_factor,
case when nvl(i.clustering_factor,0) = 0                       then 'No Stats'
     when nvl(t.num_rows,0) = 0                                then 'No Stats'
     when (round(i.clustering_factor / t.num_rows * 100)) < 6  then 'Excellent    '
     when (round(i.clustering_factor / t.num_rows * 100)) between 7 and 11 then 'Good'
     when (round(i.clustering_factor / t.num_rows * 100)) between 12 and 21 then 'Fair'
     else                                                           'Poor'
     end  Index_Quality,
     i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key,
     to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from user_indexes i, user_objects o, user_tables t
where i.index_name = o.object_name
  and i.table_name = t.table_name
order by 1;
spool off
-- exit

And here some details on how to read and analyze each case:

                            Table      Table            Index Data Blks Leaf Blks        Clust Index
Table                        Rows     Blocks Index    Size MB   per Key   per Key       Factor Quality
--------------------- ----------- ---------- ------ --------- --------- --------- ------------ -----------
NC_SUBSCRIPTION_SALDO  22,461,175     415103 NSO_I     560.00         9         1   21,749,007 1-Poor
This is classical representative of index with enormous clustering factor who may be a candidate for rebuild (very doubtful on first look) and if that doesn't help then recreate/drop should eb an option if not and pure drop!

 
                            Table      Table            Index Data Blks Leaf Blks        Clust Index
Table                        Rows     Blocks Index    Size MB   per Key   per Key       Factor Quality
--------------------- ----------- ---------- ------ --------- --------- --------- ------------ -----------
NC_TRANSACTION_ARZ_T    5,815,708     137742 TRA_I     184.00        12         1      222,104 5-Excellent
This is representative of high quality index who is unused (used only twice). For such an index I'll recommend deeper analyze for involved SQLs. But this is not a candidate for purge in any mean.


                           Table      Table             Index Data Blks Leaf Blks        Clust Index
Table                       Rows     Blocks Index     Size MB   per Key   per Key       Factor Quality
--------------------- ---------- ---------- ------- --------- --------- --------- ------------ -----------
NC_TRANSACTION_OK_T   24,492,333     851796 TROK_I     536.00         1         1   21,977,784 1-Poor
This index has never been used-highly recommended for purge!


                           Table      Table             Index Data Blks Leaf Blks        Clust Index
Table                       Rows     Blocks Index     Size MB   per Key   per Key       Factor Quality
--------------------- ---------- ---------- ------- --------- --------- --------- ------------ -----------
NC_TRANSACTION_ULAZ_TT   840,179      27437 TRUT_I     774.00         1         1      731,609 1-Poor
This index is pretty big (774 MB), with low index quality, and use only twice. Recommendation rebuild and, if that doesn't help, purge.



More information on Indexes
http://psoug.org/reference/indexes.html