Re: Index Design

Jennifer wrote:
> What is a good documentation resource for index designing? We're
> running oracle 10g, and I have a number of queries that need to run
> similar to the below example:
>
> FROM "BILL_FCT" AB
> JOIN DATE_DIM DD
> ON AB.DATE_SKEY = DD.DATE_SKEY
> JOIN RE_DIM RD
> ON AB.RE_SKEY = RD.RE_SKEY
> join FP_DIM FP
> ON AB.FP_SKEY = FP.FP_SKEY
> WHERE RD.PH_SKEY = 27
> AND SUBSTR(FINANCIAL_PLAN_CODE, 1,2) NOT IN ('MB','XX')
>
> I can create a column that can bypass the substr if needed.
>
> Thanks!
>
> Jennifer

You could also create a function-based index on SUBSTR(FINANCIAL_PLAN_CODE, 1,2), set query_rewrite_enable to TRUE and compute statistics. Also, it appears DATE_SKEY, RE_SKEY and FP_SKEY should be indexed individually. Your best tactic is to create indexes and test, test, test. You might be able to create a concatenated index on the three columns and possibly index skip scanning will make the index usable by all of the join conditions. Again, thorough testing is a must. Create the index or indexes, trace sessions using the queries and examine the tkprof output from those traces. This is really the only way you'll know what is working and what is not.

David Fitzjarrell Received on Fri Sep 09 2005 - 14:06:07 CDT