Re: table_space problem
From: Carlos Ciuffoli <ciuffoli_at_lamar.ColoState.EDU>
Date: Thu, 25 Mar 1993 14:59:04 GMT
Message-ID: <Mar25.145904.57901_at_yuma.ACNS.ColoState.EDU>
>having ...
>group by A.c1, A.c2, ...
>
>The outer join controls the way the database generates the answer and if [further constraints] effectively constraints the number of A-rows involved then the temporary spaceusage will be smaller and as a side effect, the query quicker.
>
>Possible problems:
>
>You can only outer-join on one column (between two tables): if A and B are to
>be joined (equal) on two columns this method should be modified (e.g. outer
>join on one and normal join on the other)
>You should remove the surplus null-rows generated which the outer join generate
>in case of no matching rows in the outer joined table. This can normally be
>done by testing a not-null column in the outer joined table.
>
>Another point: instead of
>
>as it would normally be faster
>
>A generel point about outer joins:
>
>I use outer joins a lot, as I have found out that I can control in what order
>ther database makes the joins. Especially when you know what order is the most
>efficient. E.g. the restrictions effectively restricts one table, then use it
>as 'master'-table. Normally there is a naturel way in which the joins should by
>made (the way you would do it, if you should do it by hand).
>
>Always keep in mind what effect indecies (i.e. indexes :-)) would have on your query. Awoid using a indexed table as master-table for a unindexed, but do it the other way around (I asume indexed on the join-column!).
>
>Hope it helps
>--Anders
>-------------------------------------------------------------------------------
>| Anders Harder (harder_at_daimi.aau.dk) I learn mostly from my failures... |
>| Comp. Sci. Dept.| Private address: it isn't that I don't learn from my |
>| Aarhus Univ. | Ydunsvej 12 successes... |
>| DK-8000 Aarhus | DK-8230 Aabyhoj but I have more failures than successes!|
>| Denmark | Denmark (Rune T. Kidde) |
>-------------------------------------------------------------------------------
Date: Thu, 25 Mar 1993 14:59:04 GMT
Message-ID: <Mar25.145904.57901_at_yuma.ACNS.ColoState.EDU>
Thanks to all of you who kindly answered my original post. The problem on the queuery on my opinion is just the way the oracle optimizer goes about doing internal joins and the order in which process constraninst. It seems to me that the optimizer first consideres join clauses no matter in what order you place them and last it looks at your constraints; thus, sorting tons of tuples' attributes you didn't need which you thought you eliminated with your constrainst clause. However, I will like to thanks again, Mr. Anders Harder for his solution.
>A sometimes has the same problem. A workaround might by using outer joins.
>E.g. something like
>
>select A.c1, A.c2, ... count(B.c1), count(C.c1)
>from A, B, C
>where A.c3 = B.c3 (+) and -- The outer join '(+)'
>A.c4 = C.c4 (+) and
>B.some_not_null_column is not null and -- Removes null-rows generated by
>C.some_not_null_column is not null and -- the outer join
>[further constraints...]
>having ...
>group by A.c1, A.c2, ...
>
>The outer join controls the way the database generates the answer and if [further constraints] effectively constraints the number of A-rows involved then the temporary spaceusage will be smaller and as a side effect, the query quicker.
>
>Possible problems:
>
>You can only outer-join on one column (between two tables): if A and B are to
>be joined (equal) on two columns this method should be modified (e.g. outer
>join on one and normal join on the other)
>You should remove the surplus null-rows generated which the outer join generate
>in case of no matching rows in the outer joined table. This can normally be
>done by testing a not-null column in the outer joined table.
>
>Another point: instead of
...
substr(table_3.isis_dept,3,2) in
(select isis_college
from ir.college_table
where hrs_college is not null))
try:
...
exists (select 'x'
from ir.college_table ct
where hrs_college is not null
and ct.isis?college = substr(table_3.isis_dept,3,2)
)
...
>
>as it would normally be faster
>
>A generel point about outer joins:
>
>I use outer joins a lot, as I have found out that I can control in what order
>ther database makes the joins. Especially when you know what order is the most
>efficient. E.g. the restrictions effectively restricts one table, then use it
>as 'master'-table. Normally there is a naturel way in which the joins should by
>made (the way you would do it, if you should do it by hand).
>
>Always keep in mind what effect indecies (i.e. indexes :-)) would have on your query. Awoid using a indexed table as master-table for a unindexed, but do it the other way around (I asume indexed on the join-column!).
>
>Hope it helps
>--Anders
>-------------------------------------------------------------------------------
>| Anders Harder (harder_at_daimi.aau.dk) I learn mostly from my failures... |
>| Comp. Sci. Dept.| Private address: it isn't that I don't learn from my |
>| Aarhus Univ. | Ydunsvej 12 successes... |
>| DK-8000 Aarhus | DK-8230 Aabyhoj but I have more failures than successes!|
>| Denmark | Denmark (Rune T. Kidde) |
>-------------------------------------------------------------------------------
_ _ _ |
/ ) / / ) / / / ... __o | ____ O O
/ __. __ / _ / . / / _ / . .. -\<, | (__^__) O
(__(_/|_/ (_(__(_) (__/|_\_\_// \_//\_(_)_(__/\ ..(_)/(_). | ==(o) O
/ ) / ) On spare time | On vacation !
(_/ (_/ |
-------------------------------------------------------------------------------
Carlo Ciuffoli | Voice 303-491-6064
Computer Programmer | e-mail : ciuffoli_at_vienna.ir.colostate.edu
Budgets & Institutional Analysis | : ciuffoli_at_lamar.ColoState.edu
Colorado State University | Disclaimer: my opinions are my own and not
Fort Collins,CO. 80525. Great U.S.A.| of my employer.
-------------------------------------------------------------------------------
Received on Thu Mar 25 1993 - 15:59:04 CET