RE: Performance off "count(*)"

Hi

FWIW I'm with Tanel on this one ;-)

Both, the number of rows stored in the "data header" and in the "table directory" contain deleted rows and one "row" for each rowpiece. In addition, when compression or clusters are used, additional "rows" are available for the structures needed to support them.

As a result, the only way to get the number of rows is to read the "row header" of the table on which the count must be performed. Therefore, IMO and according to my tests, the only optimization of the count(*) is to be able to access the row header only. In other words, to completely skip row data. In my book, in chapter 12, there is a figure that shows that effect.

Cheers,
Chris

Troubleshooting Oracle Performance, Apress 2008 http://top.antognini.ch

--
http://www.freelists.org/webpage/oracle-l