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?

Sadly, changing the subquery into a join and using a 'leading' or
'ordered' hint is not possible.


Regards
Peter

On Aug 27, 8:51*am, Peter Kallweit <p_kallw... (AT) arcor (DOT) de> wrote:
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 --

On Aug 27, 8:51*am, Peter Kallweit <p_kallw... (AT) arcor (DOT) de> wrote:
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 --

On Aug 27, 8:51*am, Peter Kallweit <p_kallw... (AT) arcor (DOT) de> wrote:
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 --

On Aug 27, 8:51*am, Peter Kallweit <p_kallw... (AT) arcor (DOT) de> wrote:
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 --

On Wed, 27 Aug 2008 14:51:38 +0200, Peter Kallweit
<p_kallweit (AT) arcor (DOT) de> wrote:


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

On Wed, 27 Aug 2008 14:51:38 +0200, Peter Kallweit
<p_kallweit (AT) arcor (DOT) de> wrote:


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

On Wed, 27 Aug 2008 14:51:38 +0200, Peter Kallweit
<p_kallweit (AT) arcor (DOT) de> wrote:


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

On Wed, 27 Aug 2008 14:51:38 +0200, Peter Kallweit
<p_kallweit (AT) arcor (DOT) de> wrote:


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

On Aug 27, 2:58*pm, sybra... (AT) hccnet (DOT) nl wrote:
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