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

On 1 Feb 98 09:09:12 GMT, "Everton Elliott" <everton_at_aapi.co.uk> wrote:

[snip]

>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.

[snip]

Hi Everton,

if you use rule based optimizer mode, then the sequence of your where-condition does matter. I think that the difference is simply that the faster query uses the more selective index. Just try a explain plan and see which indexes get used.

Another hint: Don't do COUNT(*). Instead try count(C) or COUNT(B), this way Oracle does not have to go to the table, rather an index scan will be sufficient to get the results of your query.

HTH
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net