C:\>sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Thu May 4 23:49:02 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: scott/[email]tiger@db02.world[/email]

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

create table objects as select * from dba_objects;
create table objects1 as select * from dba_objects;
update objects1 set object_id = 17287 where rownum<150;

update objects1 set object_id = 7559 where rownum<150 and object_id <>  17287;

update objects1 set object_id = 22543 where rownum<150 and object_id <>  17287 and object_id <> 7559;

update objects1 set object_id = 13615 where rownum<150 and object_id <>  17287 and object_id <> 7559 and object_id <> 22543;

update objects1 set object_id = 10173 where  object_id <>  17287 and object_id <> 7559 and object_id <> 22543 and object_id <> 13615;
commit;
alter table objects add constraint pk_objects primary key(object_id);
alter table objects1 add constraint fk_objects1 foreign key(object_id) references objects(object_id)

analyze tables / indexes comput statistics 
******************************************

SQL> set autotrace traceonly
SQL> select o.object_name,o1.owner from objects o, objects1 o1 where o.object_id = o1.object_id;

30058 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'OBJECTS1'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'OBJECTS'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_OBJECTS' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      64515  consistent gets
          0  physical reads
          0  redo size
     814299  bytes sent via SQL*Net to client
     262888  bytes received via SQL*Net from client
       2005  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30058  rows processed

SQL> select o.object_name,o1.owner from objects o1, objects1 o where o.object_id = o1.object_id;

30058 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'OBJECTS1'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'OBJECTS'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_OBJECTS' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      64515  consistent gets
          0  physical reads
          0  redo size
    1632112  bytes sent via SQL*Net to client
     262888  bytes received via SQL*Net from client
       2005  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30058  rows processed

SQL> create index fk_objects1 on objects1(object_id);

Index created.
Analyze tables / indexes 

SQL> select o.object_name,o1.owner from objects o, objects1 o1 where o.object_id = o1.object_id;

30058 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=185 Card=30058 Bytes
          =3276322)

   1    0   HASH JOIN (Cost=185 Card=30058 Bytes=3276322)
   2    1     TABLE ACCESS (FULL) OF 'OBJECTS1' (Cost=43 Card=30058 By
          tes=901740)

   3    1     TABLE ACCESS (FULL) OF 'OBJECTS' (Cost=43 Card=30057 Byt
          es=2374503)





Statistics
----------------------------------------------------------
         42  recursive calls
          2  db block gets
        837  consistent gets
         16  physical reads
          0  redo size
     814307  bytes sent via SQL*Net to client
     262888  bytes received via SQL*Net from client
       2005  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30058  rows processed

SQL> select o.object_name,o1.owner from objects o1, objects1 o where o.object_id = o1.object_id;

30058 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=185 Card=30058 Bytes
          =3276322)

   1    0   HASH JOIN (Cost=185 Card=30058 Bytes=3276322)
   2    1     TABLE ACCESS (FULL) OF 'OBJECTS' (Cost=43 Card=30057 Byt
          es=901710)

   3    1     TABLE ACCESS (FULL) OF 'OBJECTS1' (Cost=43 Card=30058 By
          tes=2374582)





Statistics
----------------------------------------------------------
         42  recursive calls
          0  db block gets
       2784  consistent gets
         63  physical reads
          0  redo size
    1632112  bytes sent via SQL*Net to client
     262888  bytes received via SQL*Net from client
       2005  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30058  rows processed

--
-- I am setting up my environment
-- Just regular emp/dept tables. I modified the deptno number(2) to deptno number
--
scott@9i > get emp_loopinsert
  1   begin
  2   for mag in 1..15 loop
  3       insert into emp (select * from emp);
  4   end loop;
  5   commit;
  6*  end;
scott@9i > /

PL/SQL procedure successfully completed.

scott@9i > get dept_loopinsert
  1   begin
  2   for mag in 1..15 loop
  3       insert into dept (select * from dept);
  4   end loop;
  5   commit;
  6*  end;
scott@9i > /

PL/SQL procedure successfully completed.

scott@9i > update dept set deptno=rownum;

131072 rows updated.

scott@9i > commit;

Commit complete.

scott@9i > alter table dept add constraint pk_deptno primary key (deptno);

Table altered.

--
-- What you might have done is
--
scott@9i > analyze table dept compute statistics for all columns for all indexes;

