Example of weight calculation

While the query in this example can look unwise, the query shows the weight calculation and the influence of the involved resources.

SQL query:

select* from dbtst180
where   projno > ' '
and     emstdate > 0
and     empno > 0
and     emendate = 0
and     emendate > 0

Indexes:

Index 1: empno projno stdate endate
Index 2: endate projno stdate empno

The column weight depends on the type of the expression. For equal expressions ( <col> = <…> ) the value of the resource hint_idx_weight_equal is taken. For range expressions (like >=) the value of the resource hint_idx_weight_range is taken:

hint_idx_weight_equal  = 1
hint_idx_weight_range  = 1
hint_idx_weight_factor = 1

_index1: empno(1.000) projno(1.000) stdate(1.000) endate(2.000)
   weight 5.000

_index2: endate(2.000) projno(1.000) stdate(1.000) empno(1.000)
   weight 5.000

An index hint for index 1 is generated. Note that index 2 is probably better for this query:

hint_idx_weight_equal  = 5
hint_idx_weight_range  = 1
hint_idx_weight_factor = 1

_index1: empno(1.000) projno(1.000) stdate(1.000) endate(2.000)
   weight 5.000

_index2: endate(6.000) projno(1.000) stdate(1.000) empno(1.000)
   weight 9.000

Note: The weight for column endate is reduced from 6 (weight_range + weight_equal) to 2 (weight_range + weight_range) because previous columns in the index definition only had matches on range expressions. In that case, the weight_equal is reduced to weight_range.

An index hint for index 2 is generated.

hint_idx_weight_equal  = 1
hint_idx_weight_range  = 1
hint_idx_weight_factor = 0.5

_index1: empno(1.000) projno(0.500) stdate(0.250) endate(0.250)
   weight 2.000

_index2: endate(2.000) projno(0.500) stdate(0.250) empno(0.125)
   weight 2.875

An index hint for index 2 is generated.

hint_idx_weight_equal  = 1
hint_idx_weight_range  = 0
hint_idx_weight_factor = 1

_index1: empno(0.000) [stopped]
   weight 0.000

_index2: endate(1.000) projno(0.000) [stopped]
   weight 1.000

An index hint for index 2 is generated.

Note: If hint_idx_weight_equal and hint_idx_weight_range are set to 0, no index hint based on the WHERE clause is generated.