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.

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)

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

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 ?

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

db2udbgirl, Can you post the explain query as an attachment - notepad or word file ?

Sathyaram

Can you please PM the exact details of the problem you faced when trying to post "all details"

Sathyaram