Re: Optimizer execution-plan problem

Stefan Olausson wrote:
>
> Hi guys,
>
> We have a performance problem, with an SQL-query
> where Oracle-server insists on using a full tablescan.
> It looked like a trivial thing to fix,
> but I have not yet been able to.
>
> I found the problem we have can be reduced to the
> following simple case:
>
> SQL> CREATE TABLE tmp_n(a NUMBER, b NUMBER);
> SQL> CREATE INDEX tmp_n_ix ON tmp_n(a,b);
> SQL> SELECT MIN(b) FROM tmp_n WHERE a IS NULL;
>
> I think the index seems tailor-made for this SQL-query
> but the optimizer (both RBO and CBO) insists on a full
> table-scan:
>
> SELECT STATEMENT
> + SORT (AGGREGATE)
> + + TABLE ACCESS (FULL) OF TMP_N
>
> If I change the WHERE condition slightly:
>
> SQL> SELECT MIN(b) FROM tmp_n WHERE a = 0;
>
> it WILL use the index, as I want:
>
> SELECT STATEMENT
> + SORT (AGGREGATE)
> + + FIRST ROW
> + + + INDEX (RANGE SCAN (MIN/MAX)) OF TMP_N_IX (NON-UNIQUE)
>
> But why doesnt it work the same with "WHERE a IS NULL" ?
> Indexes cannot be used to find NULL-values or what ?
> Any optimizer-guru here who can help me out?
>
> Best Regards,

NULL is not a value - it is a "state", ie the absence of information. For example, assume you have an address book (ie indexed by address).

If you are looking for someone who has no address (ie null), then they could conceivably live in every possible address (or none of them). So you're up for a full scan

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."