Re: Documenation for count(*) and table scans

Finally, a useful suggestion. It makes since that is how to force the use of an index with any query operation (I have used dummy WHERE col > 0 clauses just to force use of an index before). It just never occurred to me to do the same thing with a count(*) query -- I'll have to give it a try.

Still no one has referred me to any Oracle documentation. How can so many people offer opinions on this subject but not one can point to some Oracle doc and say 'See, it says so right here'? I'm not faulting you, you found a workaround because you couldn't find any doc either. But others are telling me how to do it and I know at least some of those people are dead wrong.

There seems to be enough interest in this topic...has anyone found any documentation from Oracle on the subject? Surely there must be SOMETHING...

I'm curious...do you KNOW the index was used by using a database tool of some sort or did you surmise that because of the performance improvement? I think that would be a reasonable assumption to make, but I have also noticed that count(*) queries do not always run in the same amount of time (I assume the difference is because of caching). In any case, I'm going to try forcing the use of an index via the WHERE clause. --
Regards,
Jim
aa wrote in message <6h9b5d$j3d$1_at_news.interlog.com>...
|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 Sat Apr 18 1998 - 18:09:10 CDT