How to force subquery being the first executed
Peter Kallweit
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?
Sadly, changing the subquery into a join and using a 'leading' or
'ordered' hint is not possible.
Regards
Peter
Mark D Powell
Without the actual explain plan or any supporting detail for the
tables/indexes available there is no solid reliable way to answer your
question. The CBO is going to solve the query based on the statistics
for the referenced objects and the plan options available to it based
on how the query is written.
Run an explain plan and then look in your SQL manual to see if any of
the following hints might be of use in obtaining the plan you want:
NO_MERGE causes Oracle not to merge mergeable views.
PUSH_SUBQ causes non-merged subqueries to be evaluated at the earliest
possible step in the execution plan
But what you can do depends on how the SQL is written and what the
statistics tell the CBO. Rewriting the query so that the view code
and the query are one statement might provide more options.
HTH -- Mark D Powell --
sybr...@hccnet.nl
My bet is you are not posting a simplified version.
You are also not posting the execution plan (unless some want people
to believe there are few 'silver bullets' or generic ways to 'force'
something, especially as you seem to imply the query can not be
changed). This is all not very helpful and means it is crystal ball
time again.
First of all, Tom Kyte demonstrates there can be a tremendous negative
impact when the column involved in the subquery is both indexed and
NULL *allowed*.
As the optimizer thinks the result of a query can be NULL, it won't
use the index, even if NULL columns do not exist.
As it is currently customary to allow NULL on *all* columns except for
the primary key, this might be your issue.
Secondly there is a PUSHQ hint, which exactly does what you want:
execute the subquery first. As this would involve modifying the query,
probably you can't use it.
Enters: using stored outlines. However this won't work if the query
contains hardcoded literals and those literals vary. Which it is
important to post the exact query.
My 2 euro cents.
Let Barry Bulsara flame away at this response!!!!!!
--
Sybrand Bakker
Senior Oracle DBA
Barry Bulsara
>
> - Show quoted text -
Let's not judge you on just this one helpful response shortly after a
posting tantrum where your professionalism was questioned, let's judge
you on all the posts to come from now on. Most remaining subscribers
to this group would like a lot more professionalism and technical
input and a lot lot less ad hominem comment.
Barry
sybr...@hccnet.nl
<bbuls...@hotmail.com> wrote:
>Most remaining subscribers
>to this group would like a lot more professionalism and technical
>input and a lot lot less ad hominem comment.
I assume by 'most' you mean basically 'I'?
Because you are the only one complaining, and as Usenet doesn't have
'subscribers' as far as I am concerned the subscribers you mention are
virtual.
Put it otherwise: it seems to me you speak only for yourself.
You should avoid to speak for others: you are the only one
complaining, just because in the past I've set you straight a few
times and rightly so.
I am amazed even after two or three years you couldn't forget this and
use this to campaign against me.
This forum assumes a basic willingness to resolve your own problems
and the willingness to 'learn to fish'.
If you don't want that, there are other forums where they are happy
to abstract the Oracle documentation ad nauseam for you.
Malcolm Dew-Jones
: 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.
Aya the Vampire Slayer
>>Most remaining subscribers
>>to this group would like a lot more professionalism and technical
>>input and a lot lot less ad hominem comment.
>I assume by 'most' you mean basically 'I'?
While I realize that you are a 10-year veteran of this group (at least,
so you've said) and probably get tired of answering the same questions
over and over again, it's still no excuse to be rude to people when
telling them to go use google instead of wasting your time. Especially
if you want to increase readership. Everyone has to start somewhere
before becoming an expert with something as complex as Oracle (DB,
AppServer, whatever), and being encouraging rather than derogatory is
more likely to get people to stick around after they stop being annoying
n00bs.
>Because you are the only one complaining, and as Usenet doesn't have
>'subscribers' as far as I am concerned the subscribers you mention are
>virtual.
Most people just don't want to get involved in an obviously impending
flamewar over something theoretically off-topic by showing support for
someone who is willing to step forward and call another person out on
their rude behavior. So yeah, I agree with Barry. Sorry.
I've been subbed to these oracle groups for about a year now but up
until about a month or two ago have done nothing but immediately
'catchup' the groups without even reading. I'm not sure what got me
actually reading them recently, but it has been enlightening so far,
random bouts of crankiness aside.
--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator