Re: partitions

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 21 Nov 2004 14:00:06 +1100
Message-ID: <41a004b6$0$1959$afc38c87@news.optusnet.com.au>

DA Morgan wrote:
[snip]

>> No it won't. Read the question again. Will partitioning a global index 
>> make 'update global indexes' any faster/easier/better than 'update 
>> global indexes' on a glob non-partitioned index? Answer: Absolutely 
>> NOT, because in either case the entire index must be subject to 
>> maintenance.
>>
>> Regards
>> HJR

>
>
> Well here we are all making assumptions again ...

No we're not. We seem instead to be discussing a piece of 9i Release 2 syntax that you clearly haven't quite managed to get clued up about yet.

> but I have yet to find
> the syntax 'update global index' in any of my Oracle books.

Well, now's the time to stop digging that hole then.

> So my
> assumption was that it was the table that was being updated.

The thread has consistently and obviously about "UGI" -ie, that clause of a table DDL statement which reads "update global indexES". It's in the manuals. Check it out.

> And I'd
> rather affect an indexes partition rather than the entire index.

Daniel: you self-confessedly haven't a clue what this thread has been about or the specific technology that is being discussed. That your misunderstanding/ignorance has led you to give one piece of completely erroneous advice is fine. That's what learning from mistakes is all about. But to repeat your wildly inaccurate advice when the matter has been pointed out to you is just silly.

Update Global Indexes is an addition to a DDL statement affecting partitioned tables. It causes Oracle to maintain a global index, rather than mark it as unusable. The DDL statement therefore has to update the *entire* Global index, not just bits of it. And partitioning the index therefore makes no difference to the amount of work the "update global indexes" clause has to perform.

It is a 9i Release 2 new feature, and might I suggest that you go read up about it. And you will find it discussed, amongst other places no doubt, in my 'New Features in 9i Release 2' book which you can obtain for a modest sum here: http://www.oreilly.com/catalog/oracle9ipdf

HJR Received on Sat Nov 20 2004 - 21:00:06 CST