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@xxxxxxxxx> 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 @ 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 > -- Niall Litchfield Oracle DBA http://www.orawin.info