Prevents Merging An Inline View

Quote from oracle doc：

Using the NO_MERGE Hint

The NO_MERGE hint prevents the database from merging an inline view into a potentially non-collocated SQL statement (see "Using Hints"). This hint is embedded in the SELECT statement and can appear either at the beginning of the SELECT statement with the inline view as an argument or in the query block that defines the inline view.

My Test：

SQL> SELECT e1.ename, dallasdept.dname

2 FROM scott.emp e1,

3 (SELECT deptno, dname

4 FROM scott.dept

5 WHERE loc = 'DALLAS') dallasdept

6 WHERE e1.deptno = dallasdept.deptno;

未選定行

已用時間: 00: 00: 00.01

執行計劃

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

Plan hash value: 351108634

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

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

| 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 29 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 1 | 9 | 3 (0)| 00:00:01 |

|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - filter("LOC"='DALLAS')

4 - access("E1"."DEPTNO"="DEPTNO")

統計資訊

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

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

SQL> /

未選定行

已用時間: 00: 00: 00.00

執行計劃

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

Plan hash value: 2546051495

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

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

| 0 | SELECT STATEMENT | | 1 | 31 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 31 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 1 | 9 | 3 (0)| 00:00:01 |

| 3 | VIEW | | 1 | 22 | 3 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("E1"."DEPTNO"="DALLASDEPT"."DEPTNO")

4 - filter("LOC"='DALLAS')

統計資訊

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

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

326 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> l

1 SELECT /*+NO_MERGE(dallasdept)*/ e1.ename, dallasdept.dname

2 FROM scott.emp e1,

3 (SELECT deptno, dname

4 FROM scott.dept

5 WHERE loc = 'DALLAS') dallasdept

6* WHERE e1.deptno = dallasdept.deptno

Can not View Merging conversion will appear in the query plan of “VIEW”

SQL> /

已選擇1777行。

已用時間:  00: 00: 15.82
執行計劃
----------------------------------------------------------
Plan hash value: 3132434474

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |    | 102M| 23G|    | 23561  (98)| 00:04:43 |
|*  1 |  FILTER                |    |    |    |    |          |       |
| 2 | HASH GROUP BY       |    | 102M| 23G|    | 23561  (98)| 00:04:43 |
|*  3 | HASH JOIN          |    | 102M| 23G|    |  2407  (75)| 00:00:29 |
|*  4 |    TABLE ACCESS FULL | A |  2790 | 514K|    | 156 (2)| 00:00:02 |
|*  5 |    HASH JOIN RIGHT OUTER|    | 515K| 25M|  1016K| 486 (4)| 00:00:06 |
| 6 |    TABLE ACCESS FULL | B | 41410 | 525K|    | 155 (2)| 00:00:02 |
| 7 |    TABLE ACCESS FULL | A | 46902 |  1786K|    | 156 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

1 - filter("A"."CREATED">=MIN("A"."CREATED"))
3 - access("A"."OWNER"="A"."OWNER")
4 - filter("A"."OBJECT_TYPE"='INDEX')
5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID"(+))

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
      0  recursive calls
      0  db block gets
2091  consistent gets       0  physical reads
      0  redo size
   116065  bytes sent via SQL*Net to client
   1683  bytes received via SQL*Net from client
      120  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     1777  rows processed
SQL> l
  1 select  *
  2    from a,
  3          (select  a.owner, min(a.created) created
  4             from a
  5            left outer join b on a.object_id = b.data_object_id  6             group by a.owner) temp
  7    where a.owner = temp.owner
  8       and a.object_type = 'INDEX'
  9*    and a.created >= temp.created

hint no_merge：

SQL> select /*+ no_merge(temp) */ *
2    from a,
3         (select a.owner, min(a.created) created
4            from a
5            left outer join b on a.object_id = b.data_object_id
6           group by a.owner) temp
7   where a.owner = temp.owner
8     and a.object_type = 'INDEX'
9     and a.created >= temp.created;

已選擇1777行。

已用時間: 00: 00: 00.12

執行計劃
----------------------------------------------------------
Plan hash value: 3851281404

-----------------------------------------------------------------------------------------
| Id | Operation                | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |   467K|    90M|       |   802 (23)| 00:00:10 |
|* 1 | HASH JOIN               |      |   467K|    90M|       |   802 (23)| 00:00:10 |
|* 2 |   TABLE ACCESS FULL      | A    | 2790 |   482K|       |   156   (2)| 00:00:02 |
|   3 |   VIEW                   |      | 46902 | 1190K|       |   485   (4)| 00:00:06 |
|   4 |    HASH GROUP BY         |      | 46902 | 2381K|       |   485   (4)| 00:00:06 |
|* 5 |     HASH JOIN RIGHT OUTER|      | 46902 | 2381K| 1016K|   478   (2)| 00:00:06 |
|   6 |      TABLE ACCESS FULL   | B    | 41410 |   525K|       |   155   (2)| 00:00:02 |
|   7 |      TABLE ACCESS FULL   | A    | 46902 | 1786K|       |   156   (2)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   1 - access("A"."OWNER"="TEMP"."OWNER")
       filter("A"."CREATED">="TEMP"."CREATED")
   2 - filter("A"."OBJECT_TYPE"='INDEX')
   5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID"(+))

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       2091 consistent gets
          0 physical reads

Conclusion：

1 Can not View Merging conversion will appear in the query plan of “VIEW”；

2 tun Outer Joins to + will be possible more efficient，but it will be stands in the way of VIEW MERGE;

3 hint NO_MERGE will Speed up the query time，Views are often not performance friendly!

---------END------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-721600/，如需轉載，請註明出處，否則將追究法律責任。

Prevents Merging An Inline View

相關文章