Re: What is Select count(*) from ... doing?

Oracle is performing a count of all rows. To do this it may either perform a full table scan or utilize a primary key. You mentioned inserting 1.5 million rows but never indicated the average row size. What I'm getting to is if you have 1.5 million rows and the average rows size is 200 bytes, well you have some real estate to cover. Another consideration is to review how many extents this particular table has. If you exceed 20 and the entire tablespace is located on one disk, well some may say your table is fragmented. Is your database block size so small that you require lots of reads ? If you already have large database blocks, is you SGA large enough to accomodate multiple reads without being forced to page ? Can you stripe this table across several disks and reduce I/O contention and if possible stripe your primary key/indexes as well.

Lastly, the 'analyze table ...' function provides a method for calculating either exact or estimated row counts for a given table. If you use this function for obtaining an exact row count, you will not be happy unless you address some or all of the aforementioned hints I conveyed above.

Good Luck Received on Thu Jul 27 1995 - 00:00:00 CEST