Re: Help needed with adding index

On May 17, 12:55 pm, "aravind.ka..._at_gmail.com" <aravind.ka..._at_gmail.com> wrote:
> I have this sql:
>
> select D.* from tcustomer_history a, tdate, tcustomer b, taccount c,
> TPOLICY_SUMMARY D where A.month_sk = date_sk
> and year_month_ct = 200704 and a.CUSTOMER_SK = b.CUSTOMER_SK and
> b.ACCOUNT_SK = c.ACCOUNT_SK
> and c.SPECIAL_HANDLING = 'VIP' AND A.MONTH_SK = D.MONTH_SK AND
> A.POLICY_SK = D.POLICY_SK
> AND SNAPSHOT_TYPE_IN = 0
>
> It takes about 26 seconds to retrieve the results and I am hoping to
> improve the performance.
>
> This is the explain plan I got:
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 19 K 15653
> HASH JOIN 19 K 2 M 15653
> TABLE ACCESS FULL MISP.TACCOUNT 15 K 140 K 55
> HASH JOIN 107 K 10 M 15533
> TABLE ACCESS FULL MISP.TCUSTOMER 113 K 1 M 235
> HASH JOIN 107 K 9 M 15088
> HASH JOIN 1 M 36 M 782
> TABLE ACCESS BY INDEX ROWID MISP.TDATE 30 270 2
> INDEX RANGE SCAN MISP.IDX_TDATE_YEAR_MONTH_CT 1 1
> INDEX FAST FULL SCAN MISP.SYS_C003642 4 M 56 M 766
> TABLE ACCESS FULL MISP.TPOLICY_SUMMARY 5 M 350 M 6887
>
> I have an index on TPOLICY_SUMMARY table on MONTH_SK, POLICY_SK and
> SNAPSHOT_TYPE_IN columns. I use that to join this table in this query.
> The query optimizer still invokes a a full table scan. Why should it
> go for a FTS in this case?
>
> Are there any other tricks to make this query faster?

>From your description it sounds like you have two indexes on
TPOLICY_SUMMARY.
1) MONTH_SK, POLICY_SK
2) SNAPSHOT_TYPE (assuming that column is from the TPOLICY_SUMMARY table, but not sure since it is not qualified.)

The first index may or may not be used. There are no filter predicates on this table in the query directly, so any use of the index will depend on the computed cardinality of the table it is joined to, which is tcustomer_history.
Based on the execution plan, it looks like the TDATE table is hit first by columns (date_sk, year_month_ct) expecting 30 rows. It joins that to tcustomer_history by a system named index SYS_C003642, expecting a join cardinality of 1M. Based on that 1M the cbo determined a FTS of the 5M in TPOLICY_SUMMARY was the better choice over using the index.

That's what I see based on the information. Probably need more information to determine completely.
db version, values of optimizer_index_cost_adj, table and column statistics.
It may be a matter of statistics or it may just be that the cbo is doing what it has to do to get the request. Does reality match with what the cbo expects? Is it really returning 19K rows or does it return 5? If the stats do not match reality then look at the stats.

Andrew Received on Thu May 17 2007 - 13:59:55 CDT