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.

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.

yes

Select * from tables

Any idea why it is trying to use a bogus index?

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.

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.

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.

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.

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...