Table analyzed.

scott@9i > analyze table emp compute statistics for all columns for all indexes;

Table analyzed.

scott@9i > set autotrace traceonly exp;
scott@9i > select E.empno,D.dname from emp E, dept D where e.deptno=d.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPTNO' (UNIQUE)

--
-- Now let us create the second index, you were talking about.
-- Now, you can see the other nested loop plan that uses a RANGE SCAN.
--

  1* create index index_deptno on emp(deptno)
scott@9i > /

Index created.

scott@9i > analyze table dept compute statistics for all columns for all indexes;

Table analyzed.

scott@9i > analyze table emp compute statistics for all columns for all indexes;

Table analyzed.

scott@9i > select E.empno,D.dname from emp E, dept D where e.deptno=d.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    2       INDEX (RANGE SCAN) OF 'INDEX_DEPTNO' (NON-UNIQUE)

--
-- What you should have done is
--
scott@9i > get gather_table_stats
  1* exec dbms_stats.gather_table_stats('SCOTT',upper('&TABLE_NAME'),METHOD_OPT=>'For all indexed columns size 250',CASCADE=>true);
scott@9i > @gather_table_stats
Enter value for table_name: EMP

PL/SQL procedure successfully completed.

scott@9i > @gather_table_stats
Enter value for table_name: DEPT

PL/SQL procedure successfully completed.

scott@9i > select E.empno,D.dname from emp E, dept D where e.deptno=d.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=801 Card=458752 Byte
          s=9633792)

   1    0   HASH JOIN (Cost=801 Card=458752 Bytes=9633792)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=49 Card=131072 Bytes
          =1835008)

   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=458752 Bytes
          =3211264)

/*
--
-- So with a Nested loop, for each row in the driving table you search an
-- equal value in the other table.
-- In Hash Join, you using the smaller table , hash it and probe the Bigger table.
-- You can see from the Card, DEPT is the smaller table that is hashed.
-- Let us cross-check that Card and play with the statistics see whether it holds true.
--
*/
scott@9i > set autotrace off;
scott@9i > select table_name,num_rows from user_tables where table_name in ('EMP','DEPT');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
DEPT                               131072
EMP                                458752

scott@9i > exec dbms_stats.set_table_stats('SCOTT','EMP',numrows=>100);

PL/SQL procedure successfully completed.

/*
-- let us trick the CBO. Now CBO thinks EMP has only 100 records.
-- When running the same query, driving table is changed.
*/

scott@9i > select table_name,num_rows from user_tables where table_name in ('EMP','DEPT');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
DEPT                               131072
EMP                                   100

scott@9i > set autotrace traceonly exp
scott@9i > select E.empno,D.dname from emp E, dept D where e.deptno=d.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=312 Card=100 Bytes=2
          100)

   1    0   HASH JOIN (Cost=312 Card=100 Bytes=2100)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=100 Bytes=70
          0)

   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=49 Card=131072 Bytes

/*
-- Now What is up with this SortMerge Join?
-- here Both the tables are sorted first and then Merged
-- This will typicall occurs when Both the involved tables are massively large
-- So let us tweak the stats again.
*/
scott@9i > @gather_table_stats
Enter value for table_name: EMP

PL/SQL procedure successfully completed.

scott@9i > @gather_table_stats
Enter value for table_name: dept

PL/SQL procedure successfully completed.

scott@9i > set autotrace off;
scott@9i > select table_name,num_rows from user_tables where table_name in ('DEPT','EMP');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
DEPT                               131072
EMP                                458752

scott@9i >  exec dbms_stats.set_table_stats('SCOTT','DEPT',numrows=>100000000);

PL/SQL procedure successfully completed.

scott@9i > set autotrace traceonly exp
scott@9i > select E.empno,D.dname from emp E, dept D where e.deptno=d.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3768 Card=350000000
          Bytes=7350000000)

   1    0   MERGE JOIN (Cost=3768 Card=350000000 Bytes=7350000000)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=726 Card=1
          00000000 Bytes=1400000000)

   3    2       INDEX (FULL SCAN) OF 'PK_DEPTNO' (UNIQUE) (Cost=274 Ca
          rd=100000000)

   4    1     SORT (JOIN) (Cost=3042 Card=458752 Bytes=3211264)
   5    4       TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=458752 Byt
          es=3211264)