RE: Predicates are not being pushed if casting PL/SQL table
Date: Sat, 5 Sep 2015 15:25:35 +0000
Message-ID: <DUB131-W21F9B7A530563E18735E6A1560_at_phx.gbl>
Stefan explained it a lot better than I did. Even with the column reference corrected, I believe that the TABLE subquery still won't be unnested. e.g.
SELECT null
FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS)
WHERE OBJECT_ID IN (SELECT VALUE(t) FROM TABLE(NUMBER_TABLE(1)) t);
But it could be rewritten to avoid the subquery. Something like (untested):
SELECT DISTINCT obj.OBJECT_ID
FROM TEST_OBJECTS obj
, TABLE(NUMBER_TABLE(1)) t
WHERE obj.OBJECT_ID = VALUE(t) ;
> Date: Sat, 5 Sep 2015 12:18:17 +0200
> From: contact_at_soocs.de
> To: oracle-l_at_freelists.org; dombrooks_at_hotmail.com; jolliffe_at_gmail.com
> Subject: RE: Predicates are not being pushed if casting PL/SQL table
>
> 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-l
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 05 2015 - 17:25:35 CEST