Linda,
I am guessing that since your table is partitioned on an unspecified date
column, that the index on TID is either LOCAL or non-partitioned (i.e.
GLOBAL).
If it is LOCAL (you would have had to specify the keyword, as it is not the
default), then you will be performing indexed RANGE scans on each of the
partitions in the index. Naturally, the more partitions there are, the
longer this may take, but probably not a great deal longer than if the index
was a GLOBAL non-partitioned index.
But regardless of the number of RANGE scans and the type of index it is, the
main question is whether TID is a good index to use in the first place.
This is a matter of data, purely the nature of the data.
You can diagnose this better using results from the following query:
SELECT NUM_ROWS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
LAST_ANALYZED
FROM DBA_INDEXES
WHERE INDEX_NAME = '<name-of-index>';
Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and
AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate
the cost of an index RANGE scan (assuming that column-level statistics or
"histograms" have not been gathered).
If the values of these two columns are high, then the CBO will be hesitant
to use the index, and with good reason. Thus, with the use of the index
rejected as an option, you'll of course have a FULL table scan on your
hands.
There is probably more to it, but this should be a start. Feel free to post
the results of the query above to the list, if you wish...
Hope this helps...
-Tim
on 10/27/03 6:24 AM, Linda Wang at [EMAIL PROTECTED] wrote:
> 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
>
> _________________________________________________________________
> Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet
> Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
