Re: Documenation for count(*) and table scans

count(*) will use a primary key automatically if there is one,

Andy Tasker
Spirocom Analysts Ltd

John P. Higgins wrote in message <3538020D.613C_at_deere.com>...
>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 Sat Apr 18 1998 - 04:27:55 CDT