oracle 变量不起作用,为什么提示不起作用呢？ /*+USE_NL()*/

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> create table test as select * from dba_objects;

表已创建。

SQL> insert into test select * from test;

已创建47981行。

SQL> /

已创建95962行。

SQL> /

已创建191924行。

SQL> /

已创建383848行。

SQL> commit;

提交完成。

SQL> create table test1 as select * from dba_objects where rownum < 1000;

表已创建。

SQL> analyze table test compute statistics;

表已分析。

SQL>

SQL> analyze table test1 compute statistics;

表已分析。

SQL> set autotrace on

SQL> select /*+USE_NL(TEST1)*/ count(1) from test,test1 where test.object_id=tes

t1.object_id;

15984

执行计划

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

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2331 Card=1 Bytes=

7)

1    0   SORT (AGGREGATE)

2    1     HASH JOIN (Cost=2331 Card=15984 Bytes=111888)

3    2       TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=4 Card=99

9 Bytes=2997)

4    2       TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2319 Card=

767696 Bytes=3070784)

统计信息

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

1  recursive calls

0  db block gets

10545  consistent gets

9828  physical reads

0  redo size

392  bytes sent via SQL*Net to client

512  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ use_nl(test,test1) */ count(1) from test,test1 where test.object

_id=test1.object_id;

15984

执行计划

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

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2038396 Card=1 Byt

es=7)

1    0   SORT (AGGREGATE)

2    1     NESTED LOOPS (Cost=2038396 Card=15984 Bytes=111888)

3    2       TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2319 Card=

767696 Bytes=3070784)

4    2       TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=3 Card=1

Bytes=3)

统计信息

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

1  recursive calls

0  db block gets

11525970  consistent gets

9833  physical reads

0  redo size

392  bytes sent via SQL*Net to client

512  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed