By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

select count(*) - Full table scan

P: n/a
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)

Mar 8 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/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)


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

Mar 8 '06 #2

P: n/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.
Mar 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.