Re: Q: Performance

stowe_at_mcs.net wrote:
>> 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. 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;
>

Actually, A and B are only equivalent in that they return the same answer. From a performance perspective they are *VERY* different.

Query A is similar in performance/execution to the join suggested above (select * from a, b where a.f1 = b.f1 (+) and b.f1 is null).

Query B however is executed as though it was procedurally written as follows:

    for x in ( select rownum, field1 from A ) loop

        select count(*)
        into N
        from B 
        where B.field1 = x.Field1
          and rownum = 1;
        if ( N > 0 ) then 
            output Record
        end if;

If TABLE A is "small" and TABLE B is "big" and Table B has an index with field1 in the leading edge, then Query B is probably the best. If on the other hand, A is "Big" and B is "Big or Small" and there are no other predicates on A (eg: you will do a full tablescan of A anyway), then Query A or its variant is probably best. In short:

A-small & B-Big
o Use Not Exists

A-Big (but good predicate on A in addition to the not exists) & B-Big o Use Not Exists

A-Big (and no predicate) & B-Anysize
o Use Join or not in

A-Anysize & B-Small
o Use not in or join

>> 3. What's faster:
>> -----------------
>> A. Insert into ... values (....) as select ...
>> B. Insert with a cursor
>
>A is syntactically incorrect (values and as select clauses do not belong in the same statement) and
> as for B, a cursor is always used. I'm asssuming you mean using an explicit vs. an implicit cursor.
>Generally, explicit cursors are faster, but it depends on your code.
>

Insert into Table1
select * from AnotherTable
Where ....

is in general faster then:

   for x in ( select * from AnotherTable where .... ) loop

       insert into Table1 values ( x.c1, x.c2, .... );    end loop;

Especially in a networked environment. What you loose with Insert...Select is the ability to commit ocasionally if you want.

>>>>>
>Michael Stowe
>Constellation Engineering, Inc.
>http://www.mcs.com/~stowe
>
>

Thomas Kyte
tkyte_at_us.oracle.com Received on Sun Jun 04 1995 - 00:00:00 CEST