Re: UNUSED INDEX on CHAR fields

In article <8jcika$vir$1_at_nnrp1.deja.com>,   sklusmann_at_my-deja.com wrote:
> I am currently trying to tune my queries and I saw
> with help from "EXPLAIN PLAN" that sometimes (not always)
> indexes on char fields are not used, e.g.:
>
> SELECT MAX(a.v_adr + a.v_size * b.pc_size) max_size
> FROM varlist a, v_btypen b
> WHERE a.v_typ = b.name;
>
> OPERATION OPTIONS
> OBJECT_NAME OBJECT_TYPE
> ------------------------------ ------------------------------
> SORT
> AGGREGATE
>
> NESTED
> LOOPS
>
> TABLE ACCESS FULL
> V_BTYPEN
> TABLE ACCESS BY INDEX ROWID
> VARLIST
> INDEX RANGE SCAN
> BAUTEIL_V_TYP NON-UNIQUE
> SELECT
> STATEMENT
>
> 6 Zeilen ausgewählt
>
> TABLE ACCESS FULL for V_BTYPEN is strange, because the field "name" of
> this table (char(16)) is indexed even unique and the compared field
> v_typ is of the same type and size.
>

given the query:
 SELECT MAX(a.v_adr + a.v_size * b.pc_size) max_size  FROM varlist a, v_btypen b
 WHERE a.v_typ = b.name;

the table access full must be expected on either varlist of v_btypen. You have NO predicates, only a single join. The plan is showing that we are :

   full scanning v_btypen

     for each row we find
         do an index lookup into varlist

It is using the only index that makes sense right now. If you had a predicate such as:

 SELECT MAX(a.v_adr + a.v_size * b.pc_size) max_size  FROM varlist a, v_btypen b
 WHERE a.v_typ = b.name
   AND b.name = 'FOO';

I would expect it to use an INDEX on v_btypen to find the row(s) and then an indexed read into varlist.

As it is -- every single row in v_btypen must be processed -- why use an index? an index is used when we need to find a few of the rows...

> Thanks for any explanation in advance
>
> Stefan
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.