Drop indexes that have never been used

Here is the plan:


1. Identify the indexes that are not appeared in the execution plan


create table index_drop_candidates
as
select owner, index_name
from dba_indexes
where owner='INDEXOWNER'
minus
(
select object_owner, object_name
from v$sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
union
select object_owner, object_name
from dba_hist_sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
);


2. Exclude those indexes used for constraints


delete from index_drop_candidates
where index_name in
(
select constraint_name from dba_constraints where owner='INDEXOWNER' and constraint_type in ('P', 'U', 'R') ) ;



3. Run the following delete statement manually or put in the cron from time to time for some time

delete from index_drop_candidates
where index_name in
(
select object_name
from v$sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
union
select object_name
from dba_hist_sql_plan
where object_type ='INDEX'
and object_owner='INDEXOWNER'
);


4. After some time, change those indexes still in the index_drop_candidates table to be invisible to observe any performance impact and decide if drop them accordingly



Note: There are cases that indexes have never been used in the execution plan, but they are needed to help CBO to choose a better plan. If such indexes are dropped, performance could be negatively impacted.

Monitoring Index Usage

1. Enable monitoring

ALTER INDEX [index_name] MONITORING USAGE;

2. View the infomation

col owner format a10
col index_name format a20
col monitoring format a10
col used format a6
 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')
 and
    i.obj# = ou.obj#(+)
 and t.name='TAB';

Note: v$object_usage has to be queried by connecting as specific schema user.
The above query can show info for indexes under different schema.

3. Check whether the index is monitorred or not

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = [index_name]
 and   table_name= [table_name];

Create index -- Parallel Option Syntax

SQL> create table t as select * from all_objects where rownum < 20;

Table created.

SQL> create index owner_idx on t(owner)
2 parallel (degree 8)
3 ;

Index created.

SQL> select index_name, degree from user_indexes where index_name='OWNER_%';

no rows selected

SQL> select index_name, degree from user_indexes;

INDEX_NAME DEGREE
------------------------------ ----------------------------------------
OWNER_IDX 8
SYS_C006454 1
SYS_C006455 1

SQL> alter index OWNER_IDX parallel (degree 1);

Index altered.

SQL> select index_name, degree from user_indexes;

INDEX_NAME DEGREE
------------------------------ ----------------------------------------
OWNER_IDX 1
SYS_C006454 1
SYS_C006455 1

Concepts - Index

Oracle provides:
- B-tree indexes
- B-tree cluster indexes
- Hash cluster indexes
- Reverse key indexes
- Bitmap indexes
- Bitmap Join indexes

Performance:

However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table

Function-based index

- A function-based index computes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index.

Example 1

CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

can facilitate processing queries such as this:SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD';

Example 2

For example, if you create the following index:CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);

then Oracle can use it when processing queries such as this:
SELECT a FROM table_1 WHERE a + b * (c - 1) <>The B-tree structure has the following advantages:
- All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time.
- B-tree indexes automatically stay balanced.
- All blocks of the B-tree are three-quarters full on the average.
- B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
- Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
- B-tree performance is good for both small and large tables and does not degrade as the size of a table grows.


Index Unique Scan
- used for returning the data from B-tree indexes.
- The optimizer chooses a unique scan when all columns of a unique (B-tree) index are specified with equality conditions.

Reverse Key Indexes

- reverses the bytes of each column indexed (except the rowid) while keeping the column order.

- Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index,

- only fetch-by-key or full-index (table) scans can be performed.

Bitmap index

- Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid

- Not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. Intended for decision support in data warehousing applications where users typically query the data rather than update it.

- Not suitable for columns that are primarily queried with less than or greater than comparisons. Useful for AND, OR, NOT, or equality queries.

- The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table.