Re: Documenation for count(*) and table scans

Jim,

You didn't say whether you are doing a 'SELECT COUNT(*) FROM ABC;' or 'SELECT COUNT(*) FROM ABC WHERE COL1='something' AND COL2='another thing';'.
If you are doing the former, full table scan is the most efficient way to do it. You can speed it up by using Parallel Query if you have multiple CPUs. If you are doing the latter, than Oracle should be smart enough to use index (assuming COL1 and COL2 is in a composite index and the table is analyzed if you are using cost-base optimizer, etc).

Pei
Oracle DBA

Jim Morgan <jimmorgan_at_csi.com> wrote in article <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 - 14:41:43 CDT