Hi,
Can any one guide me on how to tune this query for
optimal performance.The query and my explain plan are
as stated below.Is there any other alternative for me
to tune this query to get better performance
benefits.Say lower the cost.And faster time.Your suggestions are
greatly appreciated.

1. Original Query with cus_account, cus_pers,
cus_card_iss
============================================================================
=====================
SELECT ccmr.CARD_NAME, ccis.CUS_ID,
ccac.TIER_STATUS_IND, ccac.ACCT_STATUS_IND,
NVL(to_char(ccis.CARD_EXP_DT,'dd/mm/yyyy hh:mm:ss'),
'NIL') as EXP_DATE
from cus_account ccac, cus_card_iss ccis, cus_pers
ccmr
where ccac.PRG_CD='KF' and
ccac.ACCT_STATUS_IND in ('A','D','E') and
ccac.TIER_STATUS_IND in ('Q','T','L') and
ccac.IGNORE_FLG='N' and
(ccac.CARD_VALID_TILL_DT is null or
ccac.CARD_VALID_TILL_DT >=SYSDATE) and
ccac.int_id=ccmr.int_id and
(ccac.int_id=ccis.int_id and
ccis.IGNORE_FLG='N' and
(ccis.CARD_IND!='S' or ccis.CARD_IND is null) and
(ccis.CARD_VALID_TILL_DT is null or
ccis.CARD_VALID_TILL_DT >=SYSDATE)
)
>> 36436 records
*** Explain Plan***
SELECT STATEMENT Optimizer=CHOOSE (Cost=13006
Card=202238 Bytes=13954422)
HASH JOIN (Cost=13006 Card=202238 Bytes=13954422)
HASH JOIN (Cost=5853 Card=130656 Bytes=5487552)
INLIST ITERATOR
TABLE ACCESS (BY GLOBAL INDEX ROWID) OF
CUS_ACCOUNT (Cost=64 Card=130656 Bytes=2482464)
INDEX (RANGE SCAN) OF IX_CUS_ACCOUNT_08
(NON-UNIQUE) (Cost=3 Card=145675)
PARTITION HASH (ALL)
TABLE ACCESS (FULL) OF CUS_PERS (Cost=3615
Card=1796560 Bytes=41320880)
PARTITION HASH (ALL)
TABLE ACCESS (FULL) OF CUS_CARD_ISS (Cost=3784
Card=1495095 Bytes=40367565)


Thanks,
Rama