Hi All,

ENV: Db2 9.5 FP5, RedHat Linux 5.3

I have a statment that sometimes takes too long to or not complete at all:

"Select auth_id from db2inst1.datamodel order by auth_id desc fetch first 1 rows only optimize for 1 rows"

There is an index that exists for auth_id and the explain plan reveals that the index is being used. The table datamodel isn't very big and has only 500 some rows in it.

Would it be better for performace to NOT use the optimize for clause in this situation?

Any other pointers.

Thanks

rangupt, why don't you just use:
If the Index allows a reverse scan (and AUTH_ID is the first or only key), this should be a 1 fetch index access (about the best you can get)

Thanks Stealth_DBA

The access plan for all the 3 statements (MAX(auth_id), without Optimize clause and with optimize clause) reveals the exact same estimated cost.

I ran application snapshot and db2pd to collect some information and found out that this particular statement (the originial one with 'Optimize for' clause) has way over 300,000 NumRef and NumExec.

What I'm wondering is why DB2 chooses an index access for a table with just 500 rows. If the table doesn't have many columns (which I don't know), a table scan may be an option as well. Do you have current statistics?

Thats another thing I don't understand. The table as i said is quite small less than 500 rows. Yes the statistics were updated over the weekend.

The table has 8 columns auth_id being the primary key.

The only column being selected is auth_id, which is in the index for the PK and there is no WHERE clause. So DB2 can satisfy the query with "index only" access, although it will scan all the leaf pages of the index instead of using the b-tree.