SQL NOT IN statement Data is not retrieved Using Index

Question

I have simple query :

SELECT * FROM MH.APPOINTMENT WHERE DOCTOR_INC_ID = 1391791151

When I examine the execution plan, I see that the data is retrieved using index

However the following query :

SELECT * FROM MH.APPOINTMENT WHERE DOCTOR_INC_ID NOT IN (1391791151)

does not benefit from our index. We are using Oracle 11g Release2. Any suggestions is welcome. Thanks

Answer 1

When you live in USA, asking for "New York" and "Washington", you will identify the location easily. Because, they are indexed on your memory. Where-as, when asked for all cities except "New York", you will still have to fetch all the cities from your memory, which is obviously not as easy as previous!!!

May be I sound funny, but this is the concept of Index scan and full table scan.

Answer 2

It just won't pay up to use index for this kind of query - it's not selective enough. If the query is expected to retrieve one row (or small number of rows compared to table size, let's say 1%), then you can find the values very quickly by first searching an index, and then returning the associated rows from the actual table. But if the query is expected to return 99% of the rows, then it just doesn't make sense to search for them in an idex, and then retrieving associated rows - it's too much work. Instead the engine goes straight for the table scan.