Skip to Main Content
  • Questions

  • Join Predicate not pushing down to Complex Inline View

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Derek.

Asked: October 15, 2015 - 3:16 am UTC

Last updated: October 16, 2015 - 6:29 am UTC

Version: 11.2.0.1

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have just faced a problem in my work.

Here is a similar case for my work.

CREATE TABLE TABLE_1 
(
MY_ID VARCHAR2(20),
REVISION NUMBER(3,0),
DATE_FROM DATE,
DATE_TO DATE,
STATUS VARCHAR2(50),
MS_USER VARCHAR2(50)
);

ALTER TABLE TABLE_1 
ADD CONSTRAINT TABLE1_PK PRIMARY KEY (MY_ID);

CREATE TABLE TABLE_2 
(
MY_ID VARCHAR2(20) ,
REVISION NUMBER(3,0),
DATE_FROM DATE,
DATE_TO DATE,
STATUS VARCHAR2(50),
MS_USER VARCHAR2(50)
);



ALTER TABLE TABLE_2
ADD CONSTRAINT TABLE2_PK PRIMARY KEY (MY_ID);

CREATE TABLE TABLE_extra 
(
extra_ID VARCHAR2(20),
EXT_MY_ID VARCHAR2(20)
);
ALTER TABLE TABLE_extra
ADD CONSTRAINT TABLEext_PK PRIMARY KEY (extra_ID);


,and which Table_1, Table_2 is extremely large

I try the following SQL.
but it full scan the table_1, and table_2 completely rather than pushing the join predicate down the in-line view.

SELECT /*+ PUSH_PRED(inline_1) */ *
FROM TABLE_extra 
JOIN ( SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
       FROM TABLE_1
       WHERE STATUS <> 'DELETE'
       GROUP BY MY_ID, MS_USER
       UNION
       SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
       FROM TABLE_2
       WHERE STATUS <> 'DELETE'
       GROUP BY MY_ID, MS_USER
) inline_1 ON TABLE_extra.EXT_MY_ID = inline_1. MY_ID
WHERE TABLE_extra.EXTRA_ID = '123456';


I suppose it will
index scan TABLE_extra by extra_ID,
then get the relevant MY_ID information from TABLE_1 using Primary Key Index
then get the relevant MY_ID information from TABLE_2 using Primary Key Index
then union them together
at least, join them together.

However, actually it does is
index scan TABLE_extra by extra_ID
Full Scan TABLE_1
Full Scan TABLE_2
Union both TABLE_1 and TABLE_2
at last, join them together.

Explain plan for the sql:
Other XML  
  {info}  
  info type="db_version"  
  11.2.0.1  
  info type="parse_schema"  
  "testDB"  
  info type="dynamic_sampling"  
  2  
  info type="plan_hash"  
  909077764  
  info type="plan_hash_2"  
  1699902986  
  {hint}  
  USE_HASH_AGGREGATION(@"SEL$2")  
  FULL(@"SEL$2" "TABLE_1"@"SEL$2")  
  USE_HASH_AGGREGATION(@"SEL$3")  
  FULL(@"SEL$3" "TABLE_2"@"SEL$3")  
  USE_NL(@"SEL$16C51A37" "INLINE_1"@"SEL$1")  
  LEADING(@"SEL$16C51A37" "TABLE_EXTRA"@"SEL$1" "INLINE_1"@"SEL$1")  
  NO_ACCESS(@"SEL$16C51A37" "INLINE_1"@"SEL$1")  
  INDEX_RS_ASC(@"SEL$16C51A37" "TABLE_EXTRA"@"SEL$1" ("TABLE_EXTRA"."EXTRA_ID"))  
  OUTLINE(@"SEL$1")  
  OUTLINE(@"SEL$4")  
  MERGE(@"SEL$1")  
  OUTLINE_LEAF(@"SEL$16C51A37")  
  OUTLINE_LEAF(@"SET$1")  
  OUTLINE_LEAF(@"SEL$3")  
  OUTLINE_LEAF(@"SEL$2")  
  ALL_ROWS  
  DB_VERSION('11.2.0.1')  
  OPTIMIZER_FEATURES_ENABLE('11.2.0.1')  
  IGNORE_OPTIM_EMBEDDED_HINTS


