OracleRdb and Oracle 7 SQL differences

From: John Tait <johnt_at_ihug.co.nz>
Date: 1998/09/08
Message-ID: <35F4F49A.8EDADA08@ihug.co.nz>#1/1

I am currently implementing a set of views which had been developed for OracleRdb onto an Oracle 7 database, but have a problem with a syntax which is allowed under OracleRdb but not Oracle 7 SQL.

Under OracleRdb, the views make a lot of use of the following (simplified) syntax

select job.job, period.period,

    (sum(cost.movement)

     from costs cost
     where cost.job = job.job
     and cost.period >=1
     and cost.period <= period.period)

from
jobs job,
periods period;

This type of select expression is not supported in Oracle 7 and I have recoded it as

select job.job, period.period, sum(cost.movement) from
jobs job,
periods period
costs cost
where cost.job (+) = job.job
and cost.period (+) >=1
and cost.period (+) <= period.period)
group by job.job, period.period;

However for even a moderate sized database (3000 jobs, 30000 costs) the performance is quite slow using this construct.

As a newcomer to Oracle SQL I would be grateful if someone can point me in the direction of a more efficient way to code such a selection.

tia

JohnT Received on Tue Sep 08 1998 - 00:00:00 CDT