Re: order of predicates affects CPU usage hence elapse time on sequent

From: Fernando Nantes De Souza <fnantes_at_usa.net>
Date: 1998/02/03
Message-ID: <6b8rim$cef@camel19.mindspring.com>#1/1

Everton Elliott wrote in message <01bd2eef$1869f2e0$1e21a4c2_at_evertonco>...
>Hi,
>I have seen a unusual thing:-
>
>TABLE
>======
>A CHAR INDEX
>B DATE INDEX
>C CHAR INDEX
>D CHAR INDEX
>E CHAR
>
>
>select count(*) from TABLE
>WHERE C='SSSS'
>AND B between '10-JAN-97' and '10-JAN-98'
>
>versus
>
>select count(*) from TABLE
>WHERE B between '10-JAN-97' and '10-JAN-98'
>AND C='SSSS'
>
>Under tkprof oracle 7.3 and under oracle 8.x I see that the IO are simular
>but
>the CPU usage diffrent by a multiple of four and hence the elapse time are
>different. Full table scan is used for both query.
>
>Why should swaping the 'AND' predicate cuase the CPU usage to change????
>
>Cheers
>
>

When it finds a full scan access path, Oracle will evaluate the conditions in a WHERE clause from bottom to top. If you put the most selective condition at the end, the query will use less CPU because the next condition will not be evaluated if the first is false.

Why is Oracle using a full scan? It's another problem that will depend
on many factors, most of them already discussed in previous posts.

Regards,

-Fernando N. De Souza
Oracle DBA
National Data Corporation
Health EDI Services
Norcross, GA Received on Tue Feb 03 1998 - 00:00:00 CST