Re: Documenation for count(*) and table scans

I have the same problem where Oracle will not use an index to do a count(*). Even if an index exists it chooses a table scan. It seems that Oracle will require a Where clause before it will pick the index. So I tricked it to scan the index by using a Where clause that will select the entire table:

EG: Select count(*) from table where id>0

Id is a numeric column which has positive values. So Id>0 will select the whole table.

Jim Morgan wrote in message <6h7lud$21mq$1_at_rtpnews.raleigh.ibm.com>...
>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
>
>
Received on Fri Apr 17 1998 - 23:42:36 CDT