query with joins 2

[TheDash]

SELECT UNIQUE SQ.SQ1, SQ.SQ2, SQ.SQ3, LC.LC1, SQ.SQ4, SQ.SQ5, SS.SS1, SS.SS2, SS.SS3, SS.SS4, CL.CL1, CL.CL2, CL..CL3, SQ.SQ5, SQ.SQ6, SQ.SQ7, SQ.SQ8, SA.SA1, SQ.SQ9, SQ.SQ10, SQ.SQ11, SQ.SQ12, SQ.SQ13, SQ.SQ14, SQ.SQ15, SQ.SQ16, SQ.SQ17, SQ.SQ18, SQ.SQ19, SQ.SQ20, SQ.SQ21, SQ.SQ22, SQ.SQ23, SQ.SQ24, SQ.SQ25, SQ.SQ26, SQ.SQ27, SQ.SQ28, SQ.SQ29, SQ.SQ30, SQ.SQ31, SQ.SQ32, SQ.SQ33, SQ.SQ34, SQ.SQ35, SQ.SQ36, SQ.SQ37, SQ.SQ38, SQ.SQ39, SQ.SQ40, SQ.SQ41
FROM
SQTABLE SQ, LCTABLE LC, SQTABLE SA, SSTABLE SS, CLTABLE CL
WHERE SQ.COL1 IN ('1000', '2000')
AND SQ.KEY1=LC.KEY1
AND SQ.KEY2=CL.KEY2
AND SQ.KEY3=SS.KEY3
AND SQ.KEY4=SA.KEY4
AND LC.KEY5=?


SQTABLE, SATABLE, SSTABLE, LCTABLE and CLTABLE are the tables

Questions:

Can we mix coloumns as in query: SQ.SQ3, LC.LC1, SQ.SQ4, SQ.SQ5, SS.SS1,
or should they be ordered? i.e all cols from one table first, second table so on ...

Does UNIQUE SQ.SQ1 have any effect on the execution?

Is this query optimized? Any way to improve performance?

Please suggest

 

[SantaMufasa]

Dash,

Q1. "Can we mix coloumns...?"
A1. Absolutely. You can put the columns in any order you wish.

Q2. "...should they be ordered?"
A2. Not in the least bit necessary; does not affect performance,

Q3. "Does UNIQUE SQ.SQ1 have any effect on the execution?"
A3. Probably improves performance.

Q4. "Is this query optimized?"
A4. Your instance is probably using Oracle's Cost-Based Optimizer (CBO). The CBO relies heavily upon current and accurate statistics for each table. If your statistics are grossly stale, then your query is probably inefficient.

Q5. "Any way to improve performance?"
A5. a) Ensure that your statistics are current. b) display your execution plan and if it is executing poorly, you can embed "hints" in your code to override Oracle's CBO execution plan. (If you don't know what I'm talking about, then you can read up on, or take a class in, "Tuning Oracle Queries".

Now, more important than good performance is good results. With as many tables as you are joining, there may be the risk that one or more of the child tables has no row to match one of the parent table (SQTABLE) rows. In that case, the parent row that is missing even one match in any one of the other child tables (LCTABLE, SQTABLE, SSTABLE, or CLTABLE) fails to display. If that is what you want, okay. But if that is not what you want, then you also need to consider use of "outer join" notation "(+)". If you need help on this notion, then please post another request of your need.

Let us know if all this resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:49 (29Sep04) UTC (aka "GMT" and "Zulu"), 19:49 (28Sep04) Mountain Time)

 

[jimbopalmer]

1) the order of columns has no effect on execution time

2) Yes it is going to force a sort to be sure they are distinct

3) indexing on the keys in the subsidiary tables may well help

no Idea what the LC.key5 = ? part is about

I tried to remain child-like, all I acheived was childish.

 

[TheDash]

Great reply Santa, thanks. I will let you know.

 

[SantaMufasa]

Dash,

I have one adjustment to my comments: When you said, "Does UNIQUE SQ.SQ1 have any effect on the execution," I probably misinterpreted what you meant. I thought you meant "If there is a UNIQUE constraint on SQ.SQ1 is there any performance effect?" In such case, there would be an index on SQ.SQ1, which generally improves performance (depending upon the ratio of result rows to total table population). If, instead, you meant "If I place the modifier UNIQUE in front of the expression SQ.SQ1 is there a performance effect," then yes, there is a performance hit in that it sorts all rows and eliminates duplicates. And perhaps most importantly to your query, using the word UNIQUE applies to ALL expressions in your query, not just SQ.SQ1. If there is a possiblity that the concatenated contents of your 51 output expressions results in duplicates and your need prohibits duplicates, then you probably need the UNIQUE. But the chances of that happening are probably close to nil, therefore the UNIQUE could be causing a horrendous amount of wasted/unnecessary cycles.

Another note: if the above is really your code, then syntactically, you need to eliminate the extraneous period in your expression, "CL..CL3".

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:08 (29Sep04) UTC (aka "GMT" and "Zulu"), 20:08 (28Sep04) Mountain Time)

 

[TheDash]

Hi

thanks for the reply. The joins are with pks and fks only. All pks have indexes. I am not sure if fks have indexes? If they have then I think all indexes are good, otherwise, could you please advise what to do?

I am aware of the outer join notation. i think the outer joins if required should be

AND SQ.KEY1=LC.KEY1(+)
AND SQ.KEY2=CL.KEY2(+)
AND SQ.KEY3=SS.KEY3(+)
AND SQ.KEY4=SA.KEY4(+)

AND LC.KEY5=? is the bind variable usage.

 

[SantaMufasa]

Dash,

Yes, your outer-join notation is what I would have used. To confirm your FK indexes, the fastest method probably is simply attempt to create indexes in each FK column. If the column is already indexed, Oracle gives you and error (no problem) and if they and not already indexed, then you've taken care of business.

If you have additional questions, feel free to post, but if you don't hear from me immediately, I'm just off to the airport to pick up a colleague from the UK, but I shall respond as soon as I return.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:17 (29Sep04) UTC (aka "GMT" and "Zulu"), 20:17 (28Sep04) Mountain Time)

 

[jimbopalmer]

Is LC.KEY5 also in SQTABLE as a Foreign Key?

if there is a SQ.KEY5 the query may be faster and the indexes may be concatenated

I tried to remain child-like, all I acheived was childish.

 

[TheDash]

Hi

Yes, it is a foreign key

LC.KEY5 = SQ.KEY1 = LC.KEY1

Could you please explain it further?

 

[jimbopalmer]

FROM
SQTABLE SQ, LCTABLE LC, SQTABLE SA, SSTABLE SS, CLTABLE CL
WHERE SQ.COL1 IN ('1000', '2000')
AND SQ.KEY1=LC.KEY1
AND SQ.KEY2=CL.KEY2
AND SQ.KEY3=SS.KEY3
AND SQ.KEY4=SA.KEY4
AND LC.KEY5=?

This WANTS to use SQTABLE as the driving table, all other tables link to it, but our bind variable is not ON our ideal driving table.

FROM
SQTABLE SQ, LCTABLE LC, SQTABLE SA, SSTABLE SS, CLTABLE CL
WHERE SQ.COL1 IN ('1000', '2000')
AND SQ.KEY1=LC.KEY1
AND SQ.KEY2=CL.KEY2
AND SQ.KEY3=SS.KEY3
AND SQ.KEY4=SA.KEY4
AND SQ.KEY1=?

allows SQTABLE to be the driving table much easier as all conditions are on that table, also:

1)If you do need outer joins, LC.KEY5 = ? can be awkward 'backwards' though an outer join.

2) having all conditions on SQTABLE will allow a concatinated index on SQ.KEY1, SQ.COL1 rather than 2 simple indexes


(I may have misunderstood, but are two columns identical in the LCTABLE table? If KEY1 is always equal KEY5, why have two columns?)

I tried to remain child-like, all I acheived was childish.

 

[TheDash]

Hi,

1) LC.KEY5 = SQ.KEY1 = LC.KEY1
It should've been LC.KEY1=? actually
and that is the primarykey. Its foreignkey is SQ.KEY1 SQ.COL5 actually)

LC.KEY5 = ? can be awkward 'backwards' though an outer join.

If outer joins are used what needs to be done?

2) having all conditions on SQTABLE allow a concatinated index on SQ.KEY1, SQ.COL1 rather than 2 simple indexes

could you explain a little bit what the concatenated index here does?


Thanks a lot.

 

[jimbopalmer]

WHERE SQ.COL1 IN ('1000', '2000')

this is likely to be a very poor canidate for an index by itself, it translates to
WHERE (SQ.COL1 = '1000' OR SQ.COL1 = '2000')
and ORs do not index as well as ANDs

If we use SQ.KEY1 as our bind variable, then we have both our conditions on the same table, so we have a shot at a more selective index SQ.KEY1, SQ.COL1 that centers in on rows with our bind variable AND SQ.COL1 IN ('1000', '2000')


if we have rows like
KEY1 COL1
10 1000
10 2000
10 3000
10 4000
20 1000
20 2000
20 3000
20 4000

Now if we use WHERE SQ.COL1 IN ('1000', '2000') AND LC.KEY5=? One index can only get ALL the rows that match the bind variable, and AFTER we join all those to all the other tables we can use and index to only get 1000 or 2000.

But if we use WHERE SQ.COL1 IN ('1000', '2000') AND SQ.KEY1=? we can use ONE index to get only those rows which equal the bind variable AND are '1000' or '2000' THEN join to the other tables.

That would be MUCH less data to shuffle.





I tried to remain child-like, all I acheived was childish.