RE: Predicates are not being pushed if casting PL/SQL table
Date: Sat, 5 Sep 2015 12:18:17 +0200 (CEST)
Message-ID: <1811761491.200635.1441448297313.JavaMail.open-xchange_at_app01.ox.hosteurope.de>
Hi Patrick,
picking up Dominic's comment and your replies. Both test cases are not equivalent (once i get 90131 rows and with the other 0 rows).
> So seems it's not about predicates being pushed into subquery, but subquery cannot be un-nested. I wonder why that is, what is it about the outer
> query having TABLE expression would prevent un-nesting this subquery?
Here we go with a 12.1.0.1 CBO trace for your "TABLE(NUMBER_TABLE(1)" example.
--------------------8<------------------------------------…
SU - subquery unnesting
FPD - filter push-down CVM - complex view merging SPJ - select-project-join
…
CVM: Merging SPJ view SEL$4 (#0) into SEL$3 (#0) Registered qb: SEL$07BDC5B4 0x6f6581a8 (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"_at_"SEL$4" …
CBQT: copy not possible on query block SEL$07BDC5B4 (#0) because nested table CBQT: copy not possible on query block SEL$1 (#0) because inner query block constraints CBQT bypassed for query block SEL$1 (#0): Cannot copy query block. CBQT: Validity checks failed for 0u15kkytnwaqm. …
SU: Checking validity of unnesting subquery SEL$07BDC5B4 (#0) SU: SU bypassed: Invalid correlated predicates. SU: Validity checks failed.
…
FPD: Considering simple filter push in query block SEL$1 (#0)
EXISTS (SELECT 0 FROM TABLE("NUMBER_TABLE"(1)) "KOKBF$0")
FPD: Considering simple filter push in query block SEL$07BDC5B4 (#0)
:B1=:B2
try to generate transitive predicate from check constraints for query block SEL$07BDC5B4 (#0) finally: :B1=:B2
try to generate transitive predicate from check constraints for query block SEL$1 (#0) finally: EXISTS (SELECT 0 FROM TABLE("NUMBER_TABLE"(1)) "KOKBF$0") …
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT NULL "NULL" FROM (SELECT DISTINCT "TEST_OBJECTS"."OBJECT_ID" "OBJECT_ID" FROM "TEST"."TEST_OBJECTS" "TEST_OBJECTS") "from$_subquery$_001" WHERE EXISTS (SELECT 0 FROM TABLE("NUMBER_TABLE"(1)) "KOKBF$0" WHERE "from$_subquery$_001"."OBJECT_ID"="from$_subquery$_001"."OBJECT_ID") …
--------------------8<------------------------------------
You can see the reason for it ("SU: SU bypassed: Invalid correlated predicates.") and if you check the predicate section you gonna see that your IN query was transformed to an EXISTS with the correlated predicate "from$_subquery$_001"."OBJECT_ID"="from$_subquery$_001"."OBJECT_ID". This is also what Dominic means with "Therefore object_id resolves to object_id column from your distinct inline view".
In contrast to that here is the snippet from your GTT_TEST_OBJECTS example (also on 12.1.0.1).
--------------------8<------------------------------------…
SELECT NULL "NULL" FROM (SELECT DISTINCT "TEST_OBJECTS"."OBJECT_ID" "OBJECT_ID" FROM "TEST"."TEST_OBJECTS" "TEST_OBJECTS") "from$_subquery$_001" WHERE "from$_subquery$_001"."OBJECT_ID"=ANY (SELECT /*+ OPT_ESTIMATE (TABLE "GTT_TEST_OBJECTS"_at_"SEL$3" ROWS=1.000000 ) */ "GTT_TEST_OBJECTS"."OBJECT_ID" "OBJECT_ID" FROM "TEST"."GTT_TEST_OBJECTS" "GTT_TEST_OBJECTS") …
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest. Subquery removal for query block SEL$3 (#2) RSW: Not valid for subquery removal SEL$3 (#2) Subquery unchanged.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1). SU: Checking validity of unnesting subquery SEL$3 (#2) SU: Passed validity checks. SU: Transforming ANY subquery to a join.…
--------------------8<------------------------------------
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK
> Dominic Brooks <dombrooks_at_hotmail.com> hat am 4. September 2015 um 14:08 geschrieben:
>
> Does the test case have an error?
> Column name from collection is not "object_id" but value(alias) or column_value.
> Therefore object_id resolves to object_id column from your distinct inline view
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 05 2015 - 12:18:17 CEST