Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Creating Local partitioned index

YasuMay 26 2009 — edited May 27 2009
Hi All,

Planing to perform Range-Partitioned on a table and creating Local-Partitioned Indexes, but as table is having composite primary key constraint thought to pre-create Composite Unique Local Partitioned index.

Problem is while creating index its giving ORA-14039 error, obviously it not possible as partioning column is not a subset of key columns of a UNIQUE index.

Is there any workaround to create Local partitioned index...else i will be forced to create global part index????

Is there any disadvantages if i create Composite Unique Local Partitioned index by including partitioning columns purposely???

Will be there any impact if i create Composite Unique Local Partitioned index by including partitioning columns purposely on SQL QUERIES accessing this table???

Please suggest me a workaround.
CREATE TABLE YASIR.CS_RESEARCH (ORDER_NBR NUMBER NOT NULL,
				  ORDER_DETAIL_NBR NUMBER NOT NULL,
				  SESSION_ID VARCHAR2(40),
				  PRODUCT_GROUP VARCHAR2(16) NOT NULL,
				  PRODUCT_SUBGROUP VARCHAR2(16) DEFAULT 'N/A',
				  FORMAT_TYPE VARCHAR2(10) NOT NULL,
				  CONTRIBUTOR VARCHAR2(60) NOT NULL,
				  DOCUMENT_NUMBER VARCHAR2(15) NOT NULL,
				  DOCUMENT_DATE DATE,
				  BILLABLE_PAGES NUMBER,
				  NON_BILLABLE_PAGES VARCHAR2(512),
				  PURCHASED_UNITS VARCHAR2(2000),
				  DELIVERY_METHOD VARCHAR2(6) DEFAULT 'ONLINE',
				  COMPANY_NAME VARCHAR2(120),
				  STATUS VARCHAR2(10) DEFAULT 'NEW',
				  CONTENT_CODE VARCHAR2(15) NOT NULL, 
				  CONTENT_CODE_DESC VARCHAR2(60),
				  ITEM_PRICE NUMBER NOT NULL,
				  PAGES_ORDERED NUMBER,
				  LAST_MNT_DATE_TIME DATE NOT NULL,
				  LAST_MNT_OPID VARCHAR2(120) NOT NULL,
				  CREATE_DATE DATE NOT NULL,
				  DML_FLAG CHAR(1) NOT NULL)
	PARTITION BY RANGE (CREATE_DATE) 
	(PARTITION CS_RESEARCH_2009_Q2 VALUES LESS THAN (MAXVALUE));

###Split partitions:
ALTER TABLE YASIR.cs_research
  SPLIT PARTITION CS_RESEARCH_2009_Q2 AT (TO_DATE('30-SEP-2007 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION CS_RESEARCH_2007_Q3,
        PARTITION CS_RESEARCH_2009_Q2);

ALTER TABLE YASIR.cs_research
  SPLIT PARTITION CS_RESEARCH_2009_Q2 AT (TO_DATE('31-DEC-2007 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION CS_RESEARCH_2007_Q4,
        PARTITION CS_RESEARCH_2009_Q2);

ALTER TABLE YASIR.cs_research
  SPLIT PARTITION CS_RESEARCH_2009_Q2 AT (TO_DATE('31-MAR-2008 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION CS_RESEARCH_2008_Q1,
        PARTITION CS_RESEARCH_2009_Q2);

ALTER TABLE YASIR.cs_research
  SPLIT PARTITION CS_RESEARCH_2009_Q2 AT (TO_DATE('30-JUN-2008 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION CS_RESEARCH_2008_Q2,
        PARTITION CS_RESEARCH_2009_Q2);

ALTER TABLE YASIR.cs_research
  SPLIT PARTITION CS_RESEARCH_2009_Q2 AT (TO_DATE('30-SEP-2008 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION CS_RESEARCH_2008_Q3,
        PARTITION CS_RESEARCH_2009_Q2);

ALTER TABLE YASIR.cs_research
  SPLIT PARTITION CS_RESEARCH_2009_Q2 AT (TO_DATE('31-DEC-2008 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION CS_RESEARCH_2008_Q4,
        PARTITION CS_RESEARCH_2009_Q2);

ALTER TABLE YASIR.cs_research
  SPLIT PARTITION CS_RESEARCH_2009_Q2 AT (TO_DATE('31-MAR-2009 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION CS_RESEARCH_2009_Q1,
        PARTITION CS_RESEARCH_2009_Q2);

###Create indexes on cs_research except primary key index CSR_PKEY:
alter session set sort_area_size=262144000; -----(250MB Total will be 1GB for 4 parallel process)
create index YASIR.CSR_DETAIL_NBR on YASIR.CS_RESEARCH(TO_NUMBER(REPLACE(TO_CHAR(ORDER_NBR,'999999999')||TO_CHAR(ORDER_DETAIL_NBR,'999999999'),' ',''),'999999999999')) LOCAL
		(PARTITION CS_RESEARCH_2007_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2007_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q2 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q2 TABLESPACE USERS)
	nologging parallel (degree 4);
Index created.

create index YASIR.CSR_SESSION on YASIR.CS_RESEARCH(SESSION_ID,ORDER_NBR) LOCAL
		(PARTITION CS_RESEARCH_2007_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2007_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q2 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q2 TABLESPACE USERS)
	nologging parallel (degree 4);
Index created.

create index YASIR.CSR_CREATED on YASIR.CS_RESEARCH(CREATE_DATE) LOCAL
		(PARTITION CS_RESEARCH_2007_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2007_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q2 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q2 TABLESPACE USERS)
	nologging parallel (degree 4); 
Index created.

create index YASIR.CSR_DOCUMENT_NUMBER on YASIR.CS_RESEARCH(DOCUMENT_NUMBER) LOCAL
		(PARTITION CS_RESEARCH_2007_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2007_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q2 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q2 TABLESPACE USERS)
	nologging parallel (degree 4); 
Index created.

create index YASIR.CSR_STATUS on YASIR.CS_RESEARCH(STATUS) LOCAL
		(PARTITION CS_RESEARCH_2007_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2007_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q2 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q2 TABLESPACE USERS)
	nologging parallel (degree 4);
Index created.
 
create unique index CSR_PRKEY_IND on YASIR.CS_RESEARCH(ORDER_NBR,ORDER_DETAIL_NBR) LOCAL
	(PARTITION CS_RESEARCH_2007_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2007_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q2 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q3 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2008_Q4 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q1 TABLESPACE USERS,
		PARTITION CS_RESEARCH_2009_Q2 TABLESPACE USERS)
	nologging parallel (degree 4);
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index
Thanks,
Yasser
This post has been answered by Randolf Geist on May 27 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 24 2009
Added on May 26 2009
#general-database-discussions
25 comments
2,688 views