November 24, 2003
Always Learning
Here is a really useful feature that I didn't know about until today - function based indexes in Oracle. Cool.
How we could use them in DB2 because it suffers from the same problem that Oracle used to. Namely that applying a function to a column means that it can't be accessed via an index - with a requisite increase in the speed of queries.
I'm getting a little grief from our client at the moment because all of our date information is stored in TIMESTAMP columns. They don't like the fact that when they access data by day they have to use a function ('DATE') to split out the date part. It doesn't matter that they can use 'BETWEEN' to achieve the same thing and that it will use an index. They are insisting I replace all of the TIMESTAMP columns in our database with seperate DATE and TIME ones. I suspect that this is because its common practise on their mainframe DB2 systems, but I may be wrong.
[Courtesy of Alan Green in turn courtesy of Simon Brunning]
Posted by Andy Todd at November 24, 2003 01:36 PM
If they split the columns into DATE and TIME, wouldn't it be even more complex while updating? For example, if someone had to write queries to update these columns with value + 5 hours, it becomes kind of messy.
Posted by: Babu on December 16, 2003 01:32 PMNormally I'd say yes. But in this application most of the TIMESTAMP columns are write once, because they are the start and end times of various tasks.
Posted by: Andy Todd on December 17, 2003 01:11 PM