Re: Help needed with adding index

On May 17, 1: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?

How did you generate this explain plan? Is it generated from the row source lines in a 10046 trace, (assuming Oracle 10g) the plan that appears in a 10053 trace, DBMS_XPLAN (what options specified), EXPLAIN PLAN FOR..., or some other method? If the plan is not from a 10046 trace file or DBMS_XPLAN called with ALLSTATS LAST, compare the plan that you posted with the execution plan indicated in a 10046 trace file or the execution plan from DBMS_XPLAN called with ALLSTATS LAST. How close is the predicted cardinality with the actual cardinality? With that information, you may find that a hint is appropriate to correct the execution plan, or you may find that the the performance problem is caused by resource limitations/problems in the database instance (insufficient HASH_AREA_SIZE causing temp tablespace usage, for instance).

A 10053 trace will indicate why Oracle determined not to use the index on the MONTH_SK, POLICY_SK and SNAPSHOT_TYPE_IN columns.

Side note: it is easier for most people to read the SQL statement and plan if you use meaningful aliases for table names, rather than A, B, C, and D.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu May 17 2007 - 15:16:58 CDT