MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Update on a Indexed Timestamp Field

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Thu Sep 24, 2015 7:16 pm    Post subject: Update on a Indexed Timestamp Field Reply with quote

We have a table with some columns and Last modified timestamp. There will be updates to around 1-2% of the rows in this table everyday. So to extract all rows which were updated for a particular day, one suggestion is having an index on Last Modified Timestamp, so that it helps the extraction. But there is a concern that it will impact the updates happening to the table.

Is having an index on timestamp a overhead during updates? Since having an index on last modified timestamp will have a high cardinality how does DB2 handle an update? Should we increase the percentage free space on a page to help this? How different is having and index on a timestamp column different from having index on any other normal columns?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Sep 25, 2015 10:14 am    Post subject: Reply with quote

ed.sam13,

I am guessing that you are going to add it as part of the composite index rather an index by itself. Timestamp fields usually have a high cardinality of having different values as the you can have micro seconds in it. so depending on the predicate on your sql statement you may still be doing a table scan instead of an index scan

Assuming you did indeed create an index and if your intention is to bring in the day records then if your WHERE clause is as follows

Code:

DATE(timestamp_col) BETWEEN '2015-09-24' AND '2015-09-25'


You WILL end up with table scan instead of index scan as you are using the Date function on a timestamp column.

However if you have something like this

Code:

timestamp_col BETWEEN TIMESTAMP('2015-09-24','00:00:00') AND TIMESTAMP('2015-09-25','00:00:00')


Then you will get an index scan.

So It all depends on what kind of predicates you plan to have on this column.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Sep 25, 2015 11:52 am    Post subject: Reply with quote

hi Kolusu,
Thanks for your reply. Actually I wanted to understand the impact of an update statement when we have a last modified timestamp as index. This will not be a primary index. This will be a non-composite, non-clustered index.

So the situation is that the table will have very minimal inserts everyday. There will be lot of updates. So everytime a row is updated, the last modified timestamp will be updated to the current timestamp. So when this update statement is executed and the last modified timestamp is changed, how much of an impact will it have on the index. From theory on Control Intervals and Control Areas for VSAM files i understand that when this update for a particular row occurs, the control area of the current row gets aligned and a new entry is made at the end of the index. Is this understanding right or when I update the timestamp the index gets broken and would need a reorg to fetch that row effectively?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Sep 25, 2015 11:59 am    Post subject: Reply with quote

ed.sam13 wrote:
hi Kolusu,
Thanks for your reply. Actually I wanted to understand the impact of an update statement when we have a last modified timestamp as index. This will not be a primary index. This will be a non-composite, non-clustered index.

So the situation is that the table will have very minimal inserts everyday. There will be lot of updates. So everytime a row is updated, the last modified timestamp will be updated to the current timestamp. So when this update statement is executed and the last modified timestamp is changed, how much of an impact will it have on the index. From theory on Control Intervals and Control Areas for VSAM files i understand that when this update for a particular row occurs, the control area of the current row gets aligned and a new entry is made at the end of the index. Is this understanding right or when I update the timestamp the index gets broken and would need a reorg to fetch that row effectively?


Is the update performed where condition on the primary key? or just the timestamp field? As I mentioned earlier, Timestamp fields have high cardinality and you should have an index on it if ONLY you are going to use the full key in your predicates.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Sep 25, 2015 12:52 pm    Post subject: Reply with quote

hi Kolusu, Yes the update statement is using the primary keys of the table on the predicate. Last Modified timestamp is one of the columns which get updated on the SET statement along with few other columns.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Sep 25, 2015 1:08 pm    Post subject: Reply with quote

ed.sam13 wrote:
hi Kolusu, Yes the update statement is using the primary keys of the table on the predicate. Last Modified timestamp is one of the columns which get updated on the SET statement along with few other columns.


So as long as your Queries are using the full key on the where clauses you should be ok with having the index. As I showed in earlier examples the index will not be used if the predicate is not using the full key.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group