More efficient SQL-code ?????

From: Paul Sjoerdsma <pgs1002_at_esc.cam.ac.uk>
Date: 16 May 1994 13:04:42 GMT
Message-ID: <PGS1002.94May16140442_at_delphi.esc.cam.ac.uk>


I have a piece of SQL-code that, hopefully, could be coded more efficiently. The thing is I'm not that familiar with SQL, so I hope somebody out there could give me some hints.

I'm using Oracle 7.0.13

My relevant tables look like (where .. is non-relevant information):

table core



unique_id integer not nul primary key
..
..

table contents



unique_id not null primary key
..
..

table plane



unique_id integer not null primary key
core      integer not null references core(unique_id)
depth     number not null

..

unique (depth, core) using index
..

table observation



unique_id integer not null
plane integer not null references plane(unique_id) contents integer not null references contents(unique_id) data number not null
unique (plane, contents) using index

The general idea is that every core has a certain amount of planes. A plane is just a sample taken from a core at a certain depth. Different observations can be made on each plane, so each observation of type 'contents' belongs to a 'plane'. Also different observations can be made on the same 'plane'.

What I need to find for a certain 'contents' and 'core' combination:

The minimum and maximum plane.depth together with their associated observation.data so that I end up with two tuples:

min(plane.depth), observation.data
max(plane.depth), observation.data

My solution till now has been:

    select
      P.depth, O.data
    from delphi.plane P, delphi.observation O     where (O.plane = P.unique_id and

           O.contents = $contents_id
           and P.depth in 
           (select min(Pl.depth) 
           from delphi.plane Pl, delphi.observation Ol
           where (Ol.plane = Pl.unique_id and
                  Ol.contents = $contents_id and
                  Pl.core = $core_id)))

where $core_id and $contents_id are the unique_id of core and contents resp. This piece of SQL-code will result in only the min-tuple, the same goes for the max-tuple. Their will only be 1 return value in the whole database.

Any improvements are welcome,

Thanks in advance
Paul Received on Mon May 16 1994 - 15:04:42 CEST

Original text of this message