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?
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.
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
Table: invoice Columns : invoice_id, cust_code