467,171 Members | 1,234 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,171 developers. It's quick & easy.

select count(*) - Full table scan

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
  • viewed: 5737
Share:
2 Replies


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

Similar topics

3 posts views Thread by Ian T | last post: by
4 posts views Thread by jimh@netwasp.com | last post: by
8 posts views Thread by Rich | last post: by
10 posts views Thread by AC Slater | last post: by
12 posts views Thread by Bing Wu | last post: by
10 posts views Thread by Henk Ernst Blok | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.