Why a simple select all query uses index scan and not table scan ?

[selma]

I am puzzled by visual explain for a simple query ( select * from
mytable ) chooses an index scan. That index has only a single column
and the table has 30 columns not covered by the index. When running
explain for another database (same schema structure), it showed table
scan. These are all production databases with >10 GB of real data, and
stats are all up to date. Reorgchk showed nothing bad.

Per chance visual explain GUI was acting up, I tried the manual
db2exfmt method and confirm same index scan/fetch for this particular
database. This is really puzzling. Does anyone have an answer? Thank
you for your reply.

Selma

[m0002a]

selma wrote:

I am puzzled by visual explain for a simple query ( select * from
mytable ) chooses an index scan. That index has only a single column
and the table has 30 columns not covered by the index. When running
explain for another database (same schema structure), it showed table
scan. These are all production databases with >10 GB of real data, and stats are all up to date. Reorgchk showed nothing bad.

Per chance visual explain GUI was acting up, I tried the manual
db2exfmt method and confirm same index scan/fetch for this particular
database. This is really puzzling. Does anyone have an answer? Thank
you for your reply.

Selma

First, make sure you have run runstats. Then, make sure the table is
not set to volatile (check syscat.tables).

If the problem still persists, run db2look and post the exact DDL for
the table and index(es) and the exact SQL statement.

[Kiran]

Hi Selma,
You can also check for if any MQT's (Materialised Query Tables ) are
created for the select statements in the database with table scan. You
can see out the explain if its doing a table scan on MQT ( or AST
automatic summary tables).

Kiran

[Shyam Peri]

Did u try describing index structures on the table(s) which are
involved in the query?