Skip to Content
0
Former Member
Jan 19, 2011 at 02:15 PM

Estimated cost of 8 with entire key

42 Views

I execute a simple SQL request on table BUT000 :

SELECT
  "PARTNER" , "NAME_LAST" , "NAME_FIRST"
FROM
  "BUT000"
WHERE
  "CLIENT" = :A0 AND "PARTNER" IN ( :A1 , :A2 , :A3 , :A4 , :A5 )#

CLIENT and PARTNER are key fields of BUT000, and there's no other field in the key.

So I expected an estimated cost of 1. But the EXPLAIN gives 8 in ST04 :

SELECT STATEMENT ( Estimated Costs = 8 , Estimated #Rows = 0 )

       3 INLIST ITERATOR

           2 TABLE ACCESS BY INDEX ROWID BUT000
             ( Estim. Costs = 8 , Estim. #Rows = 3 )
             Estim. CPU-Costs = 116.853 Estim. IO-Costs = 8

               1 INDEX RANGE SCAN BUT000~0
                 ( Estim. Costs = 7 , Estim. #Rows = 3 )
                 Search Columns: 2
                 Estim. CPU-Costs = 107.422 Estim. IO-Costs = 7
                 Access Predicates

I thought that we always had an estimated cost of 1 when we have the entire key. Am I wrong ?

I updated statistics of BUT000 indexes in DB20, but it doesn't change anything. Maybe this is not the correct method to update statistics ?

Thanks for your help.

Julien.