Tom Kyte is one of the best in the business when it come to Oracle and is the Tom that many people rely on at Ask Tom. Here is a link to an article that has him talking about some common myths about Oracle indexing. I get questions on these exact things often. I even have people who have worked with Oracle for years that try to tell me that some of these myths are true. I don’t always have time to provide them examples to back up my point so having Tom out there to come in and settle the dispute is nice.
Here is a quick summary of his tips
- Nulls are not completely stored in B*Tree indexes – (Null, Null) in one row is not the same as (Null, Null) in another row – by definition Null is not equal to another null. When you aggregate it thought they group together. Also – if you have an index that is based on all NULLABLE columns and you do a query looking where one or more of the columns are null – the index cannot be used
- Views are indexed by indexing the base tables the view is based off of – the optimizer will break the view down and develop an execution plan based on the base tables
- Unindexed foreign keys are the biggest cause of deadlocks that Tom sees – so index them!
- 6 most common reasons your index is not being used – nothing earth-shatteringly new here, but great things to remember
- Index space IS reused (always) and can be automatically reclaimed (under certain conditions)
- The column with the highest selectivity (most distinct values) need not be the first column in the index – from a space perspective they use the same space – the most important thing to consider most of the time is HOW you query the table – without understanding the most common access methods – effectively indexing a table is difficult. Also he points out that indexes that go from least selective to most are more compressible, save space, and reduce I/O making the case if you are using Index Key compression to go that route for building your indexes.