This forum is currently locked. You can't register or post questions at this time. (read more)

functions on date columns 11gR2

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

kfoster
Member
Posts: 17
Joined: Wed Feb 05, 2014 10:04 pm

functions on date columns 11gR2

Postby kfoster » Mon Mar 03, 2014 6:45 pm

I'm relatively new to 11gR2 and we recently added some date columns to existing tables. The developers are writing code that will be using these new columns and I'm faced with trying to make them perform. My issue is some of the code looks like the following:

to_char(c.entry_date,'yyyy-mm-dd hh24') = to_char(sysdate - 1/24,'yyyy-mm-dd hh24')

Initially I would say no problem I'll put a function based index on the column but it is not that easy. At this stage the developers tell me some SQL may not have a function on the column at all or different to_char formats. The table is already over indexed in my opinion so adding a function based index for every eventuality makes me cringe.

I'm starting to investigated to see if anything offered by 11gR2 helps with a situations like this or am I basically stuck.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: functions on date columns 11gR2

Postby Tim... » Tue Mar 04, 2014 6:54 am

Hi.

Well, this is not a good way to do the match really. They want to compare the dates to a level of precision of hours, so they should use truncate, like this.

Code: Select all

TRUNCATE(c.entry_date,'hh24') = truncate(sysdate- 1/24,'hh24')


There is no point converting it to a string. It's just wastes effort. You can read about date function here:

http://www.oracle-base.com/articles/mis ... ervals.php

To make it more efficient, you can either create a function based index on TRUNCATE(entry_date), or write this comparison in longhand.

Code: Select all

c.entry_date BETWEEN truncate(sysdate- 1/24,'hh24') and truncate(sysdate,'hh24')- (1/24/60)


So that is between one hour ago and the current minus 1 second. Adjust as required. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

kfoster
Member
Posts: 17
Joined: Wed Feb 05, 2014 10:04 pm

Re: functions on date columns 11gR2

Postby kfoster » Tue Mar 04, 2014 4:11 pm

That will help a lot Tim. Thanks

As a matter of fact you made me realize I have some notes from Hotsos Symposium to review on this as well.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: functions on date columns 11gR2

Postby Tim... » Tue Mar 04, 2014 5:36 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron