First of all, I work in a OS390 V7 DB2 database.

Well, what can you say me about the clause "OPTIMIZE FOR XX ROW"? I haven't found any useful information, and some persons recommend it when the query doesn't have good index.

I have a query to a 2 tables (10mill rows each one) of persons, and I have one index in the column of the WHERE clause. When I run this query, it takes 5 minutes; use the OPTIMIZE clause and the query takes 1 min; remove the OPTIMIZE clause and the query takes 1 min 30 seg!!!!! What's happen here? This is very strange, isn't it?

Thanks in advance

The optimize clause may do several things, one of which is to shut off sequential prefetch. A lot depends on whether DB2 must materialize the answer set in a temp table before it returns the first row of the answer set to you. For example ,if you had an order by clause, the answer set will have to be materialized in a temp table (unless DB2 can use an index in lieu of sorting of the rows).

Keep in mind that it is hard to exactly predict query response time on a system that it used by many other people at the same time. A major factor in response time is whether the data is already in the buffer pool when you ask for it, or whether DB2 must get it from disk.

When the system is busy, pages your query needs may be flushed out of the buffer pool more often. Even on a system that is not busy, the first time you run a query will usually be slower than if you run it again right after that, because at least some of the needed pages are in the buffer pool when you run the query the second time.