Example of index selection

This example is based on the Baan 5.0b data model.

SQL query:

SELECT     tfacr200.ttyp, tfacr200.ninv 
FROM       tfacr200
WHERE      tfacr200.ninv = :inv 
AND        tfacr200.tdoc = :doc
AND        ( tfacr200.itbp BETWEEN :it1 AND :it2
             OR tfacr200.itbp = :it3 ) 
AND        tfacr200.line = :lin 
AND        tfacr200.ttyp = :ttyp

Ignore unusable parts:

WHERE     tfacr200.ninv = :inv 
AND       tfacr200.tdoc = :doc
AND       ( tfacr200.itbp BETWEEN :it1 AND :it2
             OR tfacr200.itbp = :it3 )
AND       tfacr200.line = :lin 
AND       tfacr200.ttyp = :ttyp

Determine fields that are considered for the hint generation:

WHERE     tfacr200.ninv = :inv
AND       tfacr200.tdoc = :doc
AND       tfacr200.line = :lin
AND       tfacr200.ttyp = :ttyp

Determine the matching indexes:

Index 1:  ttyp  ninv  line  tdoc  docn  lino
Index 2:  itbp  ttyp  ninv  line  tdoc  docn  lino
Index 3:  year  btno  ttyp  ninv  line  tdoc  docn  lino
Index 4:  itbp  docd  ttyp  ninv  line  tdoc  docn  lino
Index 5:  itbp  ttyp  ninv  line  docd  tdoc  docn  lino
Index 6:  year  btno  tdoc  docn  lino  ttyp  ninv  line

An index hint in index 1 is generated because all other indexes do not match; this is because the initial index fields are not used in the WHERE clause.