Re: UNUSED INDEX on CHAR fields

Thank you Thomas for your explanation before ...

> 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
>

... you may suspect me correctly to be not deeply engaged with SQL, but how then can I persuade the ORACLE-Server to do it vice versa, that means:

full scanning varlist

   for each row we find

      do an index lookup into v_btypen

That would come much closer to what I want it to do! Is that done by defining something like "varlist.v_typ is a foreign key on v_btypen.name" ?

In article <8jd02c$96q$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 29 2000 - 00:00:00 CDT