Re: count (*) and index use (was RE: is it possible in pl/sql?)
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: jaromir@xxxxxxxxxxxx
- Date: Thu, 17 Feb 2005 06:35:53 +0000
On Thu, 17 Feb 2005 01:12:01 +0100, jaromir nemec <jaromir@xxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
