select count(*) - Full table scan

[db2udbgirl]

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)

[Joe Weinstein]

db2udbgirl wrote:

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)

Usually, if there is any unique index on a table,
the DBMS can avoid a table scan, else it has to.

[Mark A]

> db2udbgirl wrote:

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)

"Joe Weinstein" <jo*******@bea.com> wrote in message
news:44**************@bea.com...
Usually, if there is any unique index on a table,
the DBMS can avoid a table scan, else it has to.

Not exactly. There is one index row for each table row, so any index will
do, unique or non-unique. DB2 will likely choose the smallest index and
count the index rows. If the table is small enough, DB2 may count the table
rows and bypass the index. Try an explain with each scenario.