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

Must be a cultural difference. In the US, the word has a 'z', and we don't use a hyphen.

In article <3a4af778_at_news.iprimus.com.au>,   "Howard J. Rogers" <howardjr_at_www.com> wrote:
>
> "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
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Dec 28 2000 - 13:27:32 CST