I try to use push_pred() hint on the inline_1 but nothing happened,
please suggest how to optimize this kind of SQL which join push pred is not working.

Thank you,
Derek

and Connor said...

Puhsing predicates into subqueries containing aggregates is always a challenge (for the optimizer and for us) because you never know if by doing you might change the result.

See https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:3469884600671 for a good example

But we can work around it if you're prepared to use some object types. Here's a top to bottom example, showing the poor plan and how to alter the SQL to make it a better one.

SQL> CREATE TABLE TABLE_1
  2  (
  3  MY_ID VARCHAR2(20),
  4  REVISION NUMBER(3,0),
  5  DATE_FROM DATE,
  6  DATE_TO DATE,
  7  STATUS VARCHAR2(50),
  8  MS_USER VARCHAR2(50)
  9  );

Table created.

SQL> insert /*+ APPEND */ into table_1
  2  select rownum, mod(rownum,10), trunc(sysdate)+mod(rownum,20), trunc(sysdate)+50 + mod(rownum,20), 'X', 'user'
  3  from
  4    ( select 1 from dual connect by level <= 1000 ) ,
  5    ( select 1 from dual connect by level <= 5000 );

5000000 rows created.

SQL> ALTER TABLE TABLE_1  ADD CONSTRAINT TABLE1_PK PRIMARY KEY (MY_ID);

Table altered.

SQL> CREATE TABLE TABLE_2
  2  (
  3  MY_ID VARCHAR2(20) ,
  4  REVISION NUMBER(3,0),
  5  DATE_FROM DATE,
  6  DATE_TO DATE,
  7  STATUS VARCHAR2(50),
  8  MS_USER VARCHAR2(50)
  9  );

Table created.

SQL> insert /*+ APPEND */ into table_2
  2  select rownum, mod(rownum,10), trunc(sysdate)+mod(rownum,20), trunc(sysdate)+50 + mod(rownum,20), 'X', 'user'
  3  from
  4    ( select 1 from dual connect by level <= 1000 ) ,
  5    ( select 1 from dual connect by level <= 5000 );

5000000 rows created.

SQL> ALTER TABLE TABLE_2
  2  ADD CONSTRAINT TABLE2_PK PRIMARY KEY (MY_ID);

Table altered.

SQL> CREATE TABLE TABLE_extra
  2  (
  3  extra_ID VARCHAR2(20),
  4  EXT_MY_ID VARCHAR2(20)
  5  );

Table created.

SQL> ALTER TABLE TABLE_extra
  2  ADD CONSTRAINT TABLEext_PK PRIMARY KEY (extra_ID);

Table altered.

SQL> insert into TABLE_extra select 123450+rownum, rownum+1000 from dual connect by level <= 20;

20 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','TABLE_extra')

PL/SQL procedure successfully completed.

SQL> @exp
  5  SELECT /*+ PUSH_PRED(inline_1) */ *
  6  FROM TABLE_extra
  7  JOIN (
  8    SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
  9    FROM TABLE_1
 10    WHERE STATUS <> 'DELETE'
 11    GROUP BY MY_ID, MS_USER
 12    UNION
 13    SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
 14    FROM TABLE_2
 15    WHERE STATUS <> 'DELETE'
 16    GROUP BY MY_ID, MS_USER
 17  ) inline_1
 18  ON TABLE_extra.EXT_MY_ID = inline_1. MY_ID
 19  WHERE TABLE_extra.EXTRA_ID = '123456';

