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: Clustering_factor and performance

Re: Clustering_factor and performance

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 30 Aug 2002 11:08:20 +1000
Message-ID: <Hrzb9.18427$g9.57480@newsfeeds.bigpond.com>


Hi Carlos,

Other than what you suggest, no.

The "worse" the clustering factor, the less effective the index and hence the less likely of Oracle using the index. The key is to ensure that the statistics are accurate and that the CBO is aware of this.

Some other DBs have a concept of a clustered index, the index by which most accesses use and the order by which the database will "attempt" to store the data in the tables. Oracle equivalent is clusters although I'm not suggesting that this is your solution.

However, as you want a number of different indexes to have a high clustering ratios, it makes it all a tad difficult. The most frequently used/important/range scan query based index is the one that ideally should have the best clustering factor. However, even after an ordered data load, depending, there is no guarantee that such ordering will remain effective over time.

Cheers

Richard
"Carlos Alberto" <calberto2312_at_hotmail.com> wrote in message news:72954535.0208291042.795c95b2_at_posting.google.com...
> Hi all,
>
> I have some very large tables in my database. There are some
> queries I have to run in these large tables that have a bad
> performance. Theyīre using the correct indexes, but the data of the
> column indexed are very scattered (I saw this at clustering_factor in
> dba_indexes). Is there a workaround to this? I know a possible one is
> to reorganize the table ordering by this column, but I couldnīt do
> that, because the other queries on other columns would suffer. I have
> a simple SELECT to demonstrate this :
>
> select a
> from big_table
> where b = 10
>
> Column b is indexed, and itīs selective, but the values are
> scattered over the table. Iīm using Oracle 8.0.6.3.0.
>
> Thanks in advanced,
> Carlos
Received on Thu Aug 29 2002 - 20:08:20 CDT

Original text of this message

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