Results 1 to 7 of 7

Thread: Full tablescan

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: Full tablescan

    I am running db2 v8.1.6.664 on win2k.

    I have a generic question.
    We have a Data warehouse db and queries are taking some time. I did a explain plan, reorgchk/reorg and runstats, bind etc but the issue seem to be with tablescan where most of the time gets spent. This in turn impact resource utilization eg: CPU, disk sorts etc

    btw, summary table/materialized view is not an option since most of the queries are generated at runtime through external application...

    Are there any specific ways in which one can avoid full tablescans?

    Any thoughts/ideas appreciated.

    TIA.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes. Indexes. For example, if most of your queries involve a date range, then it is a very good idea to put an index on the date column. Version 8.1 as the "Design Advisor" to help in this area.

    HTH

    Andy

  3. #3
    Join Date
    Sep 2003
    Posts
    218
    thanks! for your response.

    I have tried design advisor and that does not seem to suggest index creation which would reduce time nor avoid ts...

    I am doing a sum(.....) in my query therefore assuming that this will go through each and every record in the table. TS relates in high % of sorts (inspite of increasing SORTHEAP and SHEAPTHRES parameters....)

    I am still not sure how to go about this?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are on a Datawarehouse, many of your sorts tend to overflow ... You should consider tuning I/O, like placement of tempspace containers, bufferpools etc.

    MQTs may still be an option if your queries(though user generated on the fly) use a similar predicates, summarization etc.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There are two things that greatly affect whether a tablescan will be used instead of an index scan. The WHERE clause and the ORDER BY clause.
    Examine these, mostly the WHERE clause. Some predicates in the WHERE clause can cause a tablescan even if some of them refer to indexed columns. Best rule of thumb here is to keep the predicates simple and not to use functions here.

    Andy

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If DB2 needs to read all the pages in the tablespace, then it will do a tablespace scan because that is the fastest way to do it. If you post the DDL and SQL, then maybe someone can verify that a tablespace is needed.

    Even if you have a tablespace scan, there are ways to speed it up, such using the correct extent size, prefetch size, proper number and placement of containers, and proper settings to invoke intra-partition parallelism. How well these things will work depends somewhat on your disk subsystem and how many seperate disks (or arrays) you are able to use for the tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Sep 2003
    Posts
    218
    Thanks for all the suggestions..

    Let me work on these input to see if we can avoid TS...

    Regards.

Posting Permissions

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