| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: query optimization for many to many join
On May 31, 11:52 pm, romeo.olym..._at_gmail.com wrote:
> On Jun 1, 8:05 am, grasp06110 <grasp06..._at_yahoo.com> wrote:
>
>
>
> > Hi Everybody,
>
> > Does anyone have an suggestions on how to optimize a join on data from
> > both sides of a many to many relationship? It sees that I can get
> > very quick queries for anything on either side of the many to many but
> > when a try to join accross the many to many relationship there is no
> > way to do it quickly.
>
> > For example, if I have an address table and a person table and a
> > person can have multiple addresses and an address can be occupied by
> > multiple people so I join person and address in a table person_address
> > then...
>
> > If I try to get all of the person data I can join to the
> > person_address table using an index on person_id and address_id. But
> > then if I try to join in address information I am using the person_id
> > index which is useless for the join on address.
>
> > The same holds trure swapping person and address in the above
> > paragraph.
>
> > Any suggestions would be greatly appreciated.
>
> > Thanks,
> > John
>
> I'm confused by what you're trying to do. Can you show us what kind of
> results you're looking for.
>
> And oh, just to confirm, you now have 3 tables:
>
> person (person_id, <other person data>)
> address (address_id, <other address data>)
> person_address (person_id, address_id)
>
> Correct?
>
> Cheers,
>
> Romeo
Hi Everybody,
Turns out the many-to-many thing had nothing to do with the problem.
I put in something like the following. The use_nl(table_name) hints gave me nested loops and a query time of less than one second.
select
/*
use_nl(per) use_nl(add) use_nl(pa)
 |
 |