As we mentioned in Indexes, you may have to adjust the base starting index set to suit your operational environment. You can find out what indexes are used (and by corollary, which ones are not used) through index monitoring. To enable index monitoring, issue the following commands, one for each index:
...
alter index yfs_order_header_pk monitoring usage;
alter index yfs_order_header_i1 monitoring usage;
alter index yfs_order_header_i2 monitoring usage;
...
You can generate the command above by issuing the following query:
select 'alter index ' || index_name || ' monitoring usage;'
from user_indexes;
Periodically, as you run your functionality and system test, you can run the following query to see if which indexes have been used and which have not yet been used:
select index_name, monitoring, used, start_monitoring
from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING
------------------- ---------- ---- -------------------
YFS_ORDER_HEADER_I1 YES YES 01/29/2011 01:23:03
To turn off index monitoring, issue the following command:
alter index yfs_order_header_i1 nomonitoring usage;