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> 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.