Re: "select count('x') from BigTable" takes more than 1 minute !

On 24 Aug 2006 15:07:21 -0700, "Big George" <jbeteta_at_gmail.com> wrote:

>If I make a query searching for a NumID or for Names, it is very fast.
>Get row immediately, like:
>select * from BigTable
>where NumID = '12345678'
>
>I supose it means that indexes are OK

Not necessarily. It means indexes exist, are considered to use the least I/O to perform this query. Ie: even if they are used, they still can be corrupted.

>The problem is when I try to make a simple count(*), like:
>
>select count('x') from BigTable
>
>It takes 1min30 sec ! Is it normal? Or is it to be faster? I think that
>something is wrong with the table. Is it not supposed that Oracle can
>handle this amount of records very fast? Maybe there is some tuning
>that it is needed.

What is the business case for this exercise? Do you want to find out how to trash database performance? A quick calculation shows you are retrieving 1.6 Gb in 90 sec, assuming 8k block size. Do you call that slow? You need to realize you are probably replacing the entire buffer cache multiple times.  

--
Sybrand Bakker, Senior Oracle DBA