Re: is index better option here

On 13.12.2006 09:14, peter wrote:
> before index output of explain is
>
> SQL> @?\rdbms\admin\utlxplp.sql
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------
>
> --------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 40 | 5 |
> |* 1 | TABLE ACCESS FULL | N | 1 | 40 | 5 |
> |* 2 | TABLE ACCESS FULL | N | 1 | 15 | 5 |
> |* 3 | TABLE ACCESS FULL | N | 1 | 15 | 5 |
> --------------------------------------------------------------------
> ********************************************************************
>
> after index output of explain is
>
> SQL> @?\rdbms\admin\utlxplp.sql
>
> PLAN_TABLE_OUTPUT
> ----------------------------------------------------------------------------
>
> ----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost |
> ----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 40 |
> 5 |
> |* 1 | TABLE ACCESS FULL | N | 1 | 40 |
> 5 |
> |* 2 | TABLE ACCESS BY INDEX ROWID| N | 1 | 15 |
> 27 |
> |* 3 | INDEX RANGE SCAN | IND_N_CC | 144 | |
> 1 |
> |* 4 | TABLE ACCESS BY INDEX ROWID| N | 1 | 15 |
> 27 |
> |* 5 | INDEX RANGE SCAN | IND_N_CC | 144 | |
> 1 |
> ----------------------------------------------------------------------------
>

Difficult to tell without more detail (DDL, Ora version etc.). The remaining table scan can be caused by any of these - and maybe more:

• no statistics
• statistics out of date
• index missing
• small table

Btw, you usually get more information from the plan by using package DBMS_XPLAN.         robert Received on Wed Dec 13 2006 - 04:20:56 CST