Help needed with adding index

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? Received on Thu May 17 2007 - 12:55:25 CDT