How to know whether a query is using index or not?

You can use the index monitoring feature to check if indexes are used by an application or not. If u set the MONITORING USAGE property for an index, you can query the v$object_usage to see if the index is being used or not. Here is an example:


sys@BBK> CREATE INDEX t1_idx ON t1(c1);

Index created.

sys@BBK> ALTER INDEX t1_idx MONITORING USAGE;

Index altered.

sys@BBK> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME INDEX_NAME MON USE
------------------------------ ------------------------------ --- ---
T1 T1_IDX YES NO

sys@BBK> SELECT * FROM t1 WHERE c1 = 1;

no rows selected

sys@BBK> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME INDEX_NAME MON USE
------------------------------ ------------------------------ --- ---
T1 T1_IDX YES YES

sys@BBK> ALTER INDEX t1_idx NOMONITORING USAGE;

Index altered.

sys@BBK> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME INDEX_NAME MON USE
------------------------------ ------------------------------ --- ---
T1 T1_IDX NO YES

sys@BBK>

OR use the following way;

sql> set autot on exp
sql> SELECT * FROM t1 WHERE c1 = 1;