Re: How to force subquery being the first executed

From: Malcolm Dew-Jones <>
Date: 27 Aug 2008 09:26:45 -0800
Message-ID: <48b58045$>

Peter Kallweit ( wrote:
: Hi %,

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

: 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 c1 from heavy h where h.k = s.k) C1 ,
	( select c2 from heavy h where h.k = s.k) C2 ,
	simple s

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

Original text of this message