Originally posted by DreamWarrior
It is entirely relevent, read my above post. What would you like Oracle to do, scan the entire Index and remember everything IN the table where your != statement is false, then go back to read the entire table, but only dispatch rows that were not remembered from the index scan? Sounds like double duty to me.
I dont know a bit what are you talking, do you know how will index search work?

Well its not that "scan the entire Index and remember everything IN the table where your != statement is false, then go back to read the entire table, but only dispatch rows that were not remembered from the index scan? Sounds like double duty to me"

But,Its some thing like this "It will return those rows(ROWIDS) which dont match the value spicfied." Does this statement sound correct?

Originally posted by DreamWarrior
If you ask Oracle to:

Select from table where column != value.

or

Select from table where column NOT IN (value1, value2, etc.)

Then an index on column can NOT BE USED!
Just see the behaviour of the index usage, you can see the response time is high for FULL SCAN of Index
and then return ROWIDS that dont match & then eventually the data being picked up from the table.

Rather it would be easy for Oracle to do FTS and return non matched values.

Code:
TEST_ABHAY> set autotrace on explain
TEST_ABHAY> select * from test where ID4!=1;

                  ID                  ID1                  ID2                  ID4
-------------------- -------------------- -------------------- --------------------
             1999989              1999990              1999991                    2
             1999990              1999991              1999992                    2
             1999991              1999992              1999993                    2
             1999992              1999993              1999994                    2
             1999993              1999994              1999995                    2
             1999994              1999995              1999996                    2
             1999995              1999996              1999997                    2
             1999996              1999997              1999998                    2
             1999997              1999998              1999999                    2
             1999998              1999999              2000000                    2
             1999999              2000000              2000001                    2
             2000000              2000001              2000002                    2
                  11                                                              2

13 rows selected.

Elapsed: 00:00:02.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=359 Card=549946 Byte
          s=9349082)

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

TEST_ABHAY> select /*+ INDEX(TEST AK_TEST) */ * from test where ID4!=1;

                  ID                  ID1                  ID2                  ID4
-------------------- -------------------- -------------------- --------------------
             1999989              1999990              1999991                    2
             1999990              1999991              1999992                    2
             1999991              1999992              1999993                    2
             1999992              1999993              1999994                    2
             1999993              1999994              1999995                    2
             1999994              1999995              1999996                    2
             1999995              1999996              1999997                    2
             1999996              1999997              1999998                    2
             1999997              1999998              1999999                    2
             1999998              1999999              2000000                    2
             1999999              2000000              2000001                    2
             2000000              2000001              2000002                    2
                  11                                                              2

13 rows selected.

Elapsed: 00:00:03.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9734 Card=549946 Byt
          es=9349082)

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

   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (FULL SCAN) OF 'AK_TEST'
Abhay.