| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance Problems
<mjmather_at_gmail.com> wrote in message
news:1179308104.448880.191170_at_h2g2000hsg.googlegroups.com...
> 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
>
You need to be more precise about your requirement before we can suggest a reasonable answer to this question.
Do you want to join all the data from one table to the other table; or do you typically pick a very few rows from one table to join to the other; if it's just a few (or just one) which table is it that drives the join.
If your queries are low volume, then your only concern is whether you can do a nested loop join between the two tables - and the initial access path to the driving table becomes the important issue.
If your queries are high volume, then you need to be able to do a
hash join (or possibly merge join) between the two tables, and again
it's not the join condition that is the problem, it's the other filters
that
you may have to minimise the driving table data.
For nested loops: assume you want to drive off the package table:
select
Pck.padding,
aud.padding
from
package pck,
audit_trail aud
where
aud.proc = to_number(substr(pck.caseref,1,instr(pck.caseref,'-')-1)) and aud.num = to_number(substr(pck.caseref,instr(pck.caseref,'-')+1))
Possible execution plan (I've trimmed the right hand edge to try and keep this readable for as many people as possible).
| Id | Operation | Name | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | AUDIT_TRAIL | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | PACKAGE | | 4 | BITMAP CONVERSION TO ROWIDS | | | 5 | BITMAP AND | | | 6 | BITMAP CONVERSION FROM ROWIDS| | |* 7 | INDEX RANGE SCAN | AUD_NUM | | 8 | BITMAP CONVERSION FROM ROWIDS| | |* 9 | INDEX RANGE SCAN | AUD_PROC | ---------------------------------------------------------
Another possible execution plan:
select
Pck.padding,
aud.padding
from
audit_trail aud,
package pck
where
pck.caseref =
to_char(aud.proc,'fm99999') || '-' ||
to_char(aud.num,'fm99999')
| Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS FULL | AUDIT_TRAIL | | 3 | TABLE ACCESS BY INDEX ROWID| PACKAGE | |* 4 | INDEX UNIQUE SCAN | PACK_PK | ----------------------------------------------------
The problem becomes awkward only if (a) you want to cover every possible query requirement with a single generic approach and (b) you want every possible query to return the result in the shortest time possible given the volume of data requested.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Sun May 20 2007 - 07:25:02 CDT
 |
 |