oracle hint no_merge,hints "no_merge" 的用法

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>