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

From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
Date: Mon, 7 Sep 2015 13:48:53 +0800

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

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

| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | OMem | 1Mem | Used-Mem
|

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

| 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

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

| Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem
|
---------------------------------------------------------------------------------------------------------------------------------------------

| 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@xxxxxxxxxxx> 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@xxxxxxxx
To: oracle-l@xxxxxxxxxxxxx; dombrooks@xxxxxxxxxxx; jolliffe@xxxxxxxxx
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"@"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"@"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: @OracleSK

Dominic Brooks <dombrooks@xxxxxxxxxxx> 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

--
//www.freelists.org/webpage/oracle-l

Follow-Ups:
- Re: Predicates are not being pushed if casting PL/SQL table
  - From: Patrick Jolliffe

References:
- RE: Predicates are not being pushed if casting PL/SQL table
  - From: Dominic Brooks
- RE: Predicates are not being pushed if casting PL/SQL table
  - From: Stefan Koehler
- RE: Predicates are not being pushed if casting PL/SQL table
  - From: Dominic Brooks

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

Other related posts: