Re: Index Usage ?!

Hi!

> the env. is 9.2.0.3/win2k-sp3.
>
> it is built on "profile" table .
> the index has the columns "entpc,sex,flag,pref,entday" in it.
> the order of the columns in the index is also the same as i have
mentioned.
>
> 1.so ,does it mean that "idx_profile_shinki" is a bad index.
> 2.if it is bad , why does cbo select this index ?

did you analyze your table in addition to index as well? first time you were probably using RBO, which always counts index access better than table access.
but after analyzing your segments CBO thinks that full table scan is better because of optimizer_index_cost_adj and optimizer_index_caching parameters. what are your values of those parameters? (also db_file_multiblock_read_count).

> 3.if it is good , why does elapsed time increase after analyzing this
index ?
> 4.the leading column (entpc) of the index is not there in the beginning of
"where" clause.
> then how is the index used ? it was not said so in a perf tuning book by
"richard j. niemiec"

doesn't matter, as long as your column is somewhere in the where clause. (note than in 9i indexes can sometimes be used even when first column(s) of index aren't in where clauses - it's called index skip scanning).

If you already have analyzed your indexes and tables, have set the parameters, then run alter session set events '10053 trace name context forever, level 1';
then run your statements and send me the trace file from udump.

Tanel.

>
>
> It's eating my brains.Kindly explain me Gurus.
>
> Regards,
> Jp.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Prem Khanna J
> INET: jprem_at_kssnet.co.jp
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Thu Jul 24 2003 - 05:21:05 CDT