0

I have table called 

CREATE TABLE process (
    batch_id      Integer
    ,product_id   Integer
    ,machine_id   Integer
    ,created_date DATE
    ,updated_date DATE
    ,primary key(batch_id,product_id,machine_id)
 )

But I generally use SQL like

SELECT *
FROM   process 
WHERE  product_id  = 123
AND    machine_id = 1

When i check SQL plan for this id does not uses primary key index.

Do i need to create another index of both columns?

Database is DB2

Ganesh Jadhav
  • 616
  • 2
  • 7
  • 21
  • 1
    While a second index is possible, does the selectivity of the PK columns justify the existing order of the composite PK columns? The highest selectivity columns should appear first in the list usually. – mao Mar 24 '20 at 14:14
  • yes, sequence of create table and index column sequence and select where clause in same sequence of index. – Ganesh Jadhav Mar 24 '20 at 14:22
  • 1
    if your PK has columns A,B,C, and your where clause only has columns B,C then Db2 will not use the PK index in this case. But if the PK column order is B,C,A then Where-clause predicates B,C may use the PK index if the costs justify it. – mao Mar 24 '20 at 14:26
  • 1
    Another thing to consider for this kind of table and query is to try get an INDEX-only query. Consider an index with include columns – data_henrik Mar 24 '20 at 14:27

0 Answers0