Home » SQL & PL/SQL » SQL & PL/SQL » Partition Question (10g)
Partition Question [message #356421] Thu, 30 October 2008 12:38 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Just want to ask for this case of a partitioned table. We have a partitioned table "T". It has a global index on column "ID", and a local partitioned index which composed of columns "DATE", "PRODUCTCODE" etc. Now sometimes we truncate previous partitions to reprocess it, and the global index goes invalid. Now what is the difference between using the "update global index" option instead of recreating the global index into a local partitioned index? I mean what will be the effect of those two options? will the changing to local partitioned index be the better option? Thanks.
Re: Partition Question [message #356430 is a reply to message #356421] Thu, 30 October 2008 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first question is "Can you create a local index with your current one?".

Regards
Michel
Re: Partition Question [message #356444 is a reply to message #356430] Thu, 30 October 2008 13:45 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks Michel. What do you mean on your question? Like if we have rights to create the local index? Or not that?
Re: Partition Question [message #356446 is a reply to message #356444] Thu, 30 October 2008 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean what is in the doc:
Quote:
If the index is local nonprefixed (see local_partitioned_index ), then the index key must contain the partitioning key.


Regards
Michel
Re: Partition Question [message #356454 is a reply to message #356446] Thu, 30 October 2008 14:46 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks. I've check on the local nonprefixed. You mean if we could create that local index with the partitioning column and the "ID" column? Yes we can I think. But what would be most beneficial, is it the using of "update global..." or just recreating the index as local? Ill continue reading also.

I forgot to say that the ID column is the primary key, it was not partitioned.


update again:

I think I got it now Michelle, do you mean I'll receive an error by defining a unique local index without including the partitioning column. Base on what I tried it did =( Thanks again, you really have a great way of leading and making people think about the solution on their own, and that's a much better way of learning =)I'll post again for the next questions. Will continue on the effects of recreating and the "update global"

[Updated on: Thu, 30 October 2008 15:41]

Report message to a moderator

Previous Topic: get ORA-04098 when inserting nextval into sequence nbr using trigger
Next Topic: Procedure Calling another procedure
Goto Forum:
  


Current Time: Sun Dec 04 20:52:22 CST 2016

Total time taken to generate the page: 0.08383 seconds