Re: Q: Performance

In article <3qoou3$f95_at_News1.mcs.com>, stowe_at_mcs.net writes:
>> martinj_at_pi.net writes:
>>
>> Hello,
>>
>> Because of daily usage of large tables, I have some performance questions I'd
>> like answers to:
>>
>> 1. What's faster:
>> -----------------
>> A. select rownum from A where A.field1 NOT IN
>> (select fieldA from B)
>> B. select rownum from A where NOT EXISTS
>> (select 'x' from B where A.field1 = B.fieldA)
>> C. Anything else
>
> A and B are actually equivalent.

They are not equivalent if an index exists on B.fieldA.

B is faster (with an index) because the sub-query ENDS when it finds any record match rather than resolving the full sub-query and passing back 'n' records to satisfy the request.

>Depending on the characteristics of the tables, a join is often
> faster, i.e.,
> select a.rownum
> from a, b
> where a.field1 = b.fielda(+)
> and b.fielda is null;

Try using EXPLAIN or TKPROF to check how they run.

>
>
>> 2. Are there performance differences between:
>> ---------------------------------------------
>> A. Select count(*) ...
>> B. Select count(rownum) ...
>> C. Select count('x') ....
>
> No.

You only have a performance difference if you count particular columns as the query must then go and unpack each row to locate the column and then count it.

If the column being counted is also a unique index and appears in the WHERE clause then you can have marked speed ups as the query may only need to read the index which is usually made up of less blocks and thus less I/O.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras