Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: optimal sql
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