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

 

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

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-721600/