Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Clustering_factor and performance
calberto2312_at_hotmail.com (Carlos Alberto) 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.
>
The more selective the indexed column is, the less tuning its clustering factor will help. So you probably won't benefit much from reorganizing the table if your SQL is run randomly. But if you run it within a cursor loop where records are accessed in a sorted order on column b, a better clustering factor could make some difference. I did that a couple times and got about 5-10 percent improvement. For you it could be different depending on the amount of cache you have, the way your database is configured, speed of I/O, etc.
> Thanks in advanced,
> Carlos
Received on Fri Aug 30 2002 - 11:17:58 CDT