Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!elroy.jpl.nasa.gov!decwrl!nsc!voder!blia!miket
From: miket@blia.sharebase.com (Mike Tossy)
Newsgroups: comp.databases
Subject: Re: Optimal SQL query needed
Message-ID: <13659@blia.sharebase.com>
Date: 14 Mar 91 17:19:19 GMT
References: <1991Mar13.224746.5293@qualcomm.com>
Organization: ShareBase Corp, Los Gatos, CA
Lines: 69

In article <1991Mar13.224746.5293@qualcomm.com>, greg@harvey.qualcomm.com (greg Noel) writes:
> ...
> I have an Oracle data base table, with three columns, which I will call A, B,
> and C.  There are multiple thousands of value A.  For each value A, there are
> a handful of records, perhaps as many as a dozen.  Within each value A, value
> B is almost always unique (it's really a time value when the record became
> active).  If value B is not unique within A, the tie is broken by value C,
> which is unique within each A/B combination (it's the timestamp of when the
> record was modified).
> 
> Here's the problem:  I want to define a view on this table that only consists
> of the most recent active record for each value of A.  How do I define this
> view such that the lookup is optimal?  Intuitivly, what I would like to tell
> the data base engine is to look up all records with the same value of A, riffle
> through them, and select the one with the largest B.C value.
> 
> What I am working with right now is
> 	select * from TBL T1 where A = 'value' and B =
> 		(select MAX(B) from TBL T2 where T2.B = T1.B)
> which works except for the rare case when there is more than one maximum value
> of B, which I handle programmatically.  This query is also not optimal as it
> requires two indexed lookups within TBL.
> 
> Is there a way to do do the query more optimally or to do the whole query in
> one request, so that the execptions don't need to be handled by the program?
> 
> Tks,

This is a fairly common problem.  I believe it is caused by a database design
problem.  The problem is that B (and C) tells when a particular record became
valid, but the time when the record became invalid is stored in another record
(or implied by the absence of any record with a higher time stamp).  Notice
that your SQL demonstrates this: it joins the table to itself to create the
record you need.  

I suggest that the solution is to add another column B'.  Column B is the
time the record became active and B' is the time the record became inactive
(was replaced by a more current record).  The active row should have a null
value (or a VERY LARGE value) for B'. Depending on your system, this may
elimate the need for C or you may have to fix C in an analogous way.  Your SQL
becomes (probably not exact Oracle syntax):

 	select * from TBL T1 where A = 'value' 
	   and B <= now() and B' = null;
or
 	select * from TBL T1 where A = 'value' 
	   and B <= now() and now() <= B';

This should be a very fast query and it is easy to build the view you want.

This solution is not without cost.  Inserting a new record becomes a longer
process - you must update the old current record also.  But, the update
should be very fast.  The database does becomes larger.  But, before you reject
this solution out of hand because of the "extra disk space" required.  Do
yourself a favor and do a back of the envolope calculation of how much extra
space is required.  Disk space is cheap.  If it really is only "multiple
thousands" of values of A, you will problably never even notice the extra disk
space utilized by this solution.

I suggest that my solution is the correct one from a database design point
of view and you should only use your solution for an overwhelming reason.

--
      >>>>>>  The above statements are only my opinions <<<<<<

Mike Tossy					ShareBase Coropration
miket@sharebase.com				14600 Wichester Blvd
(408) 378-7575 ext2200				Los Gatos, CA 95030
	(ShareBase is a subsidiary of Teradata Corportation)