Re: exists or limit

Mark D Powell wrote :

> I have trouble seeing how these explain plans match a single table
> select unless the target of the select is a view?

It's a "complex" query with some lot's of inner joins. (normalized schema...)

> In the second query why I think the exists is not doing what you
> expect is because of the way the query is written Oracle cannot check
> the exists until the entire subquery result set is returned. If you
> could write the SQL as a coordinated subquery which would be fired
> once for every row in the outer query then as soon as Oracle gott a
> hit it could stop the sub-query.

Ok, sounds great. But I don't really know how to do that ;). I'll try to read some literature about it, but it's almost chinese to me ^_^

> Also if you run these queries back to back the result times may not be
> reliable since the second and third queries potentially benefit or may
> be adversly impacted by blocks cached by the previous queries.

Yes but since the queries are all as slow, I'm not sure any cache is involved in making some of them quicker.

Anyway, I tested all day long, and I now think my problem is more about my data design. I have a very disparate repartition of my data:

Some document list are tiny (2), or "huge" (2.000.000) and some of my entities are rare (2 occurrences in whole corpus) and others are very abundant (2.000.000). And I guess the optimizer can't know in advance if the current parameters will return 2 or 2.000.000 rows.

A hint to that thinking is the Entreprise manager, the tuning advisor that, after looking at the actual data repartition and the current parameter values, is able to find another execution plan which is 99.9% quicker to my slow query.

Too sad it could not find it beforehand...

-- 
Hugo