Turning off an index in Oracle

Question

To optimize SELECT queries, I run them both with and without an index and measure the difference. I run a bunch of different similar queries and try to select different data to make sure that caching doesn't throw off the results. However, on very large tables, indexes take a really long time to create, and I have several different ideas about what indexes would be appropriate.

Is it possible in Oracle (or any other database for that matter) to perform a query but tell the database to not use a certain index when performing the query? Or just turn off the index entirely, but be able to easily switch it back on without having to re-index the entire table? This would make it much easier to test, since I can create all the indexes I'm thinking about all at once, then try my queries using different ones.

Alternatively, is there any better way to go about optimizing queries on large tables and know which indexes would be best to create?

Answer 1

You can use the NO_INDEX hint in the queries to ignore the indexes - see docs for further details. The SQL Access Advisor is an Oracle utility that will recommend indexing strategies.

Answer 2

You can set index visibility in 11g -

ALTER INDEX idx1 [ INVISIBLE | VISIBLE ]

this makes it unusable by the optimizer, but oracle still updates the index when data is added or removed. This makes it easy to test performance with the index disabled without having to remove & rebuild the whole index.

See here for the oracle docs on index visibility

Answer 3

Well you can write the query in such a way that it wont use index(using expression instead of a value)

For example

Select * from foobar where column1 = 'result'  --uses index on column1

To avoid using index for a number and varchar

Select * from foobar where column1 + 0 = 5 -- simple expression to disable the index

Select * from foobar where column1 || '' = 'result' --simple expression to disable the index

Or you can just use NVL to disable the index in the query without worrying about the column's data type

Select * from foobar where nvl(column1,column1) = 'result' --i love this way :D

Similarly you can use index hints

like /* Index(E employee_id) */ to use indexes. P.S. This is all the paraphrased from Dan Tow's Book SQL Tuning. I started reading it a few days back :)