Re: select count(*)

In article <mWeg5.1096$_8.143299_at_nnrp3.clara.net>,   "Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote:
> 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.
>
>

I shall consider myself further educated on 8i.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.