13.2 Indexes

---

Many factors affecting indexes and types of indexes were discussed in detail in Chapter 7 including index compression. Even though there is some crossover with the previous section on tables there are a few other factors to consider.

13.2.1 Monitoring

The simplest use for monitoring is to tell if an index is used or not. When an index is monitored as being used the V$OBJECT_ USAGE.USED column will contain a YES value. An index not used after monitoring over a period of time could possibly be dropped; preferably do not drop foreign key indexes.

Tip 

Monitoring applies to both tables and indexes. However, when testing on my database I got absolutely no sensible response to monitoring of tables for DML activity.

Let's experiment. This query creates a script allowing monitoring of indexes for all the tables in the Accounts schema.

DECLARE        CURSOR cIndexes IS             SELECT index_name FROM user_indexes             WHERE index_type != 'LOB'; BEGIN        FOR rIndex IN cIndexes LOOP             EXECUTE IMMEDIATE 'ALTER INDEX                 '||rIndex.index_name ||' MONITORING                      USAGE';       END LOOP; END; /

The result will be something like this:

ALTER INDEX XFK_COA# MONITORING USAGE; ALTER INDEX XFK_SM_STOCK MONITORING USAGE; ALTER INDEX XPK_GENERALLEDGER MONITORING USAGE; ALTER INDEX XPK_STOCKMOVEMENT MONITORING USAGE; ¼ 

Now I change all these tables with the previous script and then execute my DBMS_JOBS package scripting to start up my highly active concurrent OLTP database. After that I query the V$OBJECT_USAGE performance view and see if I can find any unused indexes.

SQL> SELECT COUNT(*) FROM v$object_usage WHERE used = 'NO';        COUNT(*) -----------     42

Many of the indexes I have in my Accounts schema are not used.

Tip 

Foreign key indexes will not be flagged as used when utilized for validation of Referential Integrity. Be careful not to remove foreign key indexes. You will get a lot of performance problems if you do. When foreign keys are not indexed, table exclusive locks are extremely likely. Most of the unused indexes in my Accounts schema are foreign key indexes.

I could use a query such as this to find exact details of monitoring.

SELECT start_monitoring "Start", end_monitoring "End",     used ,index_name "Index" FROM v$object_usage WHERE used = 'NO'; 

It is important to tuning to drop unused indexes because a DML operation executed on a table results in a DML operation on the table plus all its indexes. The fewer the objects requiring changes the faster DML activity will be. Note that indexing usually speeds up data retrieval so it is sensible to weigh between DML, SELECT activity and the need for foreign key indexes when deciding on the necessity for an index.

13.2.2 Index Parallelism

Index creations and rebuilds can be executed in parallel. There are three indexes on the Accounts.GeneralLedger table. Let's rebuild one of them.

SQL> ALTER INDEX xfk_coa# REBUILD;     Index altered.     Elapsed: 00:00:30.00 

Now let's rebuild in parallel.

SQL> ALTER INDEX xfk_coa# REBUILD PARALLEL 2;     Index altered.     Elapsed: 00:02:56.04 

It appears that the parallel rebuild is quite a lot slower. Perhaps there is something very wrong with my machine, perhaps not!

Let's try creating the index from scratch, both serially and in parallel. First serially.

SQL> DROP INDEX xfk_coa#;     Index dropped.     Elapsed: 00:00:00.04     SQL> CREATE INDEX xfk_coa# ON generalledger(coa#);     Index created.     Elapsed: 00:05:24.01 

Secondly, let's create the index in parallel.

SQL> DROP INDEX xfk_coa#;     Index dropped.     Elapsed: 00:00:00.01     SQL> CREATE INDEX xfk_coa# ON generalledger(coa#) PARALLEL 2;     Index created.     Elapsed: 00:06:02.06 

Once again parallel processing on my two CPU machine is slower and once again my dual CPU box has limited RAM, limited disk capacity and I/O performance. It is possible that a combination of these factors causes parallel processing to be abnormally slow. Then again parallel processing requires extra processing and coordination between different processes. In my experience parallelism is often only effective in very large databases on Unix machines with at least four CPUs, or when Oracle Partitioning is utilized.

13.2.3 Fragmentation and Coalescing

Indexes can become defragmented, perhaps by large amounts of deletion. Space in index leaf blocks is not reclaimed for reuse. Coalescence of indexes can help to alleviate loss of space by attempting to merge neighboring blocks together where those blocks are empty.

ALTER INDEX index COALESCE;

Be aware that it is more effective to reclaim index space by rebuilding the index: slower but much more effective. Also indexes can be rebuilt with the ONLINE option. The ONLINE option does not disrupt DML activity using that index.

ALTER INDEX index REBUILD ONLINE;

---