Re: count (*) and index use (was RE: is it possible in pl/sql?)

On Thu, 17 Feb 2005 01:12:01 +0100, jaromir nemec <jaromir_at_db-nemec.com> wrote:
> Hi Jacques,
>
>
> > A bitmap index will index columns with null values and therefore can be
> > used for a count (*) on the table.
>
> A bitmap index makes the count(*) very performant indeed, but on the other
> side (as usual there is no free lunch) a bitmap index is usually defined on
> not very volatile tables, so you probably don't need the count(*) very
> frequently:)

True. However bitmap indexes are often created to support DSS queries in large DW environments which are subject to periodic load (daily or at the end of a shift say). It would probably make sense in such an environment to drop the indexes during the load and recreate them at the end *and* count the number rows shortly after load.

I also wonder how often one actually needs count(*) instead of the approximation that num_rows gives. I can see count(*) being of interest where count(*) has a value of 0 or 1. On tables where it is slow enough however to be a concern how long it takes to query count(*) <broad generalisation> in most cases where count(*) is used NUM_ROWS ought to suffice because knowing that the table has , say 7m rows gives as much information as the count of 7253612 rows. </broad generalisation>

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l