Explained.

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   353K|    32M|       |   162K  (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |             |   353K|    32M|       |   162K  (1)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE_EXTRA |     1 |    12 |       |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | TABLEEXT_PK |     1 |       |       |     0   (0)| 00:00:01 |
|*  4 |   VIEW                       |             |   353K|    27M|       |   162K  (1)| 00:00:07 |
|   5 |    SORT UNIQUE               |             |  7071K|   229M|   497M|   162K  (1)| 00:00:07 |
|   6 |     UNION-ALL                |             |       |       |       |            |          |
|   7 |      SORT GROUP BY           |             |  3535K|   114M|   248M| 81047   (1)| 00:00:04 |
|*  8 |       TABLE ACCESS FULL      | TABLE_1     |  4999K|   162M|       |  7362   (1)| 00:00:01 |
|   9 |      SORT GROUP BY           |             |  3535K|   114M|   248M| 81047   (1)| 00:00:04 |
|* 10 |       TABLE ACCESS FULL      | TABLE_2     |  4999K|   162M|       |  7362   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TABLE_EXTRA"."EXTRA_ID"='123456')
   4 - filter("TABLE_EXTRA"."EXT_MY_ID"="INLINE_1"."MY_ID")
   8 - filter("STATUS"<>'DELETE')
  10 - filter("STATUS"<>'DELETE')

25 rows selected.

SQL> drop type inline_stuff_list;

Type dropped.

SQL> create or replace
  2  type inline_stuff_row as object (
  3    MY_ID VARCHAR2(20) ,
  4    MS_USER VARCHAR2(50),
  5    MIN_REVISION NUMBER(3,0),
  6    MAX_REVISION NUMBER(3,0),
  7    DATE_FROM DATE,
  8    DATE_TO DATE
  9  )
 10  /

Type created.

SQL> create or replace
  2  type inline_stuff_list as table of inline_stuff_Row
  3  /

Type created.

SQL> @exp1
  5  SELECT  *
  6  FROM TABLE_extra,
  7       TABLE(CAST(MULTISET(
  8                      SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
  9                      FROM TABLE_1
 10                      WHERE STATUS <> 'DELETE'
 11                      AND MY_ID = TABLE_extra.EXT_MY_ID
 12                      GROUP BY MY_ID, MS_USER
 13                      UNION
 14                      SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
 15                      FROM TABLE_2
 16                      WHERE STATUS <> 'DELETE'
 17                      AND MY_ID = TABLE_extra.EXT_MY_ID
 18                      GROUP BY MY_ID, MS_USER)
 19             as inline_stuff_list)) inline_results
 20  where TABLE_extra.EXTRA_ID = '123456';

Explained.

------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |     2 |   190 |     9  (23)| 00:00:01 |
|   1 |  NESTED LOOPS                    |             |     2 |   190 |     9  (23)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID    | TABLE_EXTRA |     1 |    12 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN             | TABLEEXT_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   VIEW                           |             |     2 |   166 |     8  (25)| 00:00:01 |
|   5 |    SORT UNIQUE                   |             |     2 |    68 |     8  (25)| 00:00:01 |
|   6 |     UNION-ALL                    |             |       |       |            |          |
|   7 |      SORT GROUP BY               |             |     1 |    34 |     4  (25)| 00:00:01 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| TABLE_1     |     1 |    34 |     3   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | TABLE1_PK   |     1 |       |     2   (0)| 00:00:01 |
|  10 |      SORT GROUP BY               |             |     1 |    34 |     4  (25)| 00:00:01 |
|* 11 |       TABLE ACCESS BY INDEX ROWID| TABLE_2     |     1 |    34 |     3   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN         | TABLE2_PK   |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TABLE_EXTRA"."EXTRA_ID"='123456')
   8 - filter("STATUS"<>'DELETE')
   9 - access("MY_ID"="TABLE_EXTRA"."EXT_MY_ID")
  11 - filter("STATUS"<>'DELETE')
  12 - access("MY_ID"="TABLE_EXTRA"."EXT_MY_ID")

28 rows selected.

SQL> SELECT  *
  2  FROM TABLE_extra,
  3       TABLE(CAST(MULTISET(
  4                      SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
  5                      FROM TABLE_1
  6                      WHERE STATUS <> 'DELETE'
  7                      AND MY_ID = TABLE_extra.EXT_MY_ID
  8                      GROUP BY MY_ID, MS_USER
  9                      UNION
 10                      SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
 11                      FROM TABLE_2
 12                      WHERE STATUS <> 'DELETE'
 13                      AND MY_ID = TABLE_extra.EXT_MY_ID
 14                      GROUP BY MY_ID, MS_USER)
 15             as inline_stuff_list)) inline_results
 16  where TABLE_extra.EXTRA_ID = '123456';

EXTRA_ID             EXT_MY_ID            MY_ID
-------------------- -------------------- --------------------
MS_USER                                            MIN_REVISION MAX_REVISION DATE_FROM DATE_TO
-------------------------------------------------- ------------ ------------ --------- ---------
123456               1006                 1006
user                                                          6            6 21-OCT-15 10-DEC-15


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

In Oracle 12c Lateral Inline Views make it simpler

Oren Nakdimon (@DBoriented), October 15, 2015 - 12:58 pm UTC

In Oracle 12c you can achieve the same using Lateral Inline Views, without TABLE, MULTISET and user-defined types:

SELECT *
FROM TABLE_extra,LATERAL (
SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
FROM TABLE_1
WHERE STATUS <> 'DELETE'
AND MY_ID = TABLE_extra.EXT_MY_ID
GROUP BY MY_ID, MS_USER
UNION
SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
FROM TABLE_2
WHERE STATUS <> 'DELETE'
AND MY_ID = TABLE_extra.EXT_MY_ID
GROUP BY MY_ID, MS_USER) inline_results
where TABLE_extra.EXTRA_ID = '123456';

Connor McDonald
October 15, 2015 - 3:38 pm UTC

Very nice, thanks for sharing.

Derek Derek, October 16, 2015 - 2:33 am UTC

Hi,
I wonder what makes the optimizer thinks that it is not possible to push the join predicate down to the view.

As I did try to replicate the scenario with restructured table structure before I posted the question,
with this sql (without group by MS_USER), the optimizer actually push down the predicate to the inline view
SELECT  *
FROM TABLE_extra 
JOIN ( SELECT MY_ID, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
       FROM TABLE_1
       WHERE STATUS <> 'DELETE'
       GROUP BY MY_ID, MS_USER
       UNION
       SELECT MY_ID, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
       FROM TABLE_2
       WHERE STATUS <> 'DELETE'
       GROUP BY MY_ID
) inline_1 ON TABLE_extra.EXT_MY_ID = inline_1. MY_ID
WHERE TABLE_extra.EXTRA_ID = '123456';

Plan hash value: 4224569892
 
--------------------------------------------------------
| Id  | Operation                        | Name        |
--------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |
|   1 |  NESTED LOOPS                    |             |
|   2 |   TABLE ACCESS BY INDEX ROWID    | TABLE_EXTRA |
|   3 |    INDEX UNIQUE SCAN             | TABLEEXT_PK |
|   4 |   VIEW                           |             |
|   5 |    SORT UNIQUE                   |             |
|   6 |     UNION-ALL                    |             |
|   7 |      SORT GROUP BY               |             |
|   8 |       TABLE ACCESS BY INDEX ROWID| TABLE_1     |
|   9 |        INDEX FULL SCAN           | TABLE1_PK   |
|  10 |      SORT GROUP BY               |             |
|  11 |       TABLE ACCESS BY INDEX ROWID| TABLE_2     |
|  12 |        INDEX FULL SCAN           | TABLE2_PK   |
--------------------------------------------------------

It seems to me the behavior is not predictable and quite prone to error for programmers.


Thank you,
Derek
Connor McDonald
October 16, 2015 - 6:29 am UTC

Take a closer look at that plan...Its "INDEX FULL SCAN" not an index lookup. That predicate didnt make it

Its just a limitation of the optimizer. For example

SELECT /*+ PUSH_PRED(inline_1) */ *
FROM TABLE_extra 
JOIN ( 
  SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
  FROM TABLE_1
  WHERE STATUS <> 'DELETE'
  GROUP BY MY_ID, MS_USER
--  UNION ALL
--  SELECT MY_ID, MS_USER, MIN(REVISION), MAX(REVISION), MIN(DATE_FROM), MAX(DATE_TO)
--  FROM TABLE_2
--  WHERE STATUS <> 'DELETE'
--  GROUP BY MY_ID, MS_USER
) inline_1 
ON TABLE_extra.EXT_MY_ID = inline_1. MY_ID
WHERE TABLE_extra.EXTRA_ID = '123456';


*will* have the predicate pushed. So the optimizer can "cope" with one group-by in there, but the union-all plus additional group tips it over the "this is too hard" edge

Hope this helps
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library