RE: performance issue on select count(*)

Linda,

  I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ?

SF

>----- ------- Original Message ------- -----
>From: "Linda Wang" <lwang344_at_hotmail.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Mon, 27 Oct 2003 05:24:32
>
>Hi,
>I have an online application that does a 'select
>count(*)' on a few tables.
>The 'select counts' always runs slow (about 10secs)
>for the first time and
>then fast again (< 1sec) after subsequent accesses.
>The query runs slow
>again when the data is flushed out of the buffer
>cache.
>10046 trace shows that the query takes a long time
>whenever there are disk
>accesses to fetch the data (about 1000 8K) into db
>cache. It should not take
>that long to fetch 1000 8K blocks into the cache
>and I/O does not appear to
>be the problem.
>
>Anyone has any idea what the problem may be or how
>I can speed up my query?
>
>DB: 8.1.7.4
>query: select count(*) from tickets where
>tid='value1';
>where tickets has about 2 million records partition
>on a date field.
>and tid is indexed.
>
>thanks.
>
>linda
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).