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: how can I check if a index is needed to be rebuild or not?

Re: how can I check if a index is needed to be rebuild or not?

From: Howard J. Rogers <howardjr_at_www.com>
Date: Thu, 28 Dec 2000 19:19:12 +1100
Message-ID: <3a4af778@news.iprimus.com.au>

"Ted Knijff" <knijff_at_bigfoot.com> wrote in message news:3a4af0dd.1303153_at_news.online.de...
> Try :
> ANALYZE myTable COMPUTE STATISTICS
>
> It reorganizes the indices (indexes ?) and the performance is much
> better if you have a table with many updates and a large value spread
> in the primary key.
>
> Ted
>

I hesitate to use the words 'total crap', but they're the only ones that spring to mind. "Compute Statistics" does precisely that, and nothing more. Under no circumstances does it "reorganise the indices" (which is the pedantically correct plural for technical uses, incidentally -but since you prefer to spell 'reorganise' with a "Z", I'm surprised you care that much!)

If you notice a speed improvement after doing the computation, it will be because the optimiser is better able to work out the most effective access path to the data, having more accurate statistics on which to base its judgement. Sod all to do with re-organising indexes.

The only thing that re-organises indexes is an 'alter index blah rebuild' command.

Regards
HJR
>
> On Thu, 28 Dec 2000 01:00:36 -0000, "António Manuel Correia"
> <manel.marias_at_netc.pt> wrote:
>
> >I found that if we're talking about INSERT performance it's useful to
> >rebuild an index once a while. I have a 70 millin rows table partition
 with
> >duplicated checking over primary key index and one a new version of that
 row
> >arrives i've to substitute the old for the new one. In spite that's in
 very
> >small percentage operation , the index grows beyond expectation. perhaps
 the
> >stoarge parameters are'nt the best ones ., but i still thing a rebuild
> >doesn't do any harm altough then the stats have to be rebuild.
> >
> >"Mike Krolewski" <mkrolewski_at_rii.com> wrote in message
> >news:92dcp5$lg9$1_at_nnrp1.deja.com...
> >> In article <3A4A1726.219EE6A7_at_netscape.net>,
> >> Cantonese Boy <cantoneseboy_at_netscape.net> wrote:
> >> > As title, if my index is always has one extent,
> >> > how can I know when there is a need to rebuild the index?
> >> > I know that there may be some "holes" inside the index that will
> >> > cause performance problem, how can I know?
> >> >
> >> > Thanks
> >> >
> >> > W
> >> >
> >>
> >> Oracle does not generally have to have indexes rebuilt. In 7.3.4, the
> >> bitmap indexes were unstable if you were doing a lot of insert and/or
> >> deletes. However, regular indexes are not likely to have such a
> >> problem. If you want you can 'analyze' the index.
> >>
> >> Within the manual:
> >>
> >> Oracle collects the following statistics for an index (statistics
> >> marked with an asterisk are always computed exactly):
> >> () Depth of the index from its root block to its leaf blocks*
> >> () Number of leaf blocks
> >> () Number of distinct index values
> >> () Average number of leaf blocks per index value
> >> () Average number of data blocks per index value (for an index on a
> >> table)
> >> () Clustering factor (how well ordered the rows are about the indexed
> >> values)
> >>
> >> Index statistics appear in the data dictionary views USER_INDEXES,
> >> ALL_INDEXES, and DBA_INDEXES.
> >>
> >> Your issue may be more a question of the performance of a query. You
> >> may want to look into the explain plan to determine if the index that
> >> you think are really being used. Often due to the exact statements and
> >> constraints within your SQL, Oracle has decided to perform the query a
> >> different way. Remember to read the sections on how the optimizer reads
> >> your SQL.
> >>
> >> Finally, check run the analyze on the table(s). It will also help
> >> Oracle make it decisions. Generally, it is good to periodically analyze
> >> tables especially if the content of the database is changing.
> >>
> >>
> >> --
> >> Michael Krolewski
> >> Rosetta Inpharmatics
> >> mkrolewski_at_rii.com
> >> Usual disclaimers
> >>
> >>
> >> Sent via Deja.com
> >> http://www.deja.com/
> >
> >
>
> EMail: knijff_at_bigfoot.com
Received on Thu Dec 28 2000 - 02:19:12 CST

Original text of this message

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