Re: How to force subquery being the first executed

Peter Kallweit (p_kallweit_at_arcor.de) wrote:
: Hi %,

: I've a question about 'leading' the optimizer. To make it not too
: simple, it should work from 9.2.0.4 up to 11.1.0.6.

: I'm selecting from a heavy view (many joins, big result set), using a
: subquery to restrict the result:
: select *
: from heavyView
: where keys in (select keys from simpleView);

: In most cases the query starts with complete execution of the heavyView
: and is then restricting the result using the subquery. This results in a
: very bad performance.

: However, from the application logic I know, that the subquery returns
: only a few rows.
: Therefore I want the query to start with the subquery, and then going
: with the result rows into the main view.
: Indexes to support accessing the main view in this way are available.

: How can I enforce this execution order?

No idea, but there are various ways to structure your query. I would try them all to see what works best.

syntax not correct

	with simple as
	( select ...
	)
	select * from heavy where clause using simple

	select heavy.* 
	from heavy,simple
	where heavy.key=simple.key

	select
	( select c1 from heavy h where h.k = s.k) C1 ,
	( select c2 from heavy h where h.k = s.k) C2 ,
	...
	from
	simple s

probably others, it may or may not make a difference. Received on Wed Aug 27 2008 - 12:26:45 CDT