Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: 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)

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •