Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unhappy Unanswered: unique index not untilized for large table

    I have a very large table (a few millions of rows) in my db2 database:

    create table test ( c1 integer not null, c2 integer not null, c3 integer not null, c4 integer not null )

    create unique index testInd on test(c2, c4)

    sql1: select max(c2) from test
    sql2: select min(c2) from test
    when I run the sql above in db2expln, it tells me that the sql1 is using relation scan and this doesn't happen in a small table. while sq2 is Ok for small and large table.

    execution time for sql1 in large table is extremely slow!!! can anyone expert help me to solve the problems. for your information i did runstats (with/without distribution) on that large table.


    Please help. thanks
    SzeHau

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: unique index not untilized for large table

    Use the ALLOW REVERSE SCAN option in the CREATE INDEX Statement

    Check the SQL Reference for the correct syntax ...

    HTH

    Sathyaram



    Originally posted by szehau
    I have a very large table (a few millions of rows) in my db2 database:

    create table test ( c1 integer not null, c2 integer not null, c3 integer not null, c4 integer not null )

    create unique index testInd on test(c2, c4)

    sql1: select max(c2) from test
    sql2: select min(c2) from test
    when I run the sql above in db2expln, it tells me that the sql1 is using relation scan and this doesn't happen in a small table. while sq2 is Ok for small and large table.

    execution time for sql1 in large table is extremely slow!!! can anyone expert help me to solve the problems. for your information i did runstats (with/without distribution) on that large table.


    Please help. thanks
    SzeHau
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Re: unique index not untilized for large table

    Originally posted by sathyaram_s
    Use the ALLOW REVERSE SCAN option in the CREATE INDEX Statement

    Check the SQL Reference for the correct syntax ...

    HTH

    Sathyaram

    Any other solution other than changing the original database schema.. like tweaking the sql... because the the schema is used for cross-platform and different DBMS like informix

Posting Permissions

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