handling large table joins for report
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 :
2.XIE2MST_FACT on REC_DT
Pk of pty_fact :
Explain plan :
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 6 K 1752
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
Posting suggests tables are partitioned.
Posting also suggest best approach is join statement.
If my understanding of the scenario is correct I would add parallel hint for the table with highest cardinality.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width