Re: Set Arithmetic

From: Martin Haltmayer <Martin_Haltmayer_at_KirchGruppe.de>
Date: 1998/02/12
Message-ID: <34E33C4A.62620FD5@KirchGruppe.de>#1/1

Set arithmetic is a fine thing but is used much too seldom. Example of SQL optimizing course: Instead of

select c1
from t1
where c1 not in (

     select c2
     from t2
     where c1 = c2

)

you may try

select c1
from t1
minus
select c2
from t2

which may be faster even without indexes by magnitudes. A few general hints:

• Do not use "union" in a subquery but "union all" because "union" forces a sort merge.
• Do not use "distinct" in a subquery because it forces a sort aggregate.
• Always try to reduce your matching set of rows as early as possible. Example: Consider

(

     select distinct c1
     from t1
     minus
     select c2
     from t2

)
minus
select c3
from t3

instead of

select distinct c1
from t1
minus (

     select c2
     from t2
     union all
     select c3
     from t3

)

Which one is faster heavily depends on your datasets.

• Let the optimizer do the work. To help it, keep your statistics up-to-date. Try

execute dbms_utility.analyze_schema (schema => 'TESTUSER', method => 'ESTIMATE STATISTICS', estimate_percent => 1)
execute dbms_utility.analyze_schema (schema => 'TESTUSER', method => 'ESTIMATE STATISTICS', estimate_percent => 1, method_opt => 'FOR TABLE FOR ALL INDEXED COLUMNS SIZE 254') before doing huge queries and after doing heavy changes.

• It may be advantageous not to have indexes because cost based optimizer may choose to use the very efficient hash join.

Martin Haltmayer

Lawrence wrote:
>
> I am writing an application which is having to, unvoidably use a lot of set
> arithmetic (UNION, INTERSET, MINUS) with large numbers of rows.
>
> Has nayone got any experience of doing this.. I haven't had the opportunity
> to test the system with a large volume of data as yet and I am a bit
> concerned about the likely performance impact of set operations...
>
> Does anyone have any tips & tricks for optimising such any application? I
> have increased my SORT_AREA_SIZE to 1 MB and this seems to be okay with my
> small datasets..
>
> Any help would be most appreciated..
>
> Thanks
> Lawrence
Received on Thu Feb 12 1998 - 00:00:00 CST