Re: Predicates are not being pushed if casting PL/SQL table

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Mon, 7 Sep 2015 13:48:53 +0800
Message-ID: <CABx0cSUDZ=3XZ9x_ddfm9RdwdL4TumH9ihtk1vxa6XCg-UZT-Q_at_mail.gmail.com>

Thanks both, and sorry to waste your time on a bad test case. For you information, I have fixed (hopefully) test case, and still get same behaviour.
Yes, will drill into 10053 trace and see if I can get any further hints. By the way, and sorry for not mentioning before, this is on 11.2.0.4.

SQL> DROP TABLE
TEST_OBJECTS; Table
dropped.

SQL> DROP TABLE
GTT_TEST_OBJECTS; Table
dropped.

SQL> CREATE TABLE TEST_OBJECTS AS SELECT * FROM DBA_OBJECTS; Table
created.

SQL> CREATE INDEX TEST_OBJECTS_IDX ON
TEST_OBJECTS(OBJECT_ID); Index
created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'TEST_OBJECTS'); PL/SQL procedure successfully
completed.

SQL> SQL> CREATE OR REPLACE TYPE NUMBER_TABLE IS TABLE OF NUMBER; 2
/

Type
created.

SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST_OBJECTS (column_value number);

Table
created.

SQL> INSERT INTO GTT_TEST_OBJECTS2 VALUES (1);

1 row
created.

SQL> SQL> SQL> SELECT /*+ gather_plan_statistics */ null

2 FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS) 3 WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */ column_value FROM
GTT_TEST_OBJECTS); no rows
selected

SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT

SQL_ID 00ggxzya0832u, child number
0

SELECT /*+ gather_plan_statistics */ null FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS) WHERE OBJECT_ID IN (SELECT /*+

CARDINALITY(GTT_TEST_OBJECTS 1) */ column_value FROM GTT_TEST_OBJECTS) Plan hash value:
1576474484

| 0 | SELECT STATEMENT | | 1 | | 0
|00:00:00.01 | | |
|

| 1 | VIEW | VM_NWVW_1 | 1 | 1 | 0
|00:00:00.01 | | |
|

| 2 | SORT UNIQUE NOSORT | | 1 | 1 | 0
|00:00:00.01 | | |
|

| 3 | NESTED LOOPS | | 1 | 1 | 0
|00:00:00.01 | | |
|

| 4 | SORT UNIQUE | | 1 | 1 | 0
|00:00:00.01 | 1024 | 1024 |
|

| 5 | TABLE ACCESS FULL| GTT_TEST_OBJECTS | 1 | 1 | 0
|00:00:00.01 | | |
|

|*  6 |     INDEX RANGE SCAN  | TEST_OBJECTS_IDX |      0 |      1 |      0
|00:00:00.01 |       |       |
|

---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

6 -
access("OBJECT_ID"="COLUMN_VALUE")

25 rows
selected.

SQL> SQL> SELECT /*+ gather_plan_statistics */ null

2 FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS) 3 WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */ column_value FROM TABLE(NUMBER_TABLE(1)) GTT_TEST_OBJECTS); no rows
selected

SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT

SQL_ID 33cgqar810vch, child number
0

SELECT /*+ gather_plan_statistics */ null FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS) WHERE OBJECT_ID IN (SELECT /*+

CARDINALITY(GTT_TEST_OBJECTS 1) */ column_value FROM TABLE(NUMBER_TABLE(1))
GTT_TEST_OBJECTS) Plan hash value:
1401649423

| 0 | SELECT STATEMENT | | 1
| | 0 |00:00:00.18 | 627 | | |

|


|   1 |  MERGE JOIN SEMI                        |              |      1

|      1 |      0 |00:00:00.18 |     627 |       |       |

|


|   2 |   SORT JOIN                             |              |      1

|    119K|      1 |00:00:00.18 |     627 |  2887K|   760K| 2566K

(0)|



|   3 |    VIEW                                 |              |      1

|    119K|    119K|00:00:00.18 |     627 |       |       |

|


|   4 |     HASH UNIQUE                         |              |      1

|    119K|    119K|00:00:00.13 |     627 |  6695K|  2749K| 4623K

(0)|



|   5 |      TABLE ACCESS FULL                  | TEST_OBJECTS |      1

|    119K|    119K|00:00:00.04 |     627 |       |       |

|
|*  6 |   SORT UNIQUE                           |              |      1


|      1 |      0 |00:00:00.01 |       0 |  2048 |  2048 | 2048

(0)|

| 7 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1
| 1 | 1 |00:00:00.01 | 0 | | |
|

Predicate Information (identified by operation id):

6 -
access("OBJECT_ID"=VALUE(KOKBF$))

filter("OBJECT_ID"=VALUE(KOKBF$))

28 rows
selected.

On 5 September 2015 at 23:25, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

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

Received on Mon Sep 07 2015 - 07:48:53 CEST

This message: [ Message body ]
Next message: Patrick Jolliffe: "Re: Predicates are not being pushed if casting PL/SQL table"
Previous message: frits.hoogland_at_gmail.com: "Re: Huge Pages in oracle 11g"
In reply to: Dominic Brooks: "RE: Predicates are not being pushed if casting PL/SQL table"
Next in thread: Patrick Jolliffe: "Re: Predicates are not being pushed if casting PL/SQL table"
Reply: Patrick Jolliffe: "Re: Predicates are not being pushed if casting PL/SQL table"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message