Re: Documenation for count(*) and table scans

Using an index can be a problem. A unique index guarantees that no NON-NULL key is duplicated. But it does not index the nulls, so a count via the index misses some rows. Non-unique indexes have the same problem.

Only primary key indexes guarantee no duplicates AND no nulls. So counting the primary keys might use the index.

pbolduc_at_online-can.com wrote:
>
> I dont know about Oracle docs, but I have read that using:
>
> SELECT count(indexed column) ...
>
> is about approx 5% faster that count(*).
>
> Phil Bolduc
>
> In article <6h7lud$21mq$1_at_rtpnews.raleigh.ibm.com>,
> "Jim Morgan" <jimmorgan_at_csi.com> wrote:
> >
> > Does anyone know where I can find Oracle documentation that talks about the
> > intricacies of making count(*) use indexes to maximize performance? I've
> > always sidestepped this issue somehow in my applications but now I have a
> > situation where I must take care of it.
> >
> > I've heard conflicting information in this newsgroup and I'd like an
> > official Oracle doc to get an accurate answer. I am looking for ways of
> > making count(*) work efficiently when the only indexes you have defined for
> > a table are compound indexes. I want to avoid doing a table scan.
> >
> > --
> > Regards,
> > Jim
> >
> >
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Fri Apr 17 1998 - 20:29:49 CDT