| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Index to speed up "select count(*) from" queries...
On Mon, 17 May 1999 09:56:15 GMT, tim_mcconechy_at_my-dejanews.com wrote:
>My application needs to do some count(*) queries...
>
>Is there some kind of index I can use to speed this query..
>
>On a table with 100000 records it takes a couple seconds longer than I
>would like...
As others have allready pointed out, select count(*) without a where clause will usually result in full table scan. You can easily revrite this query to perform full index scan or index range scan, by referencing PK in the where clause or by referencing PK's leading column in the count(). This *may* or *may not* speeed your querry. It depends on many things, mainly on the PK's index staructure and it's "healthiness".
You must know that with table scans Oracle uses "multiblock reads", while during index reads it allways read 1 block/read. Also, with table scans, blocks are read according to their physical position (ie disk head movements are usualy very "smooth"), while with index scans blocks are read in the logicall order of their key values (ie disk head movements are typicaly very "scattered"). So you can't simply say that because a space occupied by the index is smaller than the table's space it will be scanned quicker.
In Oracle 7.3.3 and above there is another access path available, that
is definitely the fastest way to count all rows in large tables. It is
called "fast full index scan". With it Oracle scans the index in a
simmilar way it scans the tables - using multiblock reads and reading
according to the phisical order of index blocks. You can enable this
feature with setting init parameter v733_plans_enabled=TRUE in 7.3 or
with fast_full_scan_enabled=TRUE in 8. You can even hint the CBO to
use this path by specifying a hint INDEX_FFS. Look into your tuning
guide for more information or search Oramag (www.oramag.com) for an
excelent article describing this feature by Guy Harrison (it was in
one of the previous year isues).
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |