Re: Store TIME in the Oracle

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 15 Aug 2011 13:36:27 +0100
Message-ID: <CABe10sZmXCSx_+Jqi32KoUtJVHv03jXm9QmJFG6eTDBS6sO=Vw_at_mail.gmail.com>

I think it's only fair to say though that the pc I'm running this on is giving inconsistent and therefore unreliable results. I'll see if I can get some reproducible figures from a decent test rig a bit later.

On Mon, Aug 15, 2011 at 1:20 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> I think Norman's test went something like the below (I get a 13% difference
> on a single run). You are changing the DMY part of the stored data by
> calling 2 functions. As you never retrieve the stored part of the data then
> this is surely wasted work. Why not just store the raw date value and then
> *retrieve* only what you are interested in.
>
> NIALL _at_ NIALL1 >create table t1(
> 2 id number,
> 3 datecol date,
> 4 padding char(500));
>
> Table created.
>
> NIALL _at_ NIALL1 >create table t2(
> 2 id number,
> 3 datecol date,
> 4 padding char(500));
>
> Table created.
>
> NIALL _at_ NIALL1 >set timing on
> NIALL _at_ NIALL1 >begin
> 2 for i in 1..1000000 loop
> 3 insert into t1 (id,datecol,padding)
> 4 values (i,sysdate,'Record '||i);
> 5 end loop;
> 6 commit;
> 7 end;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:03:18.07
> NIALL _at_ NIALL1 >ed
> Wrote file afiedt.buf
>
> 1 begin
> 2 for i in 1..1000000 loop
> 3 insert into t2 (id,datecol,padding)
> 4 values (i,to_date('01-JAN-2000 '||
> to_char(sysdate,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),'Record '||i);
> 5 end loop;
> 6 commit;
> 7* end;
> NIALL _at_ NIALL1 >/
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:03:48.35
> NIALL _at_ NIALL1 >
>
> On Mon, Aug 15, 2011 at 12:44 PM, Eriovaldo Andrietta <
> ecandrietta_at_gmail.com> wrote:
>
>> Morning Norman,
>>
>> The datatype will be DATE., as you said.
>> I have interest only on TIME and not in DATE.
>>
>> I will store it complete as DATE + TIME and retrieve only TIME.
>>
>> You are right, and I think I am doing as you said, right ?
>>
>> Regards
>> Eriovaldo
>>
>> On Mon, Aug 15, 2011 at 4:16 AM, Dunbar, Norman (Capgemini) <
>> norman.dunbar.capgemini_at_environment-agency.gov.uk> wrote:
>>
>>> Morning,
>>>
>>> >> I will follow this way:
>>> >>
>>> >> to_date('01-JAN-2000 '||
>>> >> to_char(sysdate,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
>>> >>
>>> >>
>>> >> I will put the date hard code for all lines inserted and the
>>> >> time that must be saved.
>>>
>>> I'm not convinced that that is a good idea. I have a nagging feeling in
>>> my head that this will confuse the optimiser if it ever needs to take into
>>> account this column in working out a decent query plan.
>>>
>>> Equally, you'll probably never get the benefit of an index on that column
>>> I should imagine - but I suspect you won't be using one anyway.
>>>
>>> Why not just set your column to sysdate?
>>>
>>> You will save on one concatenation, on to_char() and one to_date()
>>> function call for every insert. As a quick and highly unscientific test, it
>>> takes around 23.8% longer to do it your way as compared to sysdate only.
>>> Over three runs of 1,000,000 rows inserted. (Very slow PC running Oracle and
>>> the database on one single disc.)
>>>
>>> The date part is irrelevant as you will be selecting only the time part,
>>> so why not save some CPU etc?
>>>
>>> Just my £0.02.
>>>
>>>
>>> Cheers,
>>> Norm.
>>>
>>> Norman Dunbar
>>> Contract Senior Oracle DBA
>>> Capgemini Database Team (EA)
>>> Internal : 7 28 2051
>>> External : 0113 231 2051
>>>
>>>
>>> Information in this message may be confidential and may be legally
>>> privileged. If you have received this message by mistake, please notify the
>>> sender immediately, delete it and do not copy it to anyone else.
>>>
>>> We have checked this email and its attachments for viruses. But you
>>> should still check any attachment before opening it.
>>> We may have to make this message and any reply to it public if asked to
>>> under the Freedom of Information Act, Data Protection Act or for litigation.
>>> Email messages and attachments sent to or from any Environment Agency
>>> address may also be accessed by someone other than the sender or recipient,
>>> for business purposes.
>>>
>>> If we have sent you information and you wish to use it please read our
>>> terms and conditions which you can get by calling us on 08708 506 506. Find
>>> out more about the Environment Agency at www.environment-agency.gov.uk
>>>
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 15 2011 - 07:36:27 CDT