Re: determine num rows in a table without doing a count(*)

In article <3ac1df7b.11699719_at_news-server>, Nuno Souto <nsouto_at_nsw.bigpond.net.au.nospam> writes:
>On Tue, 27 Mar 2001 13:54:13 -0500, "Rocr"
><rolland.cright_at_pwgsc.gc.ca> wrote:
>
>>I could do a count but I am writing a script that will identify tables that
>>have large amounts of records and candidates for re-orgs. However I want
 to
>>set a threshold (20,000 records).
>>
>
>IMHO, if you want to identify candidates for reorgs, you're better off
>looking at the space allocated rather than the number of rows. I'd go
>for DBA_SEGMENTS or USER_SEGMENTS and look at the EXTENTS column for
>all SEGMENT_TYPE = 'TABLE'. That will pinpoint them much faster and
>easier. You can do all sorts of order by on the result set for all
>sorts of interesting info (by tablespace, by owner, etc).
>
>HTH
>
>Cheers
>Nuno Souto
>nsouto_at_bigpond.net.au.nospam
>http://www.users.bigpond.net.au/the_Den/index.html

The only way to get an accurate count of the number of rows in a table is to perform a select count(*) on the table; however, if you do not have to be exact and close will work then you could look at the estimated number of rows in the dba_tables or all_tables dictionary views and use this number. The number would be most accurate immediately after performing analyzes on all the tables. Full computes would give you the exact count at the moment of analyzation, but analyze with a large sample size would be close.

Just in case you are new and unaware of the dictionary view sys.dba_segments, it has the total allocated size in bytes and blocks. Perhaps it and sys.dba_extents and sys.dba_free_space may be of some interest to whatever it is you are trying to do with the row count information.

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net