0

I am trying to understand how multiple conditions are executed in 'where clause', If I query a table and in the where condition if I have built in function with 'and' operator if I have some simple filter condition on columns, again a select query which is the best possible way it would take minimum time.

1.17sec to execute:

SELECT count(col1) FROM table1 idbs WHERE TRUNC(idbs.DATE) BETWEEN TO_DATE('01/01/2000','mm/dd/yyyy') AND TO_DATE('08/11/2016','mm/dd/yyyy') and COL2 <> 'Y' AND COL3 = 'N' AND COL4 <> 36 AND (var IS NULL OR idbs.COL5 IN( (SELECT regexp_substr(var,'[^|]+', 1, LEVEL) FROM dual CONNECT BY regexp_substr(var,'[^|]+', 1, LEVEL) IS NOT NULL)));

Below query took 1.207 sec to execute:

SELECT count(col1) FROM table1 idbs WHERE COL2 <> 'Y' AND COL3 = 'N' AND COL4 <> 36 AND TRUNC(idbs.DATE) BETWEEN TO_DATE('01/01/2000','mm/dd/yyyy') AND TO_DATE('08/11/2016','mm/dd/yyyy') AND (var IS NULL OR idbs.COL5 IN( (SELECT regexp_substr(var,'[^|]+', 1, LEVEL) FROM dual CONNECT BY regexp_substr(var,'[^|]+', 1, LEVEL) IS NOT NULL))) ;

I have just rearranged the conditions but the time I see after execution is slightly different.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
mirage
  • 1
  • 2
  • Please edit the question to add significant data, partly so you can format it properly. If you're running two queries show both of them, along with their execution plans, and the execution time for both. You may just be seeing the effect of block caching. (Truncating dates like that will prevent any index on that column being used, incidentally). – Alex Poole Aug 11 '16 at 11:52
  • Put all of those comments into the question and format the code (and then delete the comments). – Alex Poole Aug 11 '16 at 12:00
  • Is the difference of three hundredths of a second (which is nothing really, and could be down to a lot of factors like server load) actually consistently repeatable, or do the times swap about if you run them both multiple times? It looks like natural variation; sometimes one will be fractionally faster, sometimes the other will. Look at the execution plans - I'm pretty sure they will be identical. – Alex Poole Aug 11 '16 at 12:02
  • I tried both the queries and the execution plan were same. Could not understand the execution plan , but the cost for both of them are same. Thank you Alex!! – mirage Aug 11 '16 at 12:11

0 Answers0