Re: exists or limit

"Hugo" <hugo_at_nospam.invalid> wrote in message news:48773909$0$10576$426a34cc_at_news.free.fr...
> Hi,
>
> I need to test if an entity exist in a document set. For instance, test
> if there is any "Location" in a given set of 42 documents. I don't need
> to retrieve the actual rows, the count or whatever, but just the
> existence.
>
> Query A (get all Location in the corpus):
> select xxx from yyy where zzz
> execution time: 3 seconds (lots of results)
>
> Query B (exist)
> select 1 from dual where exists (select 1 from yyy where zzz)
> execution time: still 3 seconds
>
> Query C (limit)
> select xxx from yyy where zzz and rownum = 1
> execution time: still 3 seconds
>
> I can't understand why query B and C are as slow as Query A. It should
> stop at the first matching row found, and before all of them are
> visited. But here it's like it does not make any difference at all.
>
> In others SGBD (MySQL mainly), using limit or exists in this case is
> very effective, performance wise.
>
> In Oracle, "exists" is just king of syntaxic sugar, or does it really
> means something for the optimizer and execution plan generated ?
>
> As I'am very new to the Oracle world, maybe I'm missing something
> obvious. But for the moment I'm stuck with pretty poor performance...
>
> --
> Hugo

We would need to see the explain plan.(at the very least) If it is set up correctly like it shouldn't take anywhere near 3 seconds. So the question is where is it spending its time?

explain plan for
select ..... ;
select table(dbms_xplan.display) from dual;

Jim Received on Fri Jul 11 2008 - 06:01:35 CDT