Re: Why INDEX SCAN instead of FULL SCAN?

Hi ,
The UNIQUE INDEX is created when you define the PRIMARY KEY. Thus any data inserted into he table would be indexed.

The Optimizer will decide which is the most efficient way to process any statement.
Using optimizer hints, you can change this.

09:35:22 SQL> _at_explain_plan sara 'SELECT * FROM d, e WHERE e_did = d_did'
old 1: delete from plan_table where statement_id = '&1' new 1: delete from plan_table where statement_id = 'sara'

0 rows deleted.

Elapsed: 00:00:00.63
Input truncated to 1 characters

old   2: set statement_id = '&1' for
new   2: set statement_id = 'sara' for
old   3:      &2
new   3:      SELECT * FROM d, e WHERE e_did = d_did

Explained.

Elapsed: 00:00:00.15
09:36:31 SQL> l
  1 explain plan
  2 set statement_id = '&1' for
  3* &2

09:36:37 SQL> _at_view_plan sara
old  15: and statement_id = '&1'
new  15: and statement_id = 'sara'
old  17: and statement_id = '&1'
new  17: and statement_id = 'sara'

Order      Level Execution Plan                                 OPT
---------- ----- ----------------------------------------      
----------
0-0-0      1.0   SELECT STATEMENT     sara Cost =               CHOOSE
1-0-1      2.1     NESTED LOOPS
2-1-1      3.1       TABLE ACCESS FULL E
3-1-2      3.2       TABLE ACCESS BY INDEX ROWID D
4-3-1      4.1         INDEX UNIQUE SCAN SYS_C00149253 UNIQUE

Elapsed: 00:00:00.31

09:38:00 SQL> _at_explain_plan sara 'SELECT /*+ use_hash(d,e) */ * FROM d, e WHERE e_did = d_did'
old 1: delete from plan_table where statement_id = '&1' new 1: delete from plan_table where statement_id = 'sara'

5 rows deleted.

Elapsed: 00:00:00.16
Input truncated to 1 characters

old   2: set statement_id = '&1' for
new   2: set statement_id = 'sara' for
old   3:      &2
new   3:      SELECT /*+ use_hash(d,e) */ * FROM d, e WHERE e_did =

Explained.

Elapsed: 00:00:00.31

09:38:23 SQL> _at_view_plan sara

old  15: and statement_id = '&1'
new  15: and statement_id = 'sara'
old  17: and statement_id = '&1'
new  17: and statement_id = 'sara'

Order      Level Execution Plan                           Parallel Op 
                  OPT             BYTES
---------- ----- ----------------------------------------
------------------------------ ---------- ----------
0-0-2      1.0   SELECT STATEMENT     sara Cost = 2                   
                  CHOOSE           9512
1-0-1      2.1     HASH JOIN                                          
                                   9512
2-1-1      3.1       TABLE ACCESS FULL D                              
                                   4100
3-1-2      3.2       TABLE ACCESS FULL E                              
                                   5412

Elapsed: 00:00:00.31

.

I suggest doing some reading on the optimizer and hints as when HINTS are not used efficiently the process can slow down.

tips:
1]
Always make sure the tables you are using are analyzed so that the latest statistics are available to the optimizer. 2]
V$SQL_PLAN:
This view provides a way of examining the execution plan for cursors that were recently executed.

The information in this view is very similar to the output of an EXPLAIN PLAN statement.
However, EXPLAIN PLAN shows a theoretical plan that can be used if this statement were to be executed,
whereas V$SQL_PLAN contains the actual plan used. Received on Wed Nov 06 2002 - 17:35:44 CET