Re: Predicates are not being pushed if casting PL/SQL table
Date: Sat, 5 Sep 2015 07:46:43 +0800
Message-ID: <CABx0cSXAN+zV8j+ScHKSkH4eWstM1s1mDo+2f+B58yPQZMpx-Q_at_mail.gmail.com>
Thanks a lot, 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? The two things seem un-related to me.
So I could consider attempting to un-nest the subquery manually myself (of course with real-life example, logic is much more complex)? Any other ideas? Otherwise may need to consider allowing them to dump contents to GTT to join against.
Note interestingly test-case does run fine on my system, (maybe database doesn't check column alias of TABLE or something else I've missed)
On 4 September 2015 at 22:51, Dominic Brooks <dombrooks_at_hotmail.com> wrote:
> 10053 trace says "CBQT: copy not possible ... because TABLE expression".
>
> So subquery does not get unnested.
>
> Sent from my Windows Phone
> ------------------------------
> From: Dominic Brooks <dombrooks_at_hotmail.com>
> Sent: 04/09/2015 13:22
>
> To: jolliffe_at_gmail.com; oracle-l <oracle-l_at_freelists.org>
> Subject: RE: Predicates are not being pushed if casting PL/SQL table
>
> Once the test case error is addressed, try a 10053 trace to see if there's
> any explanation.
>
> Sent from my Windows Phone
> ------------------------------
> From: Dominic Brooks <dombrooks_at_hotmail.com>
> Sent: 04/09/2015 13:08
> To: jolliffe_at_gmail.com; oracle-l <oracle-l_at_freelists.org>
> Subject: RE: Predicates are not being pushed if casting PL/SQL table
>
> 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
>
> Sent from my Windows Phone
> ------------------------------
> From: Patrick Jolliffe <jolliffe_at_gmail.com>
> Sent: 04/09/2015 11:01
> To: oracle-l <oracle-l_at_freelists.org>
> Subject: Predicates are not being pushed if casting PL/SQL table
>
>
>
> Some vendors have developed a stored procedure that we have been looking
> to tune.
> Vendor has identified that rather than casting PL/SQL collection using
> SELECT * FROM TABLE() syntax, that if they dump the contents to temporary
> table then performance is better (uses a suitable index).
> I have simplified their construct to the simplest possible test case I can
> at the moment. Unfortunately re-writing their SQL construct is not a
> possibility at the moment.
> It seems somehow to me that when casting the PL/SQL collection, then the
> predicate is not getting passed to the subquery.
> Does anybody have any ideas about what is going on. Is this expected
> behviour, or a known bug?
> Any ideas how to work around it? Very hesitant to allow them to be
> dumping data into temporary tables for getting better explain plans, but a
> little difficult to argue against their apparent peformance benefits.
> Thanks in advance, paste below steps to reproduce.
> Note the cardinality hint makes it simpler to reproduce, otherwise it
> takes a couple of executions before cardinality feedback kicks in to reduce
> initial estimated cardinality of 8192 (from either the collection or GTT).
>
>
> CREATE TABLE TEST_OBJECTS AS SELECT * FROM DBA_OBJECTS;
> CREATE INDEX TEST_OBJECTS_IDX ON TEST_OBJECTS(OBJECT_ID);
> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'TEST_OBJECTS');
>
> CREATE GLOBAL TEMPORARY TABLE GTT_TEST_OBJECTS (object_id number);
> CREATE OR REPLACE TYPE NUMBER_TABLE IS TABLE OF NUMBER;
>
> INSERT INTO GTT_TEST_OBJECTS VALUES (1);
> COMMIT;
>
> EXPLAIN PLAN FOR
> SELECT /*+ gather_plan_statistics */ null
> FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS)
> WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */
> object_id FROM GTT_TEST_OBJECTS);
>
> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
>
> ------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time
> |
>
> ------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 1 | | 27
> (8)| 00:00:01
> |
>
> | 1 | VIEW | VM_NWVW_1 | 1 | | 27
> (8)| 00:00:01
> |
>
> | 2 | SORT UNIQUE NOSORT | | 1 | 19 | 27
> (8)| 00:00:01
> |
>
> | 3 | NESTED LOOPS | | 1 | 19 | 26
> (4)| 00:00:01
> |
>
> | 4 | SORT UNIQUE | | 1 | 13 | 24
> (0)| 00:00:01
> |
>
> | 5 | TABLE ACCESS FULL| GTT_TEST_OBJECTS | 1 | 13 | 24
> (0)| 00:00:01
> |
>
> |* 6 | INDEX RANGE SCAN | TEST_OBJECTS_IDX | 1 | 6 | 1
> (0)| 00:00:01
> |
>
> ------------------------------------------------------------------------------------------
>
>
>
> Predicate Information (identified by operation
> id):
>
> ---------------------------------------------------
>
>
>
> 6 - access("OBJECT_ID"="OBJECT_ID")
>
> EXPLAIN PLAN FOR
> SELECT /*+ gather_plan_statistics */ null
> FROM (SELECT DISTINCT OBJECT_ID FROM TEST_OBJECTS)
> WHERE OBJECT_ID IN (SELECT /*+ CARDINALITY(GTT_TEST_OBJECTS 1) */
> object_id FROM TABLE(NUMBER_TABLE(1)));
>
> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
>
> ----------------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows |
> Bytes |TempSpc| Cost (%CPU)| Time
> |
>
> ----------------------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 5967 |
> 35802 | | 220K (1)| 00:44:08
> |
>
> |* 1 | FILTER | |
> | | | |
> |
>
> | 2 | VIEW | | 119K|
> 699K| | 542 (3)| 00:00:07
> |
>
> | 3 | HASH UNIQUE | | 119K|
> 699K| 1416K| 542 (3)| 00:00:07
> |
>
> | 4 | TABLE ACCESS FULL | TEST_OBJECTS | 119K|
> 699K| | 145 (3)| 00:00:02
> |
>
> |* 5 | FILTER | |
> | | | |
> |
>
> | 6 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1
> | | | 2 (0)| 00:00:01
> |
>
> ----------------------------------------------------------------------------------------------------------------
>
>
>
> Predicate Information (identified by operation
> id):
>
> ---------------------------------------------------
>
>
>
> 1 - filter( EXISTS (SELECT 0 FROM TABLE() "KOKBF$0" WHERE
> :B1=:B2))
>
> 5 -
> filter(:B1=:B2)
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 05 2015 - 01:46:43 CEST