| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: UNUSED INDEX on CHAR fields
In article <8jf7es$vh0$1_at_nnrp1.deja.com>,
sklusmann_at_my-deja.com wrote:
> 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" ?
>
if you are using RBO (if not, use the /*+ RULE */ hint to do so), simply reverse the tables in the from clause:
ops$tkyte_at_8i> set autotrace on explain
ops$tkyte_at_8i> SELECT MAX(a.v_adr + a.v_size * b.pc_size) max_size
2 FROM varlist a, v_btypen b 3 WHERE a.v_typ = b.name;
MAX_SIZE
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'V_BTYPEN' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'VARLIST' 5 4 INDEX (RANGE SCAN) OF 'VARLIST_IDX' (NON-UNIQUE)
ops$tkyte_at_8i>
ops$tkyte_at_8i> SELECT MAX(a.v_adr + a.v_size * b.pc_size) max_size
2 FROM v_btypen b, varlist a 3 WHERE a.v_typ = b.name;
MAX_SIZE
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'VARLIST' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'V_BTYPEN' 5 4 INDEX (RANGE SCAN) OF 'V_BTYPEN_IDX' (NON-UNIQUE)
> 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.
>
-- 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.Received on Thu Jun 29 2000 - 00:00:00 CDT
 |
 |