Re: Store TIME in the Oracle

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 @ NIALL1 >create table t1(
  2  id number,
  3  datecol date,
  4  padding char(500));

Table created.

NIALL @ NIALL1 >create table t2(
  2  id number,
  3  datecol date,
  4  padding char(500));

Table created.

NIALL @ NIALL1 >set timing on
NIALL @ 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 @ 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 @ NIALL1 >/

PL/SQL procedure successfully completed.

Elapsed: 00:03:48.35
NIALL @ NIALL1 >

On Mon, Aug 15, 2011 at 12:44 PM, Eriovaldo Andrietta <ecandrietta@xxxxxxxxx
> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> 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

Other related posts: