Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557

    Unanswered: Why is it using index?

    Not sure why, but someone was running a select * on a 900+ million record table and it was taking a lifetime.

    8 partitions. Looking at the table I see 13 indexes. Explain path shows that select * with no where is using an INDEX ? This is the smallest index based on the leaf size and only has 27 unique values. Yes I know, why have an index with only 27 values on a billion record table. Don't ask me. I am not the one that designed it, nor the one to delete it.

    Why is it using the index when you are fetching ALL records? What am I missing. In QA it does tbs as it should. Diff, in QA table only have 70 mil.

    Thank you

    BTW. This is a dec(15) column mid field.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Cougar8000, is that the entire query "Select * From table-name"? No Where clause, Join, Sub-Select, Order By, Union, etc.?

    If it is just a Select * From Table, I agree that no index access would be needed or desired.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    yes

    Select * from tables

    Any idea why it is trying to use a bogus index?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm sure the answer is in the access plan, which you chose not to share with us. I would expect to see a parallel list prefetch there, and the easiest way to prepare the RID list for prefetch is to scan the smallest index.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Not really. Has the table been Reorged recently? If there has been a lot of Changes (especially Deletes), DB2 might think it is better getting the RIDS from an Index instead of reading a lot of empty (all rows deleted) pages.

    That is about the only thing I think of as a (really wild) suggestion.

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Nick,

    Access Plan:
    -----------
    Total Cost: 6.17634e+07
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    7.40797e+08
    DTQ
    ( 2)
    6.17634e+07
    1.28043e+07
    |
    9.25997e+07
    FETCH
    ( 3)
    6.15297e+07
    1.28043e+07
    /------+------\
    9.25997e+07 9.25997e+07
    RIDSCN TABLE: FCTFDSDB
    ( 4) FACT_FINC_PRFT_AND_LOS
    267230
    40585
    |
    9.25997e+07
    SORT
    ( 5)
    262625
    40585
    |
    9.25997e+07
    IXSCAN
    ( 6)
    103187
    40585
    |
    9.25997e+07
    INDEX: FCTFDSDB
    INX_FFPL_POPINFO


    This table is truncated and then reloaded on a monthly basis. No deletes in between.

    We have another copy of this table in QA as I just found out that has a full set of data and same indexes as prod and it is doing TBS.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    it is a bitch when you are not given a right info

    Now I am told that developers ADD 5 mil every month and then update all of the records.

    So, that seams to support what Nick is saying.

    Thanks guys.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you want to discourage the prefetch, try turning SEQDETECT off or play with PREFETCHSIZE and the block-based pool. However, not running SELECT * FROM A900MROWSTABLE usually yields better results...
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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