Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!c03.atl99.usenetserver.com!chi1.usenetserver.com!news.usenetserver.com!newsfeed-east.nntpserver.com!nntpserver.com!news1.optus.net.au!optus!newsfeeder.syd.optusnet.com.au!news.optusnet.com.au!not-for-mail Message-Id: <41932553$0$25121$afc38c87@news.optusnet.com.au> From: "Howard J. Rogers" Subject: Re: update global indexes Newsgroups: comp.databases.oracle.server Date: Thu, 11 Nov 2004 19:40:30 +1100 References: <4191d853$0$25121$afc38c87@news.optusnet.com.au> <4vnkd.491053$mD.466855@attbi_s02> Organization: Dizwell Informatics User-Agent: KNode/0.7.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit Lines: 47 NNTP-Posting-Host: 203.164.4.231 X-Trace: 1100162389 25121 203.164.4.231 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:229093 Oradba Linux wrote: > Howard J. Rogers wrote: >> Oradba Linux wrote: >> >> >>>Looking for opinions about using this option . >>>How much better is this than rebuilding indexes after a partition is >>>dropped? >>>Version 9iR2 . >> >> >> That's like asking whether apples are better than oranges. >> >> Update global indexes provides you with a way to keep a global index >> usable despite partition DDL, at the cost of DDL statements no longer >> being quick data dictionary operations. You have to evaluate whether that >> loss is worth the gain of permanently usable global indexes. >> >> A rebuild takes exclusive locks. Update Global Indexes doesn't. Rebuilds >> could be used to compact or move an index. UGI can't. A rebuild can let >> you change PCTFREE or INITRANS. UGI can't. >> >> For ordinary, every day use, UGI wins hands down I think. But for a >> thorough re-organisation, it doesn't even get close to a rebuild. It's a >> choice to make. You can't say A is better than B. They're just different. >> >> Regards >> HJR > I was trying to decide whether to use UGI or rebuild global indexes > after the partition is dropped as part of purge process. Yeah. I know. That's what I answered. > I was not talking about rebuilding an index whenever needed. I didn't think you were. Partition DDL *requires* you EITHER to rebuild an index or to UGI. You *have* to do one or the other. I know that. And I knew you knew that. And as I said, whether you do one or the other depends. There's no right or wrong answer. And one is not better or worse than the other. HJR > Thanks for the response and I got the answer.