quote :
oracle doc
This hint lets the user have more influence over the way in which the view is accessed.
For example:
SELECT /*+NO_MERGE(dallasdept)*/ e1.ename, dallasdept.dname
FROM emp e1,
(SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS') dallasdept
WHERE e1.deptno = dallasdept.deptno;
This causes view dallasdept not to be merged.
When the NO_MERGE hint is used without an argument, it should be placed in the view query block. When NO_MERGE is used with the view name as an argument, it should be placed in the surrounding query.
http://otn.oracle.com/docs/produ ... 3/hintsref.htm#6051
实例
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> SET pages 0 lin 80 autotrace traceonly timing on
SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd';
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> SELECT a.ua_abbr ua_abbr,
2 a.area_name area_name,
3 a.eff_date eff_date,
4 a.exp_date exp_date,
5 ROUND (a.def_price / 7.8, 4) def_price,
6 ROUND (b.avg_rate / 7.8, 4) avg_rate,
7 ROUND ((a.def_price - b.avg_rate) / 7.8, 4) difference
8 FROM (SELECT r.ua_id,
9 r.ua_abbr,
10 r.area_name,
11 r.eff_date,
12 r.exp_date,
13 r.def_price,
14 r.area_id
15 FROM cust_def_rates r
16 WHERE exp_date >= '2003-07-05'
17 AND ra_flag NOT IN (-8, -2, 32, 65536)) a,
18 (SELECT area_in area_id,
19 ROUND (SUM (rate_out * time_out) / SUM (time_out), 5) avg_rate
20 FROM cdr_billed
21 WHERE answer_time >= '2003-06-01'
22 AND answer_time < '2003-06-30'
23 GROUP BY area_in) b
24 WHERE a.area_id = b.area_id
25 AND a.def_price <= b.avg_rate
26 ORDER BY ua_abbr,
27 area_name,
28 eff_date;
2520 rows selected.
Elapsed: 00:02:04.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=1 Bytes=148)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=86 Card=1 Bytes=148)
3 2 HASH JOIN (Cost=84 Card=1 Bytes=148)
4 3 TABLE ACCESS (FULL) OF 'CUST_DEF_RATES' (Cost=29 Car
d=1 Bytes=100)
5 3 PARTITION RANGE (ITERATOR)
6 5 TABLE ACCESS (FULL) OF 'CDR_BILLED' (Cost=54 Card=
177 Bytes=8496)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1385 consistent gets
0 physical reads
0 redo size
96476 bytes sent via SQL*Net to client
1439 bytes received via SQL*Net from client
169 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2520 rows processed
SQL>
SQL>
SQL> SELECT /*+ NO_MERGE(A) NO_MERGE(B) */
2 a.ua_abbr ua_abbr,
3 a.area_name area_name,
4 a.eff_date eff_date,
5 a.exp_date exp_date,
6 ROUND (a.def_price / 7.8, 4) def_price,
7 ROUND (b.avg_rate / 7.8, 4) avg_rate,
8 ROUND ((a.def_price - b.avg_rate) / 7.8, 4) difference
9 FROM (SELECT r.ua_id,
10 r.ua_abbr,
11 r.area_name,
12 r.eff_date,
13 r.exp_date,
14 r.def_price,
15 r.area_id
16 FROM cust_def_rates r
17 WHERE exp_date >= '2003-07-05'
18 AND ra_flag NOT IN (-8, -2, 32, 65536)) a,
19 (SELECT area_in area_id,
20 ROUND (SUM (rate_out * time_out) / SUM (time_out), 5) avg_rate
21 FROM cdr_billed
22 WHERE answer_time >= '2003-06-01'
23 AND answer_time < '2003-06-30'
24 GROUP BY area_in) b
25 WHERE a.area_id = b.area_id
26 AND a.def_price <= b.avg_rate
27 ORDER BY ua_abbr,
28 area_name,
29 eff_date;
2520 rows selected.
Elapsed: 00:00:01.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=1 Bytes=108)
1 0 SORT (ORDER BY) (Cost=88 Card=1 Bytes=108)
2 1 HASH JOIN (Cost=86 Card=1 Bytes=108)
3 2 VIEW (Cost=29 Card=1 Bytes=82)
4 3 TABLE ACCESS (FULL) OF 'CUST_DEF_RATES' (Cost=29 Car
d=1 Bytes=93)
5 2 VIEW (Cost=56 Card=177 Bytes=4602)
6 5 SORT (GROUP BY) (Cost=56 Card=177 Bytes=8496)
7 6 PARTITION RANGE (ITERATOR)
8 7 TABLE ACCESS (FULL) OF 'CDR_BILLED' (Cost=54 Car
d=177 Bytes=8496)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1385 consistent gets
0 physical reads
0 redo size
97492 bytes sent via SQL*Net to client
1439 bytes received via SQL*Net from client
169 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2520 rows processed
SQL>