Just try running an explain plan against your select count statement. Run it against a table with a primary key, and one without one. Run it against a table with a unique key but no primary key.
What you will find is that Oracle will pick the quickest way to get the result. If there is a PK or Unique Key, it will scan the index - because the column is NOT NULL, it will have an entry for every row.
If the table does not have any unique index to use, it will count the rows in the table.
Tom
-- --Original Message-- -- From: david wendelken [mailto:davewendelken@(protected)] Sent: Thursday, February 10, 2005 9:05 AM To: oracle-l@(protected) Subject: Re: is it possible in pl/sql?
It's always good to have folks challenge your beliefs - helps one to grow!
It would never have occurred to me to count the number of reads - because the same number of rows would have to be read either way.
Where I believed the efficiency to come in was in the internals of the processing of the count function.
In other contexts, * means "all the columns returned by the query". A reasonable extrapolation in this case would mean "the values of all the columns returned by the query".
Since count does not increment it's internal counter when the value passed to it is null, it made sense to believe that the count function had to check the values returned to verify whether a non-null value had been passed to it.
Therefore, it made sense to expect it to have to parse (via if-statement logic) thru the values to determine whether the aggregate value was null. I/O wouldn't be longer but more cpu time would be required.
This morning, I tested count(*) against a table that had a row comprised solely of null values and that row was counted. (Never tested that before as I've never thought of a reason to have a table like that!)
Checked the manual, and it is treating the * as a special character, not as I believed.
