Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Explain Plan Question

    DB2 UDB V8.1 FP 14/AIX 5.3
    DB has 8 logical partitions

    One of the SQL runs for an unusually long time so when I check the App.Shap I found out that SQL is performing tablescan of big table.

    Here are few lines from app.snap
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 0
    Rows read = 1829438580
    Rows written = 1205012047


    But I ran db2expln to check the plan but as per the plan the query should perform tablescan as below.
    RETURN
    ( 1)
    |
    BTQ
    ( 2)
    |
    NLJOIN
    ( 3)
    /---------------------------/ \---------------------------\
    BTQ FILTER
    ( 4) ( 8)
    | |
    GRPBY HSJOIN
    ( 5) ( 9)
    | /---------------------/ \---------------------\
    FETCH NLJOIN BTQ
    ( 6) ( 10) ( 22)
    / \ /-------------------/ \-------------------\ |
    IXSCAN Table: BTQ FETCH FETCH
    ( 6) CARD ( 11) (----) ( 23)
    | DWH_CONTROL | /---------/ \----\ |
    Index: FETCH RIDSCN Table: IXSCAN
    ETL ( 12) ( 15) CARD ( 23)
    TX1 /-/ \ +--------------------+--------------------+ TRANSACTION |
    IXSCAN Table: SORT SORT SORT Index:
    ( 12) SMTUMT ( 16) ( 18) ( 20) SYSIBM
    | CLAIM_TYPE | | | SQL060227125909990
    Index: IXSCAN IXSCAN IXSCAN
    SMTUMT ( 17) ( 19) ( 21)
    CLAIM_TYPE_LAAMUI / \ / \ / \
    Index: Table: Index: Table: Index: Table:
    CARD CARD CARD CARD CARD CARD
    TX3 TRANSACTION TX3 TRANSACTION TX3 TRANSACTION


    Question:
    As per explain plan SQL should use index scan but the query goes for tablescan. I cannot understand this situation. Can you please share your thoughts on this.

  2. #2
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Typo Error on previous post:
    But I ran db2expln to check the plan but as per the plan the query should perform indexscan (not tablescan)

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are you sure that it is doing a tablescan? Just because the number of rows read is large does not necessarily mean a table scan. Can you supply the SQL of the query and the DDL for the tables and indexes involved? Also the cardinality of the tables and how many rows you think it should be reading through.

    Andy

  4. #4
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Andy: I'm unable to post all the details over this forum (as I think that i got deleted automatically. Do I have any other options ?

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) What is the cardinality of the table?
    2) how many rows do you expect to receive from the query.
    3) how many other tables are joined in the query and how? How are they accessed?
    4) are runstats current?
    5) how volatile are the tables (inserts, updates, deletes)?

    Andy

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2udbgirl, Can you post the explain query as an attachment - notepad or word file ?

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

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you please PM the exact details of the problem you faced when trying to post "all details"

    Sathyaram

    Quote Originally Posted by db2udbgirl
    Andy: I'm unable to post all the details over this forum (as I think that i got deleted automatically. Do I have any other options ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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