Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Question Unanswered: How is an index used in a DB2 query?

    Hi there,

    In DB2, the index name is used to create and drop the index. Other than that, the nme is not used in queries or updates to the table. So, the question is how an index is used in a query?

    Thaks in advance,

    Steven

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In a relational database, the physical access path is isolated from the user. DB2 choses the best access path (the fastest) by looking at the statistics (number of rows, number of unique values, etc), the indexes available, and chooses the best access path. For best results, make sure you run the runstats command on each table (and get the most detailed stats available). See the Command Reference manual.
    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
    Mar 2004
    Posts
    61

    Angry Thanks,

    It's still depended on human being to run the runstats command to build the statistics, how can you say "In a relational database, the physical access path is isolated from the user"? Once I have took a look to those statistics and it looked so ridiculous that it showed there was no data in the table, in fact it contained several millions. I think if DB2 offers a way for developer to specify how a query uses an index, like Sybase, it would save much more trouble than it chooses itself.

    Steven

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Then Sybase is not relational, to extent that the user must supply anything but the desired results. This may partly explain its rapidly declining market share.

    It takes a lot more human intervention to figure out which is the optimal index to use than it does to set up a scheduled Runstats. Even if Runstats is never run (stats are -1) then DB2 uses default values that would assume that there are rows in the table.

    If the table is small enough, usually less than 10 pages, it is normally faster to do a tablespace scan even if indexes exist. If you always want to encourage the index to be used anyway, alter the table to "volatile."
    Last edited by Marcus_A; 09-03-04 at 10:26.
    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
    Mar 2004
    Posts
    61
    We build the indexes, certainly we know better which index should be used than the system.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by hiolgc
    We build the indexes, certainly we know better which index should be used than the system.
    Maybe you know better than Sybase, but you do NOT know better than DB2.
    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
    Mar 2004
    Posts
    61
    Really? I don't think so!

  8. #8
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Exclamation supplying information to optimizer not necessary to be not relational, or not clever

    I don't know anything about sybase, but with oracle, one can supply any info to optimizer to direct its way to execute a sql. Without additional information, oracle will use its default method to determine a best path. Oracle is stupid some time, but who think Oracle is not clever in general?

    Quote Originally Posted by Marcus_A
    Then Sybase is not relational, to extent that the user must supply anything but the desired results. This may partly explain its rapidly declining market share.

    It takes a lot more human intervention to figure out which is the optimal index to use than it does to set up a scheduled Runstats. Even if Runstats is never run (stats are -1) then DB2 uses default values that would assume that there are rows in the table.

    If the table is small enough, usually less than 10 pages, it is normally faster to do a tablespace scan even if indexes exist. If you always want to encourage the index to be used anyway, alter the table to "volatile."

Posting Permissions

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