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?
On Wednesday, June 2, 2021, 01:30:25 PM EDT, Jonathan Lewis
Effects and options depend on version of Oracle (and whether CBQT has been
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
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.
Then in 18.104.22.168 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/
On Wed, 2 Jun 2021 at 17:12, mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx
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?
On Thursday, May 27, 2021, 05:05:55 AM EDT, Jonathan Lewis
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 errorc) 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.
On Wed, 26 May 2021 at 15:29, Amit Saroha <dmarc-noreply@xxxxxxxxxxxxx> wrote:
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.