Bug 13607764 is an example of the problem that Stefan has highlighted - and the
reason why your current code catches you in two different ways. (It also shows
why a "simple" manual rewrite to unnest the subquery won't work.)
The optimizer won't give you complex view merging (to get you to the automatic
plan you got from the global temporary table) because of the collection
operator; and if you manually unnest the subquery to try and get a nested loop
driven by the collection you still won't get what you want because the DISTINCT
will make the optimizer want to do a semi-join into test_objects - and then it
won't be able to push the join predicate (as per bug 13607764) because of the
collection operator.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Patrick Jolliffe [jolliffe@xxxxxxxxx]
Sent: 07 September 2015 09:39
To: Stefan Koehler
Cc: timur.akhmadeev@xxxxxxxxx; oracle-l; Dominic Brooks
Subject: Re: Predicates are not being pushed if casting PL/SQL table
Thanks all. Obviously the 'real' SQL is much more complex, actually using
COUNT(*)/GROUP BY joining many tables in subquery, I don't think it's a simple
case to un-nest.
The developers seem to be 'abusing' sub-query factoring to ease development,
seems to be causing us other problems (documented elsewhere), but the
combination with TABLE() functions seem to be causing this issue.
Looks alot like bug 13607764 which has status
"92 - Closed, Not a Bug"
On 7 September 2015 at 16:03, Stefan Koehler
<contact@xxxxxxxx<mailto:contact@xxxxxxxx>> wrote:
Hi Patrick,
not quite sure what you are expecting. However picking up Timur's explanation
with your test case.
…
Registered qb: SEL$1 0x74856678 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"
Registered qb: SEL$2 0x748513c8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$2 nbfros=1 flg=0
fro(0): flg=4 objn=76313 hint_alias="TEST_OBJECTS"@"SEL$2"
Registered qb: SEL$3 0x74851020 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$3 nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="GTT_TEST_OBJECTS"@"SEL$3"
Registered qb: SEL$4 0x748ae668 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$4 nbfros=1 flg=0
fro(0): flg=4 objn=0 hint_alias="KOKBF$0"@"SEL$4"
…
CVM: Merging SPJ view SEL$4 (#0) into SEL$3 (#0)
Registered qb: SEL$07BDC5B4 0x74851020 (VIEW MERGE SEL$3; SEL$4)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$07BDC5B4 nbfros=1 flg=0
fro(0): flg=0 objn=0 hint_alias="KOKBF$0"@"SEL$4"
…
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SU: Checking validity of unnesting subquery SEL$07BDC5B4 (#0)
SU: Transforming ANY subquery to a join.
Registered qb: SEL$ABEA6AB6 0x74856678 (SUBQUERY UNNEST SEL$1; SEL$07BDC5B4)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$ABEA6AB6 nbfros=2 flg=0
fro(0): flg=1 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"
fro(1): flg=0 objn=0 hint_alias="KOKBF$0"@"SEL$4"
…
PM: Passed validity checks.
PM: PM bypassed: checking.
query block SEL$1 transformed to SEL$ABEA6AB6 (#0)
FPD: Considering simple filter push in query block SEL$ABEA6AB6 (#0)
"from$_subquery$_001"."OBJECT_ID"=VALUE(KOKBF$0)
try to generate transitive predicate from check constraints for query block
SEL$ABEA6AB6 (#0)
finally: "from$_subquery$_001"."OBJECT_ID"=VALUE(KOKBF$0)
JPPD: JPPD bypassed: View not on right-side of outer-join.
…
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT NULL "NULL" FROM TABLE("NUMBER_TABLE"(1)) "KOKBF$0", (SELECT DISTINCT
"TEST_OBJECTS"."OBJECT_ID" "OBJECT_ID" FROM "TEST"."TEST_OBJECTS"
"TEST_OBJECTS") "from$_subquery$_001" WHERE
"from$_subquery$_001"."OBJECT_ID"=VALUE(KOKBF$0)
…
If you look closely at your new (correct) test case now, you can see that the
subquery is unnested and transformed into a join. My test case was run
on Oracle 11.2.0.3.6 and 12.1.0.1 and both behave the same in case of query
transformation, but the execution plan is different as the cost also
differs (699 - MERGE JOIN SEMI vs. 458 - HASH JOIN RIGHT SEMI). Any other
transformation is not legal in this case as also stated by the CBO team in
this blog post:
https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1
So for demo purpose only - if you would remove the DISTINCT from the inline
view "from$_subquery$_001", then your query (with TABLE function) could be
transformed into:
…
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT NULL "NULL" FROM TABLE("NUMBER_TABLE"(1))
"KOKBF$0","TEST"."TEST_OBJECTS" "TEST_OBJECTS" WHERE
"TEST_OBJECTS"."OBJECT_ID"=VALUE(KOKBF$0)
…
-------------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows |
Bytes | Cost | Time |
-------------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | |
| 24 | |
| 1 | NESTED LOOPS | | 1 |
7 | 24 | 00:00:01 |
| 2 | SORT UNIQUE | | 1 |
2 | 22 | 00:00:01 |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 1 |
2 | 22 | 00:00:01 |
| 4 | INDEX RANGE SCAN | TEST_OBJECTS_IDX| 1 |
5 | 1 | 00:00:01 |
-------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("OBJECT_ID"=VALUE(KOKBF$))
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Timur Akhmadeev <timur.akhmadeev@xxxxxxxxx<mailto:timur.akhmadeev@xxxxxxxxx>>
hat am 7. September 2015 um 09:25 geschrieben:
Oracle can't do complex view merging with TABLE expressions for some reason
- probably a bug and you should ask Support about it. Easy to re-write
manually if possible. Not sure how you are supposed to allow developers to
"fix" it with GTT but don't allow to re-write SQL.
By the way if you are binding an array in run time you most likely don't
need cardinality hint if array size is usually reasonable - with bind
peeking Oracle sees its elements count and uses it for building a plan.
