SQL conditions - use of NOT

[slimdave]

It seems intuitively correct that if you have a large table having a highly-skewed, indexed, histogram-analyzed column of values "A" and "B", like ...

select my_col,count(*)
from my_table
group by my_col;

MY_COL COUNT(*)
------ --------
A      99999999
B      1

.. then Oracle would use the index for predicates like ...

MY_COL='B'
... or ...
MY_COL!='A'
... or ...
MY_COL='C'

... and would use FTS for predicates like ...

MY_COL='A'
... or ...
MY_COL!='B'

Unfortunately, I haven't been able to put together a test case to demonstrate it, so maybe theory don't apply here.

[slimdave]

Actually, rethinking it i bet that the != would be much more likely to use an index in a star transformation, involving bitamp index merges etc.

[abhaysk]

Originally posted by slimdave

select my_col,count(*)
from my_table
group by my_col;

MY_COL COUNT(*)
------ --------
A      99999999
B      1

.. then Oracle would use the index for predicates like ...

MY_COL='B'
... or ...
MY_COL!='A'
... or ...
MY_COL='C'

... and would use FTS for predicates like ...

MY_COL='A'
... or ...
MY_COL!='B'

I doubt only for that highlighted part...

PS

TEST:ABHAY> Set trimspool on
TEST:ABHAY> analyze table test compute statistics;

Table analyzed.

TEST:ABHAY> ed
Wrote file afiedt.buf

  1  select
  2    INDEX_OWNER     ,
  3    INDEX_NAME      ,
  4    TABLE_OWNER     ,
  5    TABLE_NAME      ,
  6    COLUMN_NAME     ,
  7    COLUMN_POSITION
  8* from dba_ind_columns where index_name='AK_TEST'
TEST:ABHAY> /

INDEX_OWNER                    INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
COLUMN_NAME
-----------------------------------------------
     COLUMN_POSITION
--------------------
ABHAY                          AK_TEST                        ABHAY                          TEST
ID4
                   1

ABHAY                          AK_TEST                        ABHAY                          TEST
ID2
                   2

TEST:ABHAY> set autotrace on explain
TEST:ABHAY> alter session set optimizer_mode=ALL_ROWS;

Session altered.

TEST:ABHAY> select ID4, Count(*) from TEST group by ID4;

                 ID4             COUNT(*)
-------------------- --------------------
                   1              1099799
                   2                   13


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1673 Card=2 Bytes=
          4)

   1    0   SORT (GROUP BY) (Cost=1673 Card=2 Bytes=4)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1099812 Byt
          es=2199624)


TEST:ABHAY> select * from test where id4=2 and id2=1999991;

                  ID                  ID1                  ID2                  ID4                  ID5
-------------------- -------------------- -------------------- -------------------- --------------------
             1999989              1999990              1999991                    2                    1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=18)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Byt
          es=18)

   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (SINGLE VALUE) OF 'AK_TEST'


TEST:ABHAY> select * from test where id4!=1 and id2=1999991;

                  ID                  ID1                  ID2                  ID4                  ID5
-------------------- -------------------- -------------------- -------------------- --------------------
             1999989              1999990              1999991                    2                    1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=359 Card=1 Bytes=1
          8)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1 Bytes=18)

You can see its going for FTS with ID4!=1

TEST:ABHAY> select /*+ INDEX(TEST AK_TEST) */ * from test where id4!=1 and id2=1999991;

                  ID                  ID1                  ID2                  ID4                  ID5
-------------------- -------------------- -------------------- -------------------- --------------------
             1999989              1999990              1999991                    2                    1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9734 Card=1 Bytes=
          18)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=9734 Card=1
          Bytes=18)

   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (FULL SCAN) OF 'AK_TEST'
compare the costs with index scan and without for != condition

!= condition will always call for FULL scans (Table/Index), and oracle feels FULL Table scan is wiser than FULL Index scan, as you know FTS can fetch DFMC blocks in one I/O which FULL index scan dosent..

Ok, only exception will be if Index blocks is much less than Table blocks consumed. I would guess a Table with high number COLS present and indexed on only few COLS, than Index Scan would be wiser.

Abhay.