Geek Answers Handbook

SQL NOT IN statement Data cannot be retrieved Index usage

I have a simple query:

SELECT * FROM MH.APPOINTMENT WHERE DOCTOR_INC_ID = 1391791151

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

However, the following query:

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

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

+6
source share
2 answers

He simply will not pay to use the index for this kind of query - he is not selective enough. If the query is expected to retrieve one row (or a small number of rows compared to the size of the table, say 1%), then you can quickly find the values ​​by first searching the index and then returning the related rows from the actual table, But if expected Since the query returns 99% of the lines, it just doesn’t make sense to look for them in idex and then extract the related lines - this works too much. Instead, the engine goes straight to scan the table.

+7
source

When you live in the USA, asking New York and Washington, you can easily identify the place. Because they are indexed in your memory. Where, when, when asked about all the cities except “New York”, you still have to remove all the cities from your memory, which is obviously not as simple as before.

I may sound funny, but this is the concept of index scanning and full table scanning.

+15
source

More articles:


All Articles