Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Date Time PS/SQL
swaxolez wrote:
> I'm sure this is a very obvious solution but I'm having trouble
> organizing this problem.
>
> I have a database with date/time field.
>
> Each record looks like this:
>
> Scanned_DateTime,Passkey,Last_Scanned_DateTime,TimeDifference
>
> When I import these records using sql*loader I assume I can set up a
> trigger and procedure to go through the whole database (to compare
> against previous records already stored there)
> and compute the two fields listed above "Last_Scanned_DateTime" and
> "TimeDifference" as they apply to each unique passkey. So that each
> record will contain the time the passkey was intially scanned and the
> last time and time difference of the previous scan.
>
> Is there a faster way then doing a sequential scan from beginning to
> end of the entire database to achieve this? I expect this database to
> contain millions of records. I'm just beginning Oracle and am not
> quite sure on the PS/SQL coding.
>
> Any help or pointers would be appreciated.
>
The TimeDifference is a computed value. Computed values are not typically stored in a table as you will compute them on the fly when querying the data. If TimeDifference = Last_Scanned_DateTime - Scanned_DateTime then do not store this value as you can compute this value when querying as follows:
SELECT Scanned_DateTime,Passkey,Last_Scanned_DateTime,
Last_Scanned_DateTime - Scanned_DateTime AS TimeDifference FROM my_table;
So you do not need a trigger to populate this column.
HTH,
Brian
-- =================================================================== Brian Peasland dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - UnknownReceived on Tue Jul 25 2006 - 12:42:33 CDT
![]() |
![]() |