Re: Index to speed up "select count(*) from" queries...

From: Mark Powell <Mark.Powell_at_eds.com>
Date: 17 May 1999 12:57:54 GMT
Message-ID: <01bea064$f9df7e60$a12c6394@J00679271.ddc.eds.com>

If the table in question has a primary key you can use an index hint to get the count(*) to read the index instead of the table. You can verify the results using explain plan.

ORA805> EXPLAIN PLAN SET statement_id = 'mpowel01' FOR 2 -- Insert sql after this line. WARNING - Do not end sql in ';' 3 select /*+ INDEX(vendor_quote vendor_quote_pk) */ count(*) 4 from vendor_quote
5 /

Explained.

ORA805> set echo off

COST CARDINALITY QUERY_PLAN
---------- -----------

        16           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
        16        1826     3.1 INDEX FULL SCAN VENDOR_QUOTE_PK UNIQUE

3 rows selected.

James Petts <jpetts_at_celltech.co.uk> wrote in article <373fff96.16492575_at_right.celltech>...
> 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..
>
> No. Count(*) does a full table scan.
>
Received on Mon May 17 1999 - 07:57:54 CDT