Oracle and Indexes – Some tips

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.


Technorati tags: ,
This entry was posted in Technology. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s