Re: is index better option here

"peter" <hhh.database_at_gmail.com> wrote in message news:1165997665.711174.268170_at_l12g2000cwl.googlegroups.com...
> 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 |
> ----------------------------------------------------------------------------
>

Impossible to say.

Is this a query with two scalar subqueries in the select list ? In which case the statement cost will be the cost of the driving query, and misleading.

Is the optimizer's estimate of one row for the driving query accurate - in which case it probably doesn't really matter which path you take, either could be efficient.

It would have been sensible to show us the filter/access predicates as well - and if you enable CPU costing the optimizer might be able to make a better estimate of whether or not to use the indexes.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html