Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: optimal sql

Re: optimal sql

From: Christoph Seidel <chris666.seidel_at_gmx.de>
Date: Wed, 11 Dec 2002 08:38:54 +0100
Message-ID: <at6q3j$1030sn$1@ID-143718.news.dfncis.de>


ctcgag_at_hotmail.com wrote:

> For my test case (I used count(*) rather than *, which may make a big
> a difference for some things) this was pretty good, 6 seconds, and
> used an anti-hash.
>
> The 'not exist' surprisingly took 26 seconds, stepping through both
> columns in order and applying a filter.
>
> The 'minus' (with an outer select to do the counting) took 5.8
> seconds. Its explain plan seemed to be only trivially differnt from
> 'not exist', so I don't know why it's so much faster.
>
> My favorite, the left join,
> select count(*) from t1,t2 where t1.pk=t2.pk(+) and t2.pk is null;
> took 6.4 seconds. I does a hash join, then single-source filter
> on the results.
>
> I was surprised that none of these was optimized to be the same
> as another. Especially that the hash-then-filter series of the left
> join didn't just reduce to the anti-hash.

thx for that! Received on Wed Dec 11 2002 - 01:38:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US