More efficient SQL-code ?????
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