Oracle-L: RE: RE: Design question: EFFECTIVE_?

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 24 Apr 2002 03:43:20 -0800
Message-ID: <F001.0044D7B5.20020424034320@fatcity.com>

('binary' encoding is not supported, stored as-is)
My first move was to say that I fully agree with Aleem, but on second thoughts I think that the problem for current and historical values is more or less the same. Basically you can store either start and end dates, which may look a bit redundant since the end date of a row is likely to be, give or take one, the start date for another row refering to the same code, or you can store, as you suggest, a mandatory RATE_EFFECTIVE_DATE, or you can also store a RATE_END_DATE which, as some have suggested, should be a date in a distant future rather than NULL for the current rate. Whether you are interested in the current or an historical value is fairly easy, it requires finding either the maximum RATE_EFFECTIVE_DATE below the date of interest for a given code - or the minimum RATE_END_DATE above the date of interest. Note however that in both cases you will need either a subquery or an inline view, which will probably be less efficient than a BETWEEN involving the date, but not dram! !
atically so assuming the proper indices. Indices the size of which will be reduced, by the way, which is good for fast and even range index scans. The code could look like :

SELECT VALUE
FROM RATE
WHERE RATE_CODE = some_code
AND RATE_EFFECTIVE_DATE =

        (SELECT MAX(RATE_EFFECTIVE_RATE)
         FROM RATE
         WHERE RATE_CODE = some_code
           AND RATE_EFFECTIVE_DATE <= your_date)

You should also try a join with an inline view doing a GROUP BY on RATE_CODE. In some circumstances it could be better.

If you intend to use the value at a large number of places, I suggest you store it to a packaged variable to minimize the number of queries.

>----- Original Message -----
>From: Abdul Aleem <dmit_at_beaconhouse.edu.pk>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Wed, 24 Apr 2002 02:23:25
>
>Chris,
>The only issue I see with the modified table is
>that when you wish to obtain
>history from your child table based on two dates,
>to determine which rate to
>apply you need to base your SQL on two records.
>However if you store
>starting and ending dates it will be easy to
>identify the applicable rate.
>
>HTH!
>Aleem
>
>> -----Original Message-----
>> From: Grabowy, Chris [SMTP:cgrabowy_at_fcg.com]
>> Sent: Monday, April 22, 2002 7:55 PM
>> To: Multiple recipients of list ORACLE-L
>> Subject: Design question: EFFECTIVE_?_DATE
>in a RATE table...
>>
>> A design question, my RATE table looks something
>like this....
>>
>> SQL> desc rate
>> Name
>> Null? Type
>>
>> -------- -----------------
>> RATE_CODE > NOT NULL CHAR(2)
>> RATE_EFFECTIVE_START_DATE > NOT NULL DATE
>> RATE_EFFECTIVE_END_DATE > NOT NULL DATE
>> .
>> .
>>
>> those are the PK fields...and the SQL to query
>the table is easy. I am
>> trying to determine if I can "tighten up" the
>table, like this...
>>
>> SQL> desc rate
>> Name
>> Null? Type
>>
>> -------- -----------------
>> RATE_CODE > NOT NULL CHAR(2)
>> RATE_EFFECTIVE_DATE > NOT NULL DATE
>>
>> I just haven't been able to code the right SQL.
>>
>> Has anyone else taken this approach?
>>
>> Sorry for these design questions, I am just being
>very anal about every
>> table, PK column and index, before we dive into
>development and everything
>> becomes "hard coded".
>>
>> TIA!!!!
>>
>> Chris
>> --
>> Please see the official ORACLE-L FAQ:
>http://www.orafaq.com
>> --
>> Author: Grabowy, Chris
>> INET: cgrabowy_at_fcg.com
>>
>> Fat City Network Services -- (858) 538-5051
>FAX: (858) 538-5051
>> San Diego, California -- Public Internet
>access / Mailing Lists
>>
>> To REMOVE yourself from this mailing list, send
>an E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling
>of 'ListGuru') and in
>> the message BODY, include a line containing:
>UNSUB ORACLE-L
>> (or the name of mailing list you want to be
>removed from). You may
>> also send the HELP command for other information
>(like subscribing).
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.com
>--
>Author: Koivu, Lisa
>INET: lisa.koivu_at_efairfield.com
>
>Fat City Network Services -- (858) 538-5051
>FAX: (858) 538-5051
>San Diego, California -- Public Internet
>access / Mailing Lists
>To REMOVE yourself from this mailing list, send an
>E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling
>of 'ListGuru') and in
> the message BODY, include a line
>containing: UNSUB ORACLE-L (or the
>name of mailing list you want to be removed from).
>You may also send the
>HELP command for other information (like
>subscribing).
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.com
>--
>Author: Abdul Aleem
> INET: dmit_at_beaconhouse.edu.pk
>
>Fat City Network Services -- (858) 538-5051
>FAX: (858) 538-5051
>San Diego, California -- Public Internet
>access / Mailing Lists
>To REMOVE yourself from this mailing list, send an
>E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of
>'ListGuru') and in
>the message BODY, include a line containing: UNSUB
>ORACLE-L
>(or the name of mailing list you want to be removed
>from). You may
>also send the HELP command for other information
>(like subscribing).
>---------------------------------------------------
>---------------------
>---------------------------------------------------
>---------------------
>---------------------------------------------------
>-----------------
>---------------------------------------------------
>-----------------
>---------------------------------------------------
>-----------------

Stephane Faroult
Oriole Corporation
Performance Tools & Free Scripts

http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Apr 24 2002 - 06:43:20 CDT

RE: RE: Design question: EFFECTIVE_?_DATE in a RATE table...