Re: Q: Performance

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

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

No.   

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

>>>>
Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Sat Jun 03 1995 - 00:00:00 CEST