Performance Problems

Hi and thanks to anyone who answers.

I have two tables that I need to join. Package and Audit_Trail.

Package has ~300,000 rows and Audit_Trail > 8 million.

In Package there is a field CaseRef. It is a VarChar2 and has the form xx-xxx ie 34-145 or 67-12345 etc etc.

In Audit_Trail there are two fields. Proc and Num. You guessed it - they are both Numbers and form the two parts of the CaseRef in Package.

I need to join them in the fastest way possible as I am dealing with rather large tables. Both fields in Audit_Trail are indexed (not unique). CaseRef in Package is primary key. I can change / alter Package table but I have select only permission on Audit_Trail as it's in the schema of a 3rd party product.

The best I can come up with is splitting the CaseRef in Package (as it's the smaller table) and then joining on two fields rather than concatenating the Proc and Num in Audit_Trail and joining on one field.

The only better solution I have is using a Materialized View on Audit_Trail providing the PRE concatenated CaseRef along with Proc and Num fields and unique indexing on all three.

Anyone have suggestions, comments or considerations?

Thanks in advance Received on Wed May 16 2007 - 04:50:58 CDT