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

Question

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?

Answer 1

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)

• Rupesh: My apologies, the second one you did was correct. In the actual query, apart from MV which is on 2 tables, there are 3 other tables. I will retry again by using the hint to see how optimizer behaves.