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: Performance Questions Re-Indexes

Re: Performance Questions Re-Indexes

From: GDN <gert_deneve_at_hotmail.com>
Date: Sat, 5 Feb 2000 12:45:58 +0100
Message-ID: <Q7Um4.80$G14.503@news-brussels.gip.net>


Q1

Rebuilding indexes on a regular base is usefull for tables where lot's of data is added or index-key's are changed. Doing so results in a B-tree that has a better distribution over the leafs, minimizing the amount's of read's necessary to access the index.

Use the rebuild of an index. That way the rebuild is using the existing index to recreate the b-tree. If you drop the index and recreate it, a full table scan has to be done.

Q2

In general yes.

But, sometimes it's necessary to use hints in the SQL-statement.

Also, when you have a field in your table where you put an index on, and there is a certain value of that field thats rare compared to the other values, it's usefull to do an analyze for the colum. eg. table with 1000000 records

         field status char(1)
         value 'A' -> 250000 records
         value 'B'  -> 300000 records
         value 'C' -> 449490 records
         value 'D' -> 10 records
         When analyzing the table, the optimizer assumes an even spread of
data
        over the different values, resulting in index range scans.
         When we analyzed for columns status, the optimizer will see that we
have a lot of records for values A,B,C resulting in an full table scan. Only for value D the optimizer will choose index range scan. So analyzing specific columns may result in big performance gains.

Regards
Gert

Keith Jamieson <jamiesonk_at_phoenix.ie> wrote in message news:878uue$7lr$1_at_kermit.esat.net...
> I have a rapidly growing table at a customer site(Currently contains 1.2
> million rows). Some indexes were created for this table.
> When they were put on initially the query times returned were acceptable,
> but as the table has grown the query times have got worse. I am also
aware
> of some coding issues which need to be resolved, but initially I would
just
> like the benefits of this groups
> experience.
>
> Q1) Am I correct in assuming that the Indexes should be recreated on a
> regular basis?
>
> Q2) If we switched to the cost based optimiser, could I assume that as
long
> as the statistics were regenerated on a regular basis, then
> the performance would be better than the rule based optimiser.
>
>
>
>
>
Received on Sat Feb 05 2000 - 05:45:58 CST

Original text of this message

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