OraFAQ Forum: Performance Tuning » Query on the performance of JOIN on indexed columns

Home » RDBMS Server » Performance Tuning » Query on the performance of JOIN on indexed columns (Oracle 10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query on the performance of JOIN on indexed columns [message #548590]

Fri, 23 March 2012 05:44

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello

Suppose
I have 2 tables with huge data and composite unique indexes on the joining columns

create unique index a_i on a(c1,c2);
create unique index b_i on b(c1,c2);

Now in the query I am joining all indexed columns of both tables

select * from a,b
where a.c1 = b.c1
and a.c2 = b.c2
and a.c3 = <some value> --- this has poor selectivity

Sometime I have seen both tables were accessed using index access

Such queries executed better only when I tried /*+ full(a) parallel(a) */ on the larger table and let other table use index access
and this was Nested loop
This worked sometimes

I am not sure if I should use HASH by doing b also as parallel (using hint)

The question is how do I weigh FTS w.r.t Index access in similar situations where joining columns have unique index but since the filtering condition is poor lot of data needs to be handled?

Unfortunately tkprof is not available in this situation
and I assume referring COST in the plan displayed in GUI tools won't be a good starting point

Thanks and Regards
OraPratap
P.S. this being a general query I am unable to attach a plan etc.

[Updated on: Fri, 23 March 2012 05:47]

Report message to a moderator

Re: Query on the performance of JOIN on indexed columns [message #551047 is a reply to message #548590]

Sun, 15 April 2012 01:44

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

For the query you gave:

select * from a,b
where a.c1 = b.c1
and a.c2 = b.c2
and a.c3 = <some value> --- this has poor selectivity

Your plan should be

HASH JOIN
   FULL TABLE SCAN (A) (filter rows)
   FULL TABLE SCAN (B)

You should check to see if your HASH JOIN is done as OPTIMAL or ONE-PASS and not MULTI-PASS. If MULTI-PASS then increase memory available for HASH JOIN till you get at least ONE-PASS.

However, I suspect your query is different from the sample form you provided as I don't see Oracle doing anything with indexes on these two tables unless the expected overlap of rows between the two tables is very low (in which case you might see index join followed by table lookup(s) on new versions of Oracle).

Good luck, Kevin

Report message to a moderator

Re: Query on the performance of JOIN on indexed columns [message #551402 is a reply to message #551047]

Wed, 18 April 2012 00:13

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello Kevin

Many Thanks for your reply and the tip provided

By referring "One pass" and "Multiple passes", I assume you mean workarea executions as we query in v$sysstat.

Is it possible to get these details without actually running the query?

Thanks and Regards
Orapratap

Report message to a moderator

Re: Query on the performance of JOIN on indexed columns [message #551420 is a reply to message #551402]

Wed, 18 April 2012 02:46

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I do not know. I suggest you start another post asking that specific question.

Kevin

Report message to a moderator

Re: Query on the performance of JOIN on indexed columns [message #552447 is a reply to message #551420]

Wed, 25 April 2012 22:15

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

i think that index don't use becasue you select * from a and b

hash tow table the index not filter any data.

and a.c3 = filter some data.

select /*+leading(a,b),us_hash(a,b)*/*
from a,b
where a.c1 = b.c1
and a.c2 = b.c2
and a.c3 = <some value>

Report message to a moderator

Previous Topic:	How to Tune Long running queries?
Next Topic:	Troublesome trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 12:35:56 CDT 2024