Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index to speed up "select count(*) from" queries...

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

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Mon, 17 May 1999 14:57:39 GMT
Message-ID: <7hpap1$hcv$1@nnrp1.deja.com>


Response is in line.
In article <7hotol$8l0$1_at_nnrp1.deja.com>,   tim_mcconechy_at_my-dejanews.com wrote:
> hi....
>
> The query is always single table.. Something like...
> select count(*) from articolr;
>
> I tried :
> select count(recno) from articolr; (4 secs)
> select count(*) from articolr; (3 secs)
> select count(rowid) from articolr;(3 secs)
>
> But all seem the same..

Try to force the index, with CBO, it should work better if you add a where clause. IE:
  SELECT COUNT(recno) FROM articolr
    WHERE recno > -999999 ;

This may help. The WHERE clause should force the use of the index. The value should be out side of the valid range for recno. If it doesn't, then you could provide a hint in the SELECT to force the use of the index.

HTH
   James
>
> Articolr has 100000 records and a PK called recno...
>
> All three queries do....
> SELECT STATEMENT Optimizer=CHOOSE
> SORT (AGGREGATE)
> TABLE ACCESS (FULL) OF ARTICOLR
>
> According to Explain plan....
>
> Any ideas if an index could help...
>

[Snippage]
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Mon May 17 1999 - 09:57:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US