If I perform a select count(*) from tred.order_delivery query will it internally perform a full table scan to determine the row count for the following scenario
case 1: There is a primary key on a column
case 2: There is a compound key on 1 columns col1, col2
case 3: No primary key in the table (Just for my education)

select count(*) from table should do a table scan no matter what the conditions.

Andy

Usually DB2 will count the index rows (the smallest available index) instead of reading the table rows, since there is always one index row for each data row (even if the index is not unique). But if the table was small enough, then it might just use the table rows instead.

You can try this out and evaluate the visual explain in the Control Center to see the access path.

Yes, Thats my plan. Thanks guys for your comments.