Optimizer execution-plan problem

From: Stefan Olausson <qhsstol_at_hotmail.com>
Date: 1 Nov 2001 05:05:01 -0800
Message-ID: <fda9c411.0111010505.53c9c3ed@posting.google.com>

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, Received on Thu Nov 01 2001 - 07:05:01 CST