Home » RDBMS Server » Performance Tuning » Query on the performance of JOIN on indexed columns (Oracle 10.2.0.4)
Query on the performance of JOIN on indexed columns [message #548590] Fri, 23 March 2012 05:44 Go to next message
orapratap
Messages: 113
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 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
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
Re: Query on the performance of JOIN on indexed columns [message #551402 is a reply to message #551047] Wed, 18 April 2012 00:13 Go to previous messageGo to next message
orapratap
Messages: 113
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
Re: Query on the performance of JOIN on indexed columns [message #551420 is a reply to message #551402] Wed, 18 April 2012 02:46 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
Registered: December 1999
Location: Connecticut USA
Senior Member
I do not know. I suggest you start another post asking that specific question.

Kevin
Re: Query on the performance of JOIN on indexed columns [message #552447 is a reply to message #551420] Wed, 25 April 2012 22:15 Go to previous message
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>
Previous Topic: How to Tune Long running queries?
Next Topic: Troublesome trigger
Goto Forum:
  


Current Time: Thu Sep 18 21:49:11 CDT 2014

Total time taken to generate the page: 0.14093 seconds