1. it depends (as usually) something to read about
DECODE function, Oracle considers two nulls to be equivalent. If
expr is null, then Oracle returns the
result of the first
search that is also null.
otherwise null is used for undefined and you cannot compare two undefined things.
> 1011786 wrote:
> 1. Under what circumstances are indexes not used by Oracle while processing queries?
CBO has to consider lot of factors before determining a access path.
When a index will not be used? To put it simple i will give 2 case (There are lot more)
1. When the index column value is transformed in a Query, By using a function or expression
where to_char(empid, '0999999999') = '0000000001'
In this case a normal index on empid will not be considered. Because the column value of empid is transformed to a different value.
2. When the I/O needed to get a row is more in Index scan than Full table scan. In this case also index will not be used. Consider you writing a query like this
where empid between 1 and 9000
And you have only 10000 employees. This means you are picking 90% of the data from emp table. So if Oracle uses the Index on empid to reach to the rows of emp table then it will have to perform more I/O than just doing a full table scan.
If you are interested in going deep into the internals of index I would suggest Richard Foote's Blog