slow select count(*) on partitioned table

[butterfm]

Hi,

I have a partitioned table in Oracle with no indexes.

select count(*) from <tabname> partition (name)

takes around 10 mins to count 70,000 rows. It's doing a full scan of the partition but this stills seems a long time, even if the data isn't already cached.

Would an index speed up a select count(*) ?. I was under the impression that you'd need to use select count(col name) to get it to use the index.

ta,
M

 

[Dagon]

The timing would depend on how powerful your system is, so it's difficult to say whether it's good or bad. How long do other sorts of queries typically take ? Is there data very fragmented e.g. did you previously have lots of rows in there which have been deleted ? Have you checked the plan of the query and also got all of the table stats up to date ?

 

[butterfm]

It is a well specced system.
Is there a way I can tell when the stats were last updated ?
I have done an explain and it is table scanning.

 

[jimirvine]

Would an index speed up a select count(*) ?. I was under the impression that you'd need to use select count(col name) to get it to use the index.

A unique index will rewrite the query to perform a fast full scan of the index, a non unique index will continue to use a fts if you write count(*) but will use a FFS if you do count(column_name)
(The advantage of a FFS is that it too can read multiple blocks as per the db_file_multiblock_read_count parameter)

HTH

 

[Dagon]

Is there a way I can tell when the stats were last updated ?

Look at sys.dba_tables.last_analyzed and num_rows. Also, because it's partitioned, look at USER_TAB_PARTITIONS as well.

 

[jimirvine]

I re-read my post there, I mis-spoke/mis-typed.
If the table in question has a primary key (using a unique OR a non-unique index, will perform a fast full scan(FFS) of that index. However any other index (unique - non-unique, part of a constraint or not) will not automatically use a that index in a count(*), it will however perform a FFS if you count the column containing the index.
Sorry for the confusion.

 

[butterfm]

Thanks for the info guys - that's a great help.