Home » SQL & PL/SQL » SQL & PL/SQL » Order of where clause in SQL query. (merged 3)
Order of where clause in SQL query. (merged 3) [message #403232] Thu, 14 May 2009 06:51 Go to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Hi,

I have the following set of queries.

SELECT   'coke01'                                       AS "Server", 
         To_char(b.logon_time,'DD-MON-YYYY')            AS "Days", 
         Decode(e.user_area,NULL,'Nilesen',e.user_area) AS "User_Area", 
         Count(DISTINCT b.username)                     AS "# of Distinct Users on Sys", 
         Count(b.username)                              AS "# of total connect of sys", 
         Round((Sum(b.min_duration) / 60),2)            AS "Total connect time in hours" 
FROM     audit_log_duration b, 
         dbnk_user e 
WHERE    Upper(b.username) = Upper(e.user_id) 
         AND b.cstmer_id = e.cstmer_id 
         AND Trunc(b.logon_time) >= To_date('01-JUN-2008','DD-MON-YYYY') 
         AND Trunc(b.logoff_time) <= To_date('01-DEC-2008','DD-MON-YYYY') 
         AND b.cstmer_id = 6430 
GROUP BY To_char(b.logon_time,'DD-MON-YYYY'), 
         user_area;[color=red]

SELECT   'coke01'                            AS "Server", 
         To_char(b.logon_time,'DD-MON-YYYY') AS "Days", 
         Decode(e.user_area,NULL,'Nilesen', 
                            e.user_area) AS "User_Area", 
         Count(DISTINCT b.username)          AS "# of Distinct Users on Sys", 
         Count(b.username)                   AS "# of total connect of sys", 
         Round((Sum(b.min_duration) / 60),2) AS "Total connect time in hours" 
FROM     audit_log_duration b, 
         dbnk_user e 
WHERE    b.cstmer_id = 6430 
         AND b.cstmer_id = e.cstmer_id 
         AND Upper(b.username) = Upper(e.user_id) 
         AND Trunc(b.logon_time) >= To_date('01-JUN-2008','DD-MON-YYYY') 
         AND Trunc(b.logoff_time) <= To_date('01-DEC-2008','DD-MON-YYYY') 
GROUP BY To_char(b.logon_time,'DD-MON-YYYY'), 
         user_area; 


Only the order of where clauses varies in the above 2 queries.

Will there be any impact on performance ??

Thanks,
Elaiyavel.

[EDITED by LF: instead of invalid usage of the [color] tags, I've applied [code] tags]

[Updated on: Thu, 14 May 2009 06:56] by Moderator

Report message to a moderator

Re: Order of where clause in SQL query. [message #403234 is a reply to message #403232] Thu, 14 May 2009 06:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
See the explain plan for both queries and you'll be able to tell the difference.
Re: Order of where clause in SQL query. [message #403237 is a reply to message #403232] Thu, 14 May 2009 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Much likely "Trunc(b.logon_time)" or the like will be the root of problem.

Regards
Michel

Re: Order of where clause in SQL query. (merged 3) [message #403240 is a reply to message #403232] Thu, 14 May 2009 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
STOP posting the same question.

Regards
Michel
Re: Order of where clause in SQL query. (merged 3) [message #403288 is a reply to message #403240] Thu, 14 May 2009 10:45 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
select *
from table_X
where a = 1
and b = 2
and c = 3
/


select *
from table_X
where c = 3
and b = 2
and a = 1
/


Quote:
Q: Is there a difference in the performance of these two queries which can be directly attributed to the order of predicates in the where clause?

A: maybe

Q: Should I care.

A: nope

The order of predicates in the where clause can under certain circumstances make a difference but it is stupid to worry about it. Indexes and storage circumstances etc. generate a query plan. This is where you should focus performance concerns. Trying to tweak a little extra performance out of this obscure situation is a lot of work for little benefit.

However, everyone wants to know so consider this:

TABLE_X
A          B          C
---------- ---------- -----------
1          2          NULL
...

(10 MILLION ROWS, A = 1, B = 2, C = NULL FOR ALL ROWS)

1) assume no indexes on the table
2) assume Oracle parses from the bottom up and executes from top down
3) where clause is
where a = 1
and b = 2
and c = 3

   loop till no more data
      fetch a row
      look at A, it = 1 so
      look at B, it = 2 so
      look at C, it is null, throw out the row

3 tests per row for 10 million rows means 30 million tests to get zero rows

vs.

4) where clause is
where c = 3
and b = 2
and a = 1

   loop till no more data
      fetch a row
      look at C, it is null, throw out the row

1 test per row for 10 million rows means 10 millions tests to get zero rows.

Clearly there will be a performance difference. It will all be CPU costs. Why does this happen? Because Oracle stops checking values on rows once it figures out a row is no longer needed. In old parlance we used to call this "short-ciruiting" during query execution (I didn't invent the name).

But who worries about this level of detail. I would expect there would be an index on one of more of these columns. That means this analysis is less and less important. Indeed this process happens all the time, but only after indexes have been considered to construct a plan and those where clause pieces that are accounted for during acces path row aquisition do not participate in this process.

so the long and short of it is, the order of tests in the where clause can affect performance, but if you are worried about that you need to find another job cause you are wasting time that could have been used for something that is actually important.

Besides, does oracle execute top down or bottom up? Seems to me this changes by release. In fact, the concept of top down/bottom up may not even be relevent anymore if oracle moves predicates around during query rewrite so you have no real control over the order in which "left over" tests are done.

Good luck, Kevin

[Updated on: Thu, 14 May 2009 10:48]

Report message to a moderator

Previous Topic: loading with external table rejected records not written to bad or discard file
Next Topic: How to get a sequence nextval through a database link.
Goto Forum:
  


Current Time: Fri Dec 09 13:25:22 CST 2016

Total time taken to generate the page: 0.42338 seconds