Oracle Scratchpad

October 15, 2010

Good Nulls

Filed under: CBO,Function based indexes,Indexing,NULL,Tuning — Jonathan Lewis @ 6:17 pm BST Oct 15,2010

I’ve often been heard to warn people of the accidents that can happen when they forget about the traps that appear when you start allowing columns to be NULL – but sometimes NULLs are good, especially when it helps Oracle understand where the important (e.g. not null) data might be.

An interesting example of this came up on OTN a few months ago where someone was testing the effects of changing a YES/NO column into a YES/NULL column (which is a nice idea because it allows you to create a very small index on the YESes, and avoid creating a histogram to tell the optimizer that the number of YESes is small).

They were a little puzzled, though, about why their tests showed Oracle using an index to find data in the YES/NO case, but not using the index in the YES/NULL case. I supplied a short explanation on the thread, and was planning to post a description on the blog, but someone on the thread supplied a link to AskTom where Tom Kyte had already answered the question, so I’m just going to leave you with a link to his explanation.

January 13, 2009

FBI Stats

Filed under: CBO,Function based indexes,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 7:29 pm BST Jan 13,2009

It’s very easy to forget that 10g creates statistics on an index as it builds (or rebuilds) the index – and this can lead to some oddities when you add a function-based index (or, as I tend to name them, index on virtual columns) to a table.

This point (along with a couple of other observations) came up in a discussion on OTN a little while ago, with some useful responses from Richard Foote (the “index king”), Christian Antognini (author of  “Trouble-shooting Oracle Performance”) and a few others, including me.

« Previous Page

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 5,383 other followers