Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Location
    Spain
    Posts
    15

    Unanswered: Optimize for 1 row???

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •