| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Not null and index
On Sep 19, 5:27 am, "Richard Foote" <richard.fo..._at_nospam.bigpond.com>
wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
>
> news:1190148492.574605.280970_at_g4g2000hsf.googlegroups.com...
> > On Sep 18, 3:31 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> >> Hi,
>
> >> I have a table with a column with a constraint not null
> >> and a index on this column
>
> >> there are stats ion the table
>
> >> when I do:
>
> >> select * from test where no is null
>
> >> the plan is using the index, why ?
>
> >> Thanks in advance ...
>
> >> 9.2.0.6
>
> > Please provide a test case. This was performed on Oracle 10.2.0.2:
> > CREATE TABLE T1 (
> > C1 NUMBER(10) NOT NULL,
> > C2 NUMBER(10));
>
> > Table created.
>
> > INSERT INTO T1
> > SELECT
> > ROWNUM,
> > ROWNUM*2
> > FROM
> > DUAL
> > CONNECT BY
> > LEVEL<=100000;
>
> > 100000 rows created.
>
> > CREATE INDEX T1_IND1 ON T1(C1);
>
> > Index created.
>
> > EXEC
> > DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE);
>
> > We now have a table with a column that has a NOT NULL constraint, an
> > index on that column, and up to date statistics on the table and
> > index.
>
> > Test run:
> > SELECT /*+ GATHER_PLAN_STATISTICS */
> > *
> > FROM
> > T1
> > WHERE
> > C1 IS NULL;
>
> > no rows selected
>
> > SELECT
> > *
> > FROM
> > TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
>
> > ---------------------------------------------------------------------------
> > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > Time |
> > ---------------------------------------------------------------------------
> > |* 1 | FILTER | | 1 | | 0 |
> > 00:00:00.01 |
> > | 2 | TABLE ACCESS FULL| T1 | 0 | 100K| 0 |
> > 00:00:00.01 |
> > ---------------------------------------------------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> > 1 - filter(NULL IS NOT NULL)
>
> > As expected, the plam shows that the index was not used. Note the
> > filter predicate.
>
> > So, what if we force the use of the index through the use of a hint:
> > SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 T1_IND1) */
> > *
> > FROM
> > T1
> > WHERE
> > C1 IS NULL;
>
> > no rows selected
>
> > SELECT
> > *
> > FROM
> > TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
>
> > ----------------------------------------------------------------------------------------
> > | Id | Operation | Name | Starts | E-Rows | A-
> > Rows | A-Time |
> > ----------------------------------------------------------------------------------------
> > |* 1 | FILTER | | 1 |
> > | 0 |00:00:00.01 |
> > | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 0 |
> > 100K| 0 |00:00:00.01 |
> > | 3 | INDEX FULL SCAN | T1_IND1 | 0 |
> > 100K| 0 |00:00:00.01 |
> > ----------------------------------------------------------------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> > 1 - filter(NULL IS NOT NULL)
>
> > Oracle used the index specified, yet NULL values are not indexed - and
> > it apparently completed in the same amount of time as the full table
> > scan.
>
> Hi Charles
>
> The point that appears to have missed regarding all this is that the column
> in question has been created with a *NOT NULL* constraint.
>
> How many null values are going to be returned from a column that has an
> enabled, validated and non deferrable not null constraint ? The answer
> *must* be zero!!
>
> Now Oracle is clever enough to realise this, although how clever depends on
> the Oracle version ...
>
> With 9i, if the column has an index and if the not null constraint is
> enabled, validated and non deferrable, then Oracle will use this index to
> "fast-track" the point there can be no rows and quickly returns that fact
> via the index. If it doesn't have an index then it performs a redundant,
> unnecessary and potentially expensive full table scan to return now rows. If
> the state of the index is not as described as above, then there's a chance
> there could be null values and again performs a FTS.
>
> I remember showing a quick demo of this a few years ago on AskTom (search
> for Foote athttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8...)
>
> The OP is on 9.2.0.6 hence why the Index Access Path is being used.
>
> However, with 10g, the CBO has got a little smarter. With a not null
> constraint (meeting the above conditions), again Oracle knows there can be
> no null columns and acts accordingly. Although the execution plan above
> suggests a FTS is being performed, in actual fact there are *no* LIOs being
> performed. Oracle basically says "wait a minute, why bother getting any
> blocks for this, there can be no rows" and simply returns the no rows
> message. The execution plan is simply a "default" path that isn't actually
> used.
>
> With 10g, there is no need for the index, it's now redundant and the zero
> I/O path is determined regardless. This can be confirmed by looking at a
> trace of a session (like the demo above which is obviously on 10g) which
> says a FTS is being performed, but in actuality is kinda a big lie as no
> LIOs are accessed!!
>
> As a final aside, note that it's very easy to index null values.
>
> A table can have a column with many many rows but with only a handful of
> null values. The following SQL can return these handful of rows via an
> index:
>
> SELECT * FROM really_big_table
> WHERE col is NULL
>
> as one would hope, without having to change the code at all (say to some nvl
> function-based search).
>
> Happy to disclose how if anyone is interested and doesn't know how ...
>
> Cheers
>
> Richard
Richard,
Thanks for the additional information. I should have carried the test case a bit further to see why the timings were reported the same with and without the index hint. I don't think that I missed the point of the NOT NULL constraint and the SQL statement requesting all rows that could not exist in the table with the constraint enabled - I suspected that there was an optimization, or intentional short circuiting (possibly indicated by filter(NULL IS NOT NULL)), but was not aware that this optimization varied between 9i and 10g. Your explanation is very clear and helpful.
Just for fun:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
WHERE
C1 IS NULL;
>From the trace file:
PARSE #35:c=0,e=1278,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3792793360
EXEC #35:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3792794137
WAIT #35: nam='SQL*Net message to client' ela= 5 driver id=1413697536
#bytes=1 p3=0 obj#=355 tim=3792794234
FETCH #35:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3792794338
WAIT #35: nam='SQL*Net message from client' ela= 582 driver
id=1413697536 #bytes=1 p3=0 obj#=355 tim=3792795058
STAT #35 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0
time=4 us)'
STAT #35 id=2 cnt=0 pid=1 pos=1 obj=57452 op='TABLE ACCESS FULL T1
(cr=0 pr=0 pw=0 time=0 us)'
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 T1_IND1) */
*
FROM
T1
WHERE
C1 IS NULL;
>From the trace file:
PARSE #33:c=0,e=1395,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3799201886
EXEC #33:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3799202655
WAIT #33: nam='SQL*Net message to client' ela= 5 driver id=1413697536
#bytes=1 p3=0 obj#=355 tim=3799202754
FETCH #33:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3799202854
WAIT #33: nam='SQL*Net message from client' ela= 459 driver
id=1413697536 #bytes=1 p3=0 obj#=355 tim=3799203445
STAT #33 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0
time=5 us)'
STAT #33 id=2 cnt=0 pid=1 pos=1 obj=57452 op='TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us)' STAT #33 id=3 cnt=0 pid=2 pos=1 obj=57453 op='INDEX FULL SCAN T1_IND1(cr=0 pr=0 pw=0 time=0 us)'
p=0,cr=0,cu=0 - no physical IO, no consistent read, no current mode read - just as you stated.
How would you index NULL values? Function based index?
Thanks again for your contribution.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Sep 19 2007 - 06:14:21 CDT
 |
 |