exists or limit

From: Hugo <hugo_at_nospam.invalid>
Date: Fri, 11 Jul 2008 12:41:03 +0200
Message-ID: <48773909$0$10576$426a34cc@news.free.fr>


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

Received on Fri Jul 11 2008 - 05:41:03 CDT

Original text of this message