Re: table_space problem (a solution?)

ciuffoli_at_lamar.ColoState.EDU (Carlos Ciuffoli) writes:

>Hello netters;
 

> I need help to overcome a space problem error I have been getting no matter
>how I structure the query.
 

> [...]
 

>select substr(table_3.isis_dept,3,2) isis_college,
> table_3.isis_dept,
> table_3.course,
> count(distinct (table_1.pid)) enrolled,
> count(distinct (sis_demand.pid))turnaway ,
> 'ch' ch
>from ir.table_1,
> ir.table_3,
> ir.sis_demand
>where( table_3.sectid= table_1.sectid and
> table_3.course = ir.sis_demand.course
> and not exists ( select a.pid
> from ir.sis_demand a
> where a.pid = table_1.pid and
> a.course = table_3.course and
> a.term||'' ='SP93')) and
> (table_1.term||'' ='SP93' and
> table_3.term||'' ='SP93' and
> table_3.ri_census||'' ='Y' and
> sis_demand.term||'' = 'SP93' and
> substr(table_3.isis_dept,3,2) in
> (select isis_college
> from ir.college_table
> where hrs_college is not null))
>having count(distinct (table_1.pid)) <
> count(distinct (sis_demand.pid))
>group by substr(table_3.isis_dept,3,2),
> table_3.isis_dept,
> table_3.course
 

> [...long signature...]

A sometimes has the same problem. A workaround might by using outer joins. E.g. something like

(I haven't been able to make out exactly what you query do, or what the tables contain, so this an abbrivated answer. I think you should replace 'A' with table_3, 'B' with table_1 and 'C' with sis_demand.)

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...]

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)                         |
-------------------------------------------------------------------------------