How to force a Nested Loop join on Materialized View and Table in Oracle 12c

execution-planhintsmaterialized-viewoptimizationoracle

There is a third party app which generates a high-cost query that has a join on multiple tables.

I created a materialized view on some of the tables using the same join that is originated in the Application query and used rewrite hint. The hint is respected by the optimizer and the materialized view is used. What should I do to make optimizer perform a nested loop join between the materialized view and other tables?

Eg:

SELECT  /*+ rewrite */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :1;

I created a Materialized view as below:

CREATE MATERIALIZED VIEW tst_mv
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3
      FROM tab1, tab2, tab3
     WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

I tried with the hint as /*+ rewrite use_nl(tst_mv tab4) */ but no luck. Any pointers?

Best Answer

Is that really your query and MV? tab3.col1 is not in it, but joining tab4 requires it because of tab4.col1 = tab3.col1. tab3.col1 = tab2.col2, so tab2.col2 = tab4.col1, but tab2.col2 is also not in the MV.

If I create this:

create table tab1(col1 number, col2 number, col3 number);
create table tab2(col1 number, col2 number);
create table tab3(col1 number);
create table tab4(col1 number);
alter session set query_rewrite_integrity=stale_tolerated;
CREATE MATERIALIZED VIEW tst_mv
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3
      FROM tab1, tab2, tab3
       WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;


explain plan for
SELECT  /*+ rewrite */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :B1;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1776227436

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    91 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    91 |     8   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    78 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN          |      |     1 |    65 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | TAB1 |     1 |    39 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL | TAB2 |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |    BUFFER SORT        |      |     1 |    13 |     4   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL | TAB4 |     1 |    13 |     2   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL   | TAB3 |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("TAB2"."COL2"="TAB3"."COL1" AND
              "TAB4"."COL1"="TAB3"."COL1")
   3 - access("TAB1"."COL1"="TAB2"."COL1")
   5 - filter("TAB2"."COL2"=TO_NUMBER(:B1))
   7 - filter("TAB4"."COL1"=TO_NUMBER(:B1))
   8 - filter("TAB3"."COL1"=TO_NUMBER(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

TST_MV is not used. If I create another MV with tab3.col1 included, then add use_nl, that MV is used with a nested loops join:

CREATE MATERIALIZED VIEW tst_mv2
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3, tab3.col1 as tab3_col1
      FROM tab1, tab2, tab3
       WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

explain plan for
SELECT  /*+ rewrite use_nl(tab4) */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :B1;

 select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1384405329

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    65 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |         |     1 |    65 |     4   (0)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| TST_MV2 |     1 |    52 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL           | TAB4    |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter("TST_MV2"."TAB3_COL1"=TO_NUMBER(:B1))
   3 - filter("TAB4"."COL1"=TO_NUMBER(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Related Question