Home » SQL & PL/SQL » SQL & PL/SQL » Paritioning tables- Indexes (Oracle 10g)
Paritioning tables- Indexes [message #345750] Thu, 04 September 2008 13:07 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
Hi I have a question on Paritioning tables- Indexes. My current table with its paritition is as shown below.
for companys 1 through 6 the unq key is comapany id and acct number.

Not i wish to add a new company but the uniqueness is not limited to the above constraint. Only for company 7
the key should be company_id, acct_number, settl_type. This field settl type is only relevant to company 7 and
will always be a null for the others. My question is how can i have a particular index say company_id and acct_number for companies
from 1 to 6 and a different one for company 7 (company_id , acct_number, settl_type)


drop table test_table

create table test_table
(
company_id number(5) not null,
acct_number varchar2(25 byte) not null,
settl_type number(10),
date_of_rec date
)
partition by list (company_id )
(
partition p1 values (1, 2,3),
partition p2 values (4,5,6)
)

create unique index test_table_idx1 on test_table
(company_id , acct_number)
local (
partition p1,
partition p2)


New partition i am defining for company 7- but getting ora 14024. Any idea/ help on how i can get around? thanks


drop table test_table

create table test_table
(
company_id number(5) not null,
acct_number varchar2(25 byte) not null,
settl_type number(10),
date_of_rec date
)
partition by list (company_id )
(
partition p1 values (1, 2,3),
partition p2 values (4,5,6),
partition p3 values (7)
)

create unique index test_table_idx1 on test_table
(company_id , acct_number)
local (
partition p1,
partition p2)


create unique index test_table_idx1 on test_table
(company_id , acct_number,settl_type)
local (
partition p3)
Re: Paritioning tables- Indexes [message #345759 is a reply to message #345750] Thu, 04 September 2008 13:40 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As usual you are not following OraFAQ Forum Guide.
Read it.

Regards
Michel
Previous Topic: Truncate execution
Next Topic: select sql for non-english chancter
Goto Forum:
  


Current Time: Sat Dec 10 20:18:36 CST 2016

Total time taken to generate the page: 0.19190 seconds