In recent versions of Oracle it's almost all cost-based - though some
subqueries will be unnested "heuristically" i.e. because it's possible and
costs won't be considered.
In principal Oracle will compare the cost of unnesting (and subsequent
transformations) against the cost of not unnesting.
Costing (if not unnested) will be based on considerations of:
What it costs to run the query once
How many times the optimizer the query will need to execute (according to
the optimizer's estimates)
The volume of data that will remain after elimination by the suquery.
e.g. t1 joins to t2, but could be subject to a subquery against t3 first.
The join to t2 is high precision and eliminates 90% of the t1 rows, ,then
the subquery elimninates a further 50%
The subquery run against t1 is a little expensive on each execution and
will have to run for 75% of the rows, leaving 50% of the rows in place, of
which 90% will then be delete by the join
It would make sense to join then subquery.
The processing is non-trivial and the arithmetic subject to plenty of
error; so it can be important to [no_]push_subq() and order_subq() in
complex cases.
Regards
Jonathan Lewis
On Wed, 2 Jun 2021 at 18:55, Matt McPeak <
mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Very, very interesting -- and thank you!
Nowadays (12c and later, for sure), I've been taught to the view that
queries should only be hinted to give the CBO more information (so, a
CARDINALITY hint or a DYNAMIC_SAMPLING hint). Hints that tell the CBO
_how_ to run the query (e.g., an INDEX hint) were kind of a last resort.
If I don't use PUSH_SUBQ or ORDER_SUBQ, how does Oracle decide the other
in which to run subqueries? Is it always "subqueries after joins and then
subqueries in order from top to bottom"? Do statistics enter into it at
all? Is it moot because most subqueries are unnested anyway?
Thanks again,
Matt
On Wednesday, June 2, 2021, 01:30:25 PM EDT, Jonathan Lewis <
jlewisoracle@xxxxxxxxx> wrote:
Matt,
Effects and options depend on version of Oracle (and whether CBQT has been
disabled).
By default Oracle used to execute subqueries at the last possible moment
unless you added the push_subq hint to the code, in which case they were
run as early as possible.
There was also a question with multiple subqueries of the order of
execution - originally I think Oracle ran them in the order they appeared
in the text then. on one of the (10.2) upgrades, it switched to run them in
reverse order. (Affected by a parameter setting).
Then on another upgrade the push_subq() hint was changed - instead of a
single push_subq -- with no parameters - which "pushed" all the subqueries
as much as possible you could push_subq(@qb_name) to push named subqueries
individually and no_push_subq(@qb_name) to block pushing. This was about
the time that costing of subquery effects came into play.
https://jonathanlewis.wordpress.com/2007/03/09/push_subq/
Then in 12.2.0.1 Oracle introduced the order_subq() hint so you could
specifiy the order in which subqueries should be applied - which I've
written about fairly recently
https://jonathanlewis.wordpress.com/2018/09/05/subquery-order/
Regards
Jonathan Lewis
On Wed, 2 Jun 2021 at 17:12, mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx <
mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Jonathan,
Could you please elaborate on "c"? If a query contains multiple
subqueries, are you saying the position of each one in the text of the
query may influence the execution plan?
Thanks,
Matt
On Thursday, May 27, 2021, 05:05:55 AM EDT, Jonathan Lewis <
jlewisoracle@xxxxxxxxx> wrote:
Before trying a rewrite - or going though the painful process of trying to
interpret a CBO trace file, you could pursue three ideas:
a) Does Oracle consider subquery unnesting to be illegal ... try adding an
/*+ unnest */ hint to the subquery itself to see if it is possible (even it
produces a very bad plan as a result)
b) Does Oracle think the subquery will happen only a very small number of
times - if so address address the cause of of the bad cardinality estimate
before chasing the subquery error
c) Has Oracle run the subquery at the earliest possible moment or the
latest possible moment - if there are multiple subqueries have they been
run in the wrong order: would it help to change the timing of when the
subquery runs.
Regards
Jonathan Lewis
On Wed, 26 May 2021 at 15:29, Amit Saroha <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
Hi,
In one of the queries TKProof shows, most time is spent in accessing a
table thousands of times inside NOT EXIST condition.
I am looking for your inputs if it's a promising idea to replace NOT EXIST
with the LEFT OUTER JOIN and IS NULL condition?
Any inputs in this regard are appreciated.
Regards,
Amit S
