Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: DB2 9.7.5 LIMIT/OFFSET Card estimated

    Hi,

    during some playing with LIMIT / OFFSET statement, I get the following results:

    SELECT m_key, header FROM "BOB1" order by m_key FETCH FIRST 1000 ROWS ONLY;

    card estimated: 1000 - OK.

    vs.

    SELECT m_key, header FROM "BOB1" order by m_key LIMIT 1000 OFFSET 0;

    card estimated: +1000000 - *Wow*.

    The table contains 11 Mio rows.

    Should not be the estimated card the same? In case one a index scan is used, in second case a awful table scan!

    C:\>db2level
    DB21085I Instanz "DB2" verwendet "32" Bit und DB2-Codefreigabe "SQL09075" mit Aktualitõts-ID "08060107".
    Informationstoken: "DB2 v9.7.500.4299", "special_27924", "IP23286_27924" und
    FixPak "5".
    Produkt ist in "C:\Programme\IBM\SQLLIB" mit DB2-Kopienamen "DB2COPY1" installiert.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I assume that you ran the following?

    db2set DB2_COMPATIBILITY_VECTOR=MYS

    Since this is a new feature specifically for MySQL compatibility, I would not be surprised if it doesn't work perfectly in terms of optimization.

    Using DB2 native syntax, you would use the following functions to acheive what you want:
    ROW_NUMBER() OVER ()

    Example:

    SELECT * from (SELECT p.*,row_number() over(order by product_id) as rn
    from products as p) as col
    where rn between 1 and 20
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2009
    Posts
    10
    Hi Marcus,

    db2set -g
    shows: DB2_COMPATIBILITY_VECTOR=MYS

    My current approach:

    SELECT m_key, header FROM "BOB1" order by m_key LIMIT 1000 OFFSET 0 OPTIMIZE FOR 1 ROWS

    The estimated card is still very, very large, but at least the optimizer choose the index scan, that's ok!

    The table was marked as 'volatile'.

    Hope there are not any drawbacks.

    Regards

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If the table is volatile, DB2 will use default stats instead of actual stats. That may skew the estimated card.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2009
    Posts
    10
    ... sure maybe skewed, but running on same table - both statements should output similar estimated cards, not magnitudes far away from each other.

    In my tests LIMIT/OFFSET together with OPTIMIZE FOR 1 ROWS, runs quicker than your approach.

    Neverless, Thank you.

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    could you plz put the full db2exfmt here。
    if you append "optimize for 1 rows" to "
    SELECT * from (SELECT p.*,row_number() over(order by product_id) as rn
    from products as p) as col
    where rn between 1 and 20 "
    It will run as quick as your approach ( db2 also will choose index scan instead of sort table)。

  7. #7
    Join Date
    Nov 2009
    Posts
    10
    Hi fengsun2,

    I can agree, using ROW_NUMBER() OVER () with OPTIMIZE FOR 1 ROWS will result in same quick response.

    ... and the estimated card is +1000000, same as LIMIT/OFFSET with OPTIMIZE FOR 1 ROWS

    Thank you.

Posting Permissions

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