SQL NOT IN statement Data is not retrieved Using Index
indexing, oracle, sql
Solution
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.
Problem
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