Re: select count(*)

oratune_at_aol.com wrote in message <8lqdvo$abi$1_at_nnrp1.deja.com>...
>This is one of those interesting aspects of Oracle that many users are
>not aware of -- count(), max(), min(), avg(), sum() all circumvent the
>indexes and perform full table scans.

This is wrong.

>Let me repeat that:
>
>The functions count(), max(), min(), avg(), sum() ALL circumvent the
>indexes and perform FULL TABLE SCANS.

Let me repeat that :-) this is wrong.

The reasons a "select count() from table" might not use indexes are the same any select statement might not use indexes - if the select statement has no where clauses, or if the optimiser determines that it is more efficient not use the indexes. The select count() statement does NOT automatically imply that a full table scan will be used. Counter-examples below:

select count(*) from employees
where emp_id between 'P' and 'Q'

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
   4507    INDEX   GOAL: ANALYZED (RANGE SCAN)
               OF 'EMPLOYEES$EMP_ID' (NON-UNIQUE)

select count(1) from employees
where emp_id between 'P' and 'Q'

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
   4507    INDEX   GOAL: ANALYZED (RANGE SCAN)
               OF 'EMPLOYEES$EMP_ID' (NON-UNIQUE)

select count(emp_id) from employees
where emp_id between 'P' and 'Q'

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
   4507    INDEX   GOAL: ANALYZED (RANGE SCAN)
               OF 'EMPLOYEES$EMP_ID' (NON-UNIQUE)

select count(rowid) from employees
where emp_id between 'P' and 'Q'

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
   4507    INDEX   GOAL: ANALYZED (RANGE SCAN)
               OF 'EMPLOYEES$EMP_ID' (NON-UNIQUE)

From the above, we can see that all four alternatives execute exactly the same execution plan, so there's no difference between them. (The difference noted by the original poster is not statisticaly significant, and would be accounted for by different loads on the database at the time of running the queries, and other factors).

If you want to want to count *all* the rows in a table, you can force it to use an index. In theory it should be faster as you'll be doing a sequential scan of an index, which will require less block reads than a sequential scan of the data. However, full table scans are implemented so that the blocks are not stored in the buffer cache (or rather, they're marked as least-recently-used, so they're immediately aged out). This does not apply to an index scan, so an indexed read will result in replacing a large proportion of the buffer cache by your index blocks. This will force all other users to have to do physical I/O to repopulate the cache with their data blocks, which will have been overwritten. So although your query might run faster, the overall effect on the database will be to make all other queries run slower.
eg.

select /*+ INDEX(employees employees$emp_id) */

       count(*) from employees
where emp_id > ' '

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
  72896    INDEX   GOAL: ANALYZED (RANGE SCAN)
               OF 'EMPLOYEES$EMP_ID' (NON-UNIQUE)

Examples run under Oracle 8.1.5 on Solaris 2.5.1 with CBO and fully analysed table.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.