Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    10

    Unanswered: What columns to Add in Index....

    Hi,

    I wanted to create index on a table. But that table is used by many applications and they use different where clause to select from the table.SO how many index can I create. And the table on which I want to create Index has 20 triggers applied on it for insert, update and delete operations. These triggers call stored procedures which inturn update, delete and insert into 10 tables.So if a insert or update happens on the table the corresponding stored procures fire and populate the data in othere tables. THis activity takes around 30 seconds. How can I reduce the time?

    Also if a where clause in Select operation has fields like Name, Surname, Employee id and Place. And a 'index 1' is created for the columns Name, Surname, Employee id and Place. Now if the other select query has columns Name, Surname, Employee id. So do we need to create one more index withy columns Name, Surname, Employee id Or will thjis second select query use the 'Index 1'.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will probably want to submit you workload to the Design Advisor to get recommendations for new indexes.

    Andy

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    You dont mention platform nor version. In LUW V10 theres an improved index scan which can reduce the need for additional indexes.

    Other than that, have a look ar db2advis

  4. #4
    Join Date
    Feb 2013
    Posts
    10
    We r using version 9.6.

    If not the complete design, I wanted to know about:-

    If a where clause in Select operation has fields like Name, Surname, Employee id and Place. And a 'index 1' is created for the columns Name, Surname, Employee id and Place. Now if the other select query has columns Name, Surname, Employee id. So do we need to create one more index withy columns Name, Surname, Employee id Or will thjis second select query use the 'Index 1'.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    First off, there is no Version 9.6.

    Second, your second index would be redundant as it is a subset of the first one.

    Andy

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by prat31 View Post
    We r using version 9.6.

    If not the complete design, I wanted to know about:-

    If a where clause in Select operation has fields like Name, Surname, Employee id and Place. And a 'index 1' is created for the columns Name, Surname, Employee id and Place. Now if the other select query has columns Name, Surname, Employee id. So do we need to create one more index withy columns Name, Surname, Employee id Or will thjis second select query use the 'Index 1'.
    Assuming you have an index like:

    create index x1 on T (Name, Surname, Employee_id, Place)

    this will be fine for both your queries:

    Q1: ... where (Name, Surname, Employee_id, Place) = (?, ?, ?, ?)
    Q2: ... where (Employee_id, Name, Surname) = (?, ?, ?)

    However, a query like:

    Q3: ... where (Surname, Employee_id, Place) = (?, ?, ?)

    Can't efficiently use the index x1 since Name is missing in the query. In V10 there is an improved index scan that can use the index more efficiently than what is possible in earlier versions.
    --
    Lennart

Posting Permissions

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