query

[unknown]

i have a table custtrans. sp_spaceused shows 900+ million
rows. We know it has over 900+ rows. Table has all the right
indexes used by application.

I want to do a count(*) to check how many actual rows are
present in this table. What is the best way to do this? I
ran "select count(*) from custtrans" and killed it after 1
day(24+ hours) without any results.

The live data gets inserted into this table(and was being
inserted when the counts(*) was running). No blocking
otherwise our block monitoring script would have started
spitting alerts.

what happens to select count(*) when the rows keeps growing
for a table? What is the best way to run such counts?

NOTE:
I am looking for a solution OTHER THAN having to use where
criteria to count ranges which would be based on index and
then sum them up.

[Carl Kayser]

Post by unknown
i have a table custtrans. sp_spaceused shows 900+ million
rows. We know it has over 900+ rows. Table has all the right
indexes used by application.
I want to do a count(*) to check how many actual rows are
present in this table. What is the best way to do this? I
ran "select count(*) from custtrans" and killed it after 1
day(24+ hours) without any results.
The live data gets inserted into this table(and was being
inserted when the counts(*) was running). No blocking
otherwise our block monitoring script would have started
spitting alerts.
what happens to select count(*) when the rows keeps growing
for a table? What is the best way to run such counts?
I am looking for a solution OTHER THAN having to use where
criteria to count ranges which would be based on index and
then sum them up.

Server version?

Table is partitioned? (Round-robin?)

Isolation level used?

What query plan is used by "select count (*) ..."? Most efficient would be
if it used the narrowest index. But I don't think that any SQL will be 100%
accurate in this situation.

[unknown]

table custtrans. sp_spaceused shows 900+ million rows.
We know it has over 900+ rows. Table has all the right

indexes used by application. >

I want to do a count(*) to check how many actual rows
are present in this table. What is the best way to do
this? I ran "select count(*) from custtrans" and killed
it after 1 day(24+ hours) without any results.
The live data gets inserted into this table(and was
being inserted when the counts(*) was running). No
blocking otherwise our block monitoring script would
have started spitting alerts.
what happens to select count(*) when the rows keeps
growing for a table? What is the best way to run such

counts? >

I am looking for a solution OTHER THAN having to use
where criteria to count ranges which would be based on
index and then sum them up.

Server version?
Table is partitioned? (Round-robin?)
Isolation level used?
What query plan is used by "select count (*) ..."? Most
efficient would be if it used the narrowest index. But I
don't think that any SQL will be 100% accurate in this
situation.

Earlier when I ran it:
Isolation Level: Default
Table is not partitioned
Version: 12.5.3 ESD #7 on Solaris

1> select count(*) from custtrans
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
custtrans
Nested iteration.
Index : i1_custtrans
Forward scan.
Positioning at index start.
Index contains all needed columns. Base table will
not be read.
Using I/O Size 16 Kbytes for index leaf pages.
With MRU Buffer Replacement Strategy for index leaf
pages.

STEP 2
The type of query is SELECT.

Total estimated I/O cost for statement 1 (at line 1):
274858938.