Hi,
I 've a requirement to generate monthly report based on data from
2 tables.Howver the size of the tables is very large .

However there is partition defined for each month on rec_dt in both tables.
The question is it advisable to join tables or is there any alternative.
can I improve performance by breaking the query into parts.
Pl suggest some better approach in sql or pl/sql .


The query is below :

select from mst_fact a,pty_fact b
where a.ref_id like 'TA%300'
and a.rec_dt between to_date('01-aug-09','dd-mon-yy') and to_date('31-aug-09','dd-mon-yy')
and a.ref_id = b.ref_id
and a.file_id = b.file_id
and a.rec_dt = b.rec_dt

Indexes on MST_FACT :
1.Pk of mst_fact :
REF_ID,FILE_ID,REC_DT

2.XIE2MST_FACT on REC_DT

Pk of pty_fact :
REF_ID,FILE_ID,REC_DT,PTY_ROLE


Explain plan :

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 6 K 1752
FILTER
PARTITION RANGE ITERATOR KEY KEY
HASH JOIN 6 K 2 M 1752
INDEX FAST FULL SCAN XIE5_PTY_FACT 602 K 38 M 5 KEY KEY
TABLE ACCESS BY LOCAL INDEX ROWID MST_FACT 229 K 60 M 11 KEY KEY
INDEX RANGE SCAN XIE2MST_FACT 229 K 2 KEY KEY