Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> select value from V$parameter where name = 'optimizer_dynamic_sampling';

VALUE
--------------------------------------------------------------------------------

2



SQL> drop table test purge;

Table dropped.

SQL> create table test ( id, name ) as select object_id, object_name from all_objects;

Table created.

SQL> create unique index test_i  on test( id);

Index created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> create global temporary table test_tmp ( id number(10));

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------

     40299

SQL> insert into test_tmp select id from test where mod(id,2)=0 and rownum <= 5000;

5000 rows created.

SQL> set autotrace traceonly exp;

SQL>  select name from test, test_tmp where test.id=test_tmp.id;

Execution Plan
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=5000 Bytes
          =210000)

   1    0   HASH JOIN (Cost=53 Card=5000 Bytes=210000)
   2    1     TABLE ACCESS (FULL) OF 'TEST_TMP' (TABLE (TEMP)) (Cost=4
           Card=5000 Bytes=65000)

   3    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=4029
          9 Bytes=1168671)




SQL> rollback;

Rollback complete.

SQL>  insert into test_tmp select id from test where mod(id,2)=0 and rownum <= 5;

5 rows created.

SQL>  select name from test, test_tmp where test.id=test_tmp.id;

Execution Plan
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=5 Bytes=210
          )

   1    0   NESTED LOOPS (Cost=7 Card=5 Bytes=210)
   2    1     TABLE ACCESS (FULL) OF 'TEST_TMP' (TABLE (TEMP)) (Cost=2
           Card=5 Bytes=65)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=1
          Card=1 Bytes=29)

   4    3       INDEX (UNIQUE SCAN) OF 'TEST_I' (INDEX (UNIQUE)) (Cost
          =0 Card=1)
-- план поменялся для 5 записей имеем NESTED LOOPS


SQL> rollback;

Rollback complete.

SQL> set autotrace off;

-- отключаю dynamic_sampling

SQL> alter session set optimizer_dynamic_sampling = 0;

Session altered.

SQL>  insert into test_tmp select id from test where mod(id,2)=0 and rownum <= 5000;

5000 rows created.

SQL>  set autotrace traceonly exp;
SQL>  select name from test, test_tmp where test.id=test_tmp.id;

Execution Plan
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=73 Card=8168 Bytes
          =343056)

   1    0   HASH JOIN (Cost=73 Card=8168 Bytes=343056)
   2    1     TABLE ACCESS (FULL) OF 'TEST_TMP' (TABLE (TEMP)) (Cost=2
          4 Card=8168 Bytes=106184)

   3    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=4029
          9 Bytes=1168671)

SQL> rollback;

Rollback complete.

SQL>  insert into test_tmp select id from test where mod(id,2)=0 and rownum <= 5;

5 rows created.

SQL>  select name from test, test_tmp where test.id=test_tmp.id;

Execution Plan
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=73 Card=8168 Bytes
          =343056)

   1    0   HASH JOIN (Cost=73 Card=8168 Bytes=343056)
   2    1     TABLE ACCESS (FULL) OF 'TEST_TMP' (TABLE (TEMP)) (Cost=2
          4 Card=8168 Bytes=106184)

   3    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=4029
          9 Bytes=1168671)

-- план тот же , что и для 5000 записей



SQL> rollback;

Rollback complete.

SQL> set autotrace off;
SQL>  select value from V$parameter where name = 'optimizer_dynamic_sampling';

VALUE
--------------------------------------------------------------------------------

0