Re: Would Oracle use index on count(*)?

From: Urs Meier <umeier_at_trivadis.ch>
Date: Fri, 17 Apr 1998 11:45:09 +0200
Message-ID: <353724A5.7BCB7D27@trivadis.ch>

Oracle uses a superior I/O mechanism for Full Table Scan (multiblock read count). Indexes are read block by block. So there is a good chance that the Full Table Scan will perform better.

Starting with V733 (set init.ora v733_plans_enabled=true) and Oracle8 of course, Oracle can also do full index scan (called fast full scan). However, there must be an index on a NOT NULL column or a bitmap index (bitmap indexes can resolve NULL queries)

Regards,
Urs

aa wrote:

> When I issue an Select count(*) from Table, Oracle always performs a table
> scan even though an index is available. In MS SQL Server, the optimizer will
> chose to scan the index (if one is available) instead of the table. Why
> doesn't Oracle do this?

--
Urs Meier (umeier_at_trivadis.ch)
Trivadis AG
Elisabethenanlage 9
CH-4051 Basel
Phone +41 61 279 97 55
Fax +41 61 279 97 56
http://www.trivadis.com Received on Fri Apr 17 1998 - 04:45:09 CDT