Home » RDBMS Server » Server Administration » GLOBAL Partition - 14037
GLOBAL Partition - 14037 [message #241114] Mon, 28 May 2007 06:31 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I'm creating a Global partitioned index

Here the partition column identity_code is number in DDMMYYYY format.

create unique index DEPOSITS on DEPOSITS(identity_code, id_number)
global partition by range(identity_code)
	(Partition MAR_2005 values less than (01042005) tablespace index_ts, 
	 Partition MAR_DEC_2006 values less than (01012007) tablespace index_ts,
	 Partition JAN_2007 values less than (01022007) tablespace index_ts,
	 Partition FEB_2007 values less than (01032007) tablespace index_ts,
	 Partition MAR_2007 values less than (01042007) tablespace index_ts,
	 Partition APR_2007 values less than (01052005) tablespace index_ts,
	 Partition MAY_2007 values less than (01062005) tablespace index_ts,
	 Partition MAX_PARTITION values less than (MAXVALUE) tablespace index_ts
	 )


While creating it generates error "ORA-14037" becuase partition MAR_DEC_2006 bound value "01012007" is lesser than MAR_2005 bound value 01042005.

Partition key is also can not be expression Embarassed

Is there any way, I can build this index(Without changing the ranges) ??

Brayan.
Re: GLOBAL Partition - 14037 [message #241181 is a reply to message #241114] Mon, 28 May 2007 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
When you use a wrong datatype, you surely not get what you want.
Change to the correct datatype or rest on your own.

Regards
Michel
Re: GLOBAL Partition - 14037 [message #241252 is a reply to message #241114] Tue, 29 May 2007 00:20 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

The partition column is number type in Table. From users perspective its a date in DDMMYYYY format.

It is like
31012007
28022007
31032007
...

in this case also, 28022007(represents 28-Feb-07) is lesser than 31012007 (31-Jan-07).

Because of this reason I cannot partition by range.

Is there a way????

Brayan.
Re: GLOBAL Partition - 14037 [message #241253 is a reply to message #241114] Tue, 29 May 2007 00:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way????
YYYYMMDD works better.
Re: GLOBAL Partition - 14037 [message #241255 is a reply to message #241114] Tue, 29 May 2007 00:26 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

But the data stored in the table like DDMMYYYY. I wanted to do that way..but partition key cannot have expression.

Brayan
Re: GLOBAL Partition - 14037 [message #241278 is a reply to message #241252] Tue, 29 May 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I understand your problem and there is no solution as it.
You (nothing personal, you on your site I mean) have bad design, you have to correct it.

Given this, are you sure you need to partition? What is the purpose of this partitioning?

Regards
Michel
Re: GLOBAL Partition - 14037 [message #241294 is a reply to message #241114] Tue, 29 May 2007 02:20 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

This is a standard Oracle Financial Services Application.

I have partitioned the tables according to the requirement of the application for multiprocessing. That works fine.

Also for the backup stratergy and making the history data as readonly.

As far this partitioned index, there is no specific requirement. Incase of any Alter table operation, I need to manually rebuild the index. That is the reason I'm building a global partitioned index.

Brayan.
Re: GLOBAL Partition - 14037 [message #241297 is a reply to message #241294] Tue, 29 May 2007 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why the partitioning on the table does not work for the index?

Regards
Michel
Re: GLOBAL Partition - 14037 [message #241317 is a reply to message #241114] Tue, 29 May 2007 03:58 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

I did not understand your question correctly!!

Definately a normal index will work for the partitioned table.

But in this case I'm trying to avoid the rebuild of index every time whenever there is "alter table".

Brayan
Re: GLOBAL Partition - 14037 [message #241333 is a reply to message #241317] Tue, 29 May 2007 04:56 Go to previous message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
My question was that he can't partition his index but was able to partition the table.
So I ask why he can't partition this index while he can with the table.
But maybe I misunderstood what he posted (this would not be surprising).

Regards
Michel
Previous Topic: Delete ORAARCH folder
Next Topic: upgrade from unix to Linux
Goto Forum:
  


Current Time: Mon Dec 09 19:55:56 CST 2024