Query is running slow with equal operator

Question

The following query is working fine (fast) when I use emp.status >= 412 or emp.status <= 412 or without emp.status filter, but when I use only equal operator, i.e. emp.status = 412, my query is running too slow.

What's the problem? Could it be some indexing issue? I have created an index on status field.

SELECT emp.empno FROM EMP_STATUS emp WHERE
     emp.empno in 
     (
      SELECT schemp.empno FROM  sched_emp schemp,sections scts 
      WHERE    schemp.SECTSCODE = scts.SECTSCODE   AND schemp.dep_code = scts.dep_code    AND scts.years=2014    
      AND schemp.dep_code = 2 
     )
     and emp.dep_code = 2
      and  emp.status  = 412

Have you ran and compared the EXPLAIN statements for both queries? They might provide valuable hints. — Erik, Dec 30 '14 at 08:54
Sounds like your table's statistics are not available or broken. The dbms thinks to look for >= 412 or <= 412 is not worth using an index, but = 412 is. But what if 412 makes for 90% of the table? Then an index is not appropriate and the statistics would tell the dbms so. — Thorsten Kettner, Dec 30 '14 at 09:09

score 0 · Answer 1 · edited May 23 '17 at 12:28

You can check the execution plan of each query and compare them to know in details what the engine is doing to execute each of them

If there is a long path in the equal case, which is supposed to be as long as you say it take so long

Then try to run Update Statistics on your table or on your entire database

This should optimize the execution plan based on the nature of data on your table

For more details about Execution Plans, check this

For more details about Update Statistics, check this

score 0 · Answer 2 · answered Dec 30 '14 at 10:27

Yes, it looks like indexing problem. Try to drop index on status field, 80% it will help. Alternatively, you should gather statistics with histograms.

Anyway, first you should do in such case - look at execution plan (EXPLAIN PLAN mentioned above). It sounds like your query is fast when using FULL TABLE SCAN, but slow if using INDEX access.

Query is running slow with equal operator

2 Answers2