SQL - slow response

[Avi123india]

Hi,
Need ur help
I m using following query based on 3 table table
mem has 2 million,
act has 17 million and
trx has 27 million records

SELECT
MEM.MEMHIP_NO,
ACT.ACTIVITY_DATE,
TRX.TRANSACTION_DATE,
TRX.trx_fact,
.....
FROM
MEM,
ACT,
TRX
WHERE
( ACT.MEMBER_ACTIVITY_ID(+)=TRX.MEMBER_ACTIVITY_ID )
AND ( MEM.MEMBER_ID=TRX.MEMBER_ID )
AND ( TRX.USER_CREATED = ????)
AND TRX.TRx_DATE BETWEEN '1-Jan-2005' AND '31-DEC-2005'
AND
.... some other conditions....
)

this query runs very very slow ..it takes 4-5 hrs to refresh data. all indexes and joins are proper. i m using normal as well as BITmap indexes....

can anyone pls help me how to tune this query and suggetion to improve the performace. earlier i used hints also but in some case it might helpfull but not always...


your early response would be appriciate.

Regards,
Avi.

[tamilselvan]

AND TRX.TRx_DATE BETWEEN '1-Jan-2005' AND '31-DEC-2005'

Use to_Date fn. Compare always date col with date.

Post tkprof output.

Tamil