If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > new local index in v9.7

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 09-17-10, 09:17
dr_te_z dr_te_z is offline
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 555
new local index in v9.7

I am preparing myself for v9.7, especially local indexes for my table-partitioned tables. I understand that an index is only "local" when the column on which the partitioning was based, is included in the index itself.
Well, I am using a TIMESTAMP column so I wonder if I can use a generated column (based upon the values of the same TIMESTAMP) and still have a local index.

Does anyone know, or in the position to test it for me?

(on my express-C I can try & test anything, except table partitioning)
Reply With Quote
  #2 (permalink)  
Old 09-17-10, 15:40
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
I don't think your premise is correct. All indexes will be local if all unique indexes contain the Table Partitioning column (unless you specify global).

They cannot be columns generated from the Table Partitioning column, but not sure why you need that.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old 09-19-10, 11:33
dr_te_z dr_te_z is offline
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 555
Quote:
Originally Posted by Marcus_A View Post
but not sure why you need that.
It is a timestamp column and the table has 50 mil. rows. I do not need each and every index to have that many entries. So I'd better use the generated column as my partioning key in this case?
Reply With Quote
  #4 (permalink)  
Old 09-19-10, 14:41
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
Quote:
Originally Posted by dr_te_z View Post
It is a timestamp column and the table has 50 mil. rows. I do not need each and every index to have that many entries. So I'd better use the generated column as my partioning key in this case?
Timestamp is 10 bytes and INT int is 4 bytes, so you would save some space by including a generated INT column as part of the Primary Key (and any other unique indexes, if applicable).
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #5 (permalink)  
Old 09-20-10, 01:59
dr_te_z dr_te_z is offline
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 555
Quote:
Originally Posted by Marcus_A View Post
you would save some space by including a generated INT column as part of the Primary Key
My concern is not the space but the index-cardinality. All indexes would be unique when I use the timestamp column. I have not figured out yet if that is a bad thing (or not ). Insert performance is the key-issue here.
Reply With Quote
  #6 (permalink)  
Old 09-20-10, 07:57
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
You don't need all indexes to be unique, nor do you need all indexes to have the partitioning column in order to have all indexes as partitioned. The only requirement for partitioned indexes is that the unique indexes have the partitioning column.

Obviously, the PK indexes will be unique and there is nothing you can do about that. But it is not bad for insert performance that non-unique indexes are also unique, but that is not needed because of what I explained above.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On