Joining lots of tables in a many to many - firstrows enough!

[Custom24]

Hi

I have a query I would like to build where I want to find related information in a distantly related table.
For example each Library has many Synthesis_Plates, which in turn have many Synthesis_Compounds, which in turn have many Analytical Submissions. My question is how to find the Library for a given Analytical Submission.

I know I can do it just by joining them all up on the primary keys and specifying a select distinct, but this seems inelegant and wasteful. Is there a way to specify that for a join, Oracle should stop looking when it finds the first match.

I also know I could do this using rownum with maybe a first rows hint, but again it seems to me such a common thing that there might be an easier way.

Thanks if you can help, or even if you can’t (for reading this!)
Mark

 

[carp]

If there are going to be multiple identical rows and you don't want to use distinct, then I would recommend adding

AND rownum < 2

in your WHERE clause.

 

[srishan]

Mark,

Good concern. Short answer is NO, there is no simple way.
In RDBMS, SQL is designed to get all rows that matched the join condition(equi join) and all rows matched from one table and simply all the rows from the second table with out worrying about a math(outer join). But there are no other ways of restricting the search to the FIRST row unless you give an additional CONDITION in the WHERE clause. You already must be knowing all this.

Coming to the rest of your posting,

FIRST_ROWS hint would not restrict the SQL to the first row on joining condition. It just hints the optimizer (CBO only) to get the first rows FASTER.

On ROWNUM concept, it is doable. I would be interested to see how you would go about it? Let us see the sample solution here if you do have some time.

Cheers,
SriDHAR

 

[sem]

Mark: take a glance at analytic functions:

http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/functions2.htm#1064294

SriDHAR: there are a number of common ways to look for necessary data in joined tables. One of them is NESTED LOOP, when the first table is looked for the first matching row, then second, according to the join condition, and so on. Thus you get the result set row by row and stopping it with ROWNUM condition does the thing without any overhead. In this case FASTER may be treated as AS FAST AS IT CAN BE DONE.

Regards, Dima

 

[DaPi]

I'm having trouble seeing what the problem is with this . . .

Library to Synthesis_Plate,
Synthesis_Plate to Synthesis_Compound,
Synthesis_Compound to Analytical_Submission
- are all 1 to many?

Using an ordinary INNER JOIN, starting from ONE Analytical_Submission, you find one Synthesis_Compound; then one Synthesis_Plate, then one Library. It can't get better than that!
DaPi - no silver bullet

 

[Custom24]

Thanks guys
I've got it to work sufficiently quickly for the moment by putting an index on one of the joining tables.
Sem - I looked at that page, but I need a little more help.
I think you mean I should use the FIRST_VALUE function?
But this seems to require an order by, which I think would slow it up.
Anyway, I'll have more of a think. Thanks Mark

 

[srishan]

DaPi,

It is a One to Many relation on each join, I THINK.
Now Mark wants to stop the FETCH when the first row fetched for each join to improve the performance. This would CHANGE the purpose of the business meaning behind the SQL, unless the requirement does not CARE about which CHILD row the SQL brings during all of SQL's joining hits, which is kind of strange. Going forward, this CAN be only DONE by using ROWNUM.

Sem,

That is what I said in my posting. You are again saying it can be accomplished by using ROWNUM, which is confusing. Are we on the same page?

Thx,
SriDHAR

 

[carp]

SELECT
L.c1,L.c2, L.c3
sp.c1, sp.c2
sc.c1, sc.c2, sc.c3,
as.c1, as.c2
FROM
Library L,
Synthesis_Plates sp,
Synthesis_Compounds sc,
Analytical_Submissions as
WHERE L.c1 = sp.c1
AND sp.c2 = sc.c2
AND sc.c2 = as.c1
AND L.c1 = 'Some Value'
AND ROWNUM < 1;

This will bring back the first and only the first row found that satisfies all conditions.

Is this what you're looking for or is it more complicated?

 

[DaPi]

Custom24 said &quot;for a given Analytical Submission&quot;
so it's
. . . and AS.column = 'Some Value' . . .

Which if the 1-to-many are as I stated, only needs to fetch one row per table and will only give one row as output. (We are &quot;reading&quot; the tree in the many-to-1 direction - or am I completely hay-wire?). DaPi - no silver bullet

 

[DaPi]

Custom24 said &quot;for a given Analytical Submission&quot;
so it's
. . . and AS.column = 'Some Value' . . .

Which if the 1-to-many are as I stated, only needs to fetch one row per table and will only give one row as output. (We are &quot;reading&quot; the tree in the many-to-1 direction - or am I completely hay-wire?). DaPi - no silver bullet

 

[DaPi]

(Sorry about the duplicate - had a mini-crash).

I said: &quot;only needs to fetch one row per table&quot; - of course this assumes all the right indexes exist and the tables have been analyzed etc. DaPi - no silver bullet

 

[DaPi]

Custom24,
. . . just read the title again &quot;many to many&quot; ! ! ! ! Do you really mean this?
Please let us know if I interpreted the cardinality of the relationships correctly. DaPi - no silver bullet

 

[Custom24]

Hello again.
In my attempt to simplify the question, I left out an important detail. The relationship is many to many. My post should have read

For example each Library has many Synthesis_Plates, which in turn have many Synthesis_Compounds, which in turn have many entries in a bridge table, Anal_Plates_Compounds, with each record in the APC table having an id related to a record in the final table, Analytical_Submissions.

So an analytical submission contains many compounds, but a compound can be submitted more than once, which is why I described it as being many to many.

Sorry for confusing everyone and thanks for all your help

Mark

 

[DaPi]

Ahhhhhhh! DaPi - no silver bullet