Results 1 to 10 of 10

Thread: force index

  1. #1
    Join Date
    Feb 2005
    Location
    Spain
    Posts
    15

    Unanswered: force index

    In DB v7 OS390 it's possible force the use of an index? For example, in SQL Server, the next query we force to use the index IX1:

    SELECT f1 FROM t1 WITH (INDEX= IX1)

    Thanks in advance

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    You can bind the SQL statement using an optimization hint. This will require that you have the appropriate rows in the PLAN_TABLE to reflect the usable access path that you wish to use.

  3. #3
    Join Date
    Feb 2005
    Location
    Spain
    Posts
    15
    Quote Originally Posted by urquel
    You can bind the SQL statement using an optimization hint. This will require that you have the appropriate rows in the PLAN_TABLE to reflect the usable access path that you wish to use.
    Well, sorry but I don't know what is exactly the PLAN_TABLE, and I suppose that it's difficult to explain in a post, isn't it?

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Quote Originally Posted by csm
    Well, sorry but I don't know what is exactly the PLAN_TABLE, and I suppose that it's difficult to explain in a post, isn't it?
    It would probably be easier for me to have you go to the manuals and look for "OPTHINT" or "Optimization Hint". However, if you do not know what the PLAN_TABLE is, it might be helpful to learn more about EXPLAIN in DB2. In most cases, if the table statistics are up-to-date and the table has the appropriate indexes, the DB2 optimizer will make the correct choice of whether or not to use an index.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    And, sometimes the technicque used to force the index usage is to use 'OPTIMIZE for n rows' .... I wouldn't recommend it unless you have a reasonable estimation for n

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

  6. #6
    Join Date
    Feb 2005
    Location
    Spain
    Posts
    15
    Quote Originally Posted by urquel
    It would probably be easier for me to have you go to the manuals and look for "OPTHINT" or "Optimization Hint".
    That's true, the problem is that I don't have a good manual for DB2 (in fact, I don't have any manual of DB2). It's complicated to explain here, I'm looking for resources about DB2: books, white papers, tutorials, courses (do you know a good one of IBM?)...


    Quote Originally Posted by urquel
    However, if you do not know what the PLAN_TABLE is, it might be helpful to learn more about EXPLAIN in DB2. In most cases, if the table statistics are up-to-date and the table has the appropriate indexes, the DB2 optimizer will make the correct choice of whether or not to use an index.
    Ok, if the statistics are up-to-date, the engine normally will choose the right way, but I need to run a battery of test in different scenarios (different index), so a clause that force to use one or another index to compare plans could be very useful, don't you think the same?

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Craig Mullin's 'db2 developers guide' is a very good book, if you wish to purchase one ...

    The manuals are here
    http://www-306.ibm.com/software/data...s/v7books.html
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by csm
    That's true, the problem is that I don't have a good manual for DB2 (in fact, I don't have any manual of DB2). It's complicated to explain here, I'm looking for resources about DB2: books, white papers, tutorials, courses (do you know a good one of IBM?)...

    Ok, if the statistics are up-to-date, the engine normally will choose the right way, but I need to run a battery of test in different scenarios (different index), so a clause that force to use one or another index to compare plans could be very useful, don't you think the same?
    No, I don't agree. DB2 uses a cost based optimizer that chooses the fastest access path, taking into consideration the size of the table, the indexes, the cardinality of the columns, the expected number of rows to be returned, etc. So assuming you have executed runstats properly (capturing full stats), and tell DB2 how many rows you expect to fetch when it is less than the entire result set (OPTIMIZE FOR n ROWS), then DB2 will chose the best access path 99.9% of the time.

    Of course "best" means best of the available access paths depending on which index(es) you have defined, what the clustering index is, whether the table has been reorged, etc. If performance is a problem, then examine the explain and determine whether you need to change the SQL or the parameters mentioned above.

    You can download most of the manuals here:
    http://www-306.ibm.com/software/data...s/v7books.html
    I recommend the acrobat versions, which can searched with the acrobat search function.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2005
    Location
    Spain
    Posts
    15
    Quote Originally Posted by Marcus_A
    No, I don't agree. DB2 uses a cost based optimizer that chooses the fastest access path, taking into consideration the size of the table, the indexes, the cardinality of the columns, the expected number of rows to be returned, etc. So assuming you have executed runstats properly (capturing full stats), and tell DB2 how many rows you expect to fetch when it is less than the entire result set (OPTIMIZE FOR n ROWS), then DB2 will chose the best access path 99.9% of the time.

    Of course "best" means best of the available access paths depending on which index(es) you have defined, what the clustering index is, whether the table has been reorged, etc. If performance is a problem, then examine the explain and determine whether you need to change the SQL or the parameters mentioned above.
    Ok, I try to explain my situation, though my poor english. I've got a table (clients) with 10mill rows and 60 fields. The table have a clustered index (IX1) on surname1, surname2, name and nif and different index: IX2 in name, surname1 and pk and IX3 in surname2, name and pk.

    We search on this table by the typical fields: name, surname 1 and surname 2, and the resulset (paging 10 rows) must be ordered by surname 1, surname 2, name and pk. When we search on surname1 there's no problem: the engine use IX1 because is good for search coincidences and later for the order. But, when we only search for surname 2 or name, IX3 or IX2 are good for search and bad for the order (the engine must order the coincidences).

    If we drop IX2 and IX3, searchs on surname2 or name will be slower but we improve the order. Ok, if surname2 or name is not so usual or not exists, IX1 will be completely scan, this is the inconvenient.

    I need to confirm this ideas with facts, that's because I'm asking about force index.

    Well, sorry for the sermon and thanks

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Assuming your have rerorged the tables/indexes, and then executed full runstats, and then a rebind (assuming statis SQL), then I would need the following:

    - Tablespace page size
    - Table DDL
    - Index DDL
    - SQL statements used in search
    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
  •