2

I am confused by indexing strategies advocated by one of the database products. Database is "DB2 for i" (iSeries/AS400).

Please see following SQL and index advised by the product for the SQL. There is no improvement in run time after creating new index. Run time for both cases (without new index and with new index) is 10 ms. Size (disk space) of new index is double the size of existing index (primary key) and probably it will use more system resources (memory). 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)

FK for cust_code is defined on invoice table.

Query:

select i.col1, i.col2, ..., i.col20,
c.col1, c.col2,....,c.col10
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
1

Yes there is a need for it, but you can help without creating the index at least in SQL Server

You are joining on invoice..cust_code which is not indexed so that the Database engine will have to do a hash lookup on the table finding all invoices with the correct customer code and then filtering on the invoice id.

By creating and enforcing a foreign key the join will be faster, as the optimizer can safely assume that invoice_id '123' exists for this single customer, but having the suggested index can help the optimizer to find the optimal query plan, a singleton lookup to both the tables, faster.

  • Why would the engine have to do a hash lookup? The condition is where i.invoice_id = '123' and invoice_id is the PK. – ypercubeᵀᴹ Oct 9 '15 at 10:09
  • @ypercube I assume this is SQL Server, where the logical processing order of the sql statement (msdn.microsoft.com/en-us/library/ms189499.aspx) processes JOIN before WHERE so the optimzer will have to do more work to come up with a good enough plan. Which would lead to the index suggestion above. – Spörri Oct 9 '15 at 10:25
  • 1
    The logical order of processing though does not forbid the optimizer from using a plan with different order. – ypercubeᵀᴹ Oct 9 '15 at 10:34
  • yes, absolutely correct, but the question is why is this index suggested and would it help. The reason for for the index suggestion is the join clause, the optimizer will most likely use the primary keys and singleton lookups because of the WHERE clause. With out a foreign key though the optimizer has no way of knowing if the particular order is issued to a valid customer. (social.msdn.microsoft.com/forums/sqlserver/en-us/…) – Spörri Oct 9 '15 at 10:39
  • * Database in question is "DB2 for i" (AS400). – satya Oct 9 '15 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.