Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2

    Unanswered: Indexing strategies

    I am confused by indexing strategies advocated by one of the database products (not DB2 LUW). Please see following SQL and index advised by the product for the SQL. I see no advantage in new index. Size (disk space) of new index is double the size of existing index (primary key) and probably it will use more system resources (memory and disk space). Does anyone see any benefits from the new index based on the SQL in question?

    Tables:
    Invoice ( 30 columns , primary key invoice_id, several million records)
    customer ( 20 columns , primary key cust_code, half million records)
    Both invoice_id and cust_code are varchar(10) fields

    select *
    from invoice i, customer c
    where i.invoice_id = '123'
    and i.cust_code = c.cust_code

    index advised : Table: invoice Columns : invoice_id, cust_code

    Thanks...

    Satya...
    Last edited by stiruvee; 10-08-15 at 22:07. Reason: added more details about tables

  2. #2
    Join Date
    Aug 2008
    Posts
    147
    The usefulness of the Non Clustered Index suggestion will depend on the extra work required to also return the extra columns via the "select *". If you were to apply this non - clustered index , you might find it solves a key lookup problem for the clustered index , but creates a new problem because the NC index is not covering all the SELECT * columns.
    What columns are being returned in the SELECT *. ?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  3. #3
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    select * returns all columns from invoice table (about 30) and customer table (about 20). The new index does not make SQL to run using index-only access on invoice table. It still needs to access data page after index lookup even with new index.

    I understand the advantage of using index which makes SQL to run using index only access without accessing data page. It (index-only access) is not the case for the sql used in example.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You should use a test environment and check the access plan (execution plan or however it is called in MSSQL) before and after creating the index and you will see the diferences.
    My guess:
    - the first column of the index is used to filter the data ( i.invoice_id = '123')
    - the second column is used to make the join (maybe sort merge)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    The database in question is "DB2 for i" (AS400 database) .

    << the second column is used to make the join (maybe sort merge)>>

    Invoice_id is primary key. There will be only one record for given value of invoice_id. Sort is not needed as only one record will be read from invoice table.

    << You should use a test environment and check the access plan >>

    * The access plan remains same even after new index is created on invoice table. SQL does not use new index.

    * I created two test tables same as invoice table, one having unique index invoice_id and second table having new index suggested (invoice_id , cust_code). Tested the SQL against both tables and estimated run time from explain plan for both tables is same. Run time is also same for both tables. I ran SQL against both tables 5 times and compared the total run time.

    * There is no improvement for select SQL from new index. It only increases run time for insert, update and deletes. I see no improvement due to new index under any situation. However, the database product team advocates the suggested indexing strategy and their product makes these type of index suggestions a lot.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by stiruvee View Post
    There is no improvement for select SQL from new index. It only increases run time for insert, update and deletes. I see no improvement due to new index under any situation. However, the database product team advocates the suggested indexing strategy and their product makes these type of index suggestions a lot.
    Then you should present them your conclusions.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    <<Then you should present them your conclusions.>>

    I did post the same question on IBM forum. The response from "DB2 for i" team is "question was asked due to lack of understanding how optimizer works". When posted more details with several examples, there is no response.

Posting Permissions

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