Home » RDBMS Server » Server Utilities » Unique Index
Unique Index [message #250333] Mon, 09 July 2007 12:40 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
is there anybody help me out with this.I am trying to create a unique index on the partition table. I am getting this error.

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index


Thanks in advance

Report message to a moderator

Re: Unique Index [message #250334 is a reply to message #250333] Mon, 09 July 2007 12:42 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
post the querry?

Specify the column name in syntax on the basis of which you perofrm partitioning.

CREATE UNIQUE  INDEX TS_STATS_U_IDX2 ON
 TS_STATS (SERVICE,REQUEST_TIME,SERVER)
PCTFREE 20
 LOCAL
           (
            PARTITION ESPSVCS_TS_STATS_1 TABLESPACE ESPSVCS_TS_STATS_IDX1,
            PARTITION ESPSVCS_TS_STATS_2 TABLESPACE ESPSVCS_TS_STATS_IDX2,
            PARTITION ESPSVCS_TS_STATS_3 TABLESPACE ESPSVCS_TS_STATS_IDX3,
            PARTITION ESPSVCS_TS_STATS_4 TABLESPACE ESPSVCS_TS_STATS_IDX4,
            PARTITION ESPSVCS_TS_STATS_5 TABLESPACE ESPSVCS_TS_STATS_IDX5,
            PARTITION ESPSVCS_TS_STATS_6 TABLESPACE ESPSVCS_TS_STATS_IDX6,
            PARTITION ESPSVCS_TS_STATS_7 TABLESPACE ESPSVCS_TS_STATS_IDX7,
            PARTITION ESPSVCS_TS_STATS_8 TABLESPACE ESPSVCS_TS_STATS_IDX8,
            PARTITION ESPSVCS_TS_STATS_9 TABLESPACE ESPSVCS_TS_STATS_IDX9,
            PARTITION ESPSVCS_TS_STATS_10 TABLESPACE ESPSVCS_TS_STATS_IDX10
           ) ;

[Updated on: Mon, 09 July 2007 12:44]

Report message to a moderator

Re: Unique Index [message #250335 is a reply to message #250333] Mon, 09 July 2007 12:45 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
CREATE UNIQUE INDEX ORDER_ASSIGNMENT_PK_NEW ON ORDER_ASSIGNMENT_NEW
(ORDER_ASSIGNMENT_ID)
TABLESPACE LC_MONITOR_IDX5
LOCAL (
PARTITION P2006_M01
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M02
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M03
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M04
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M05
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M06
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M07
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M08
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M09
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M10
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M11
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M12
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M01
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M02
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M03
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M04
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M05
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M06
LOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_IDX5
STORAGE(MAXEXTENTS UNLIMITED)
)
NOPARALLEL;

Report message to a moderator

Re: Unique Index [message #250336 is a reply to message #250335] Mon, 09 July 2007 12:47 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
CREATE UNIQUE INDEX ORDER_ASSIGNMENT_PK_NEW ON ORDER_ASSIGNMENT_NEW
(ORDER_ASSIGNMENT_ID)
TABLESPACE LC_MONITOR_IDX5


is that the column in your table on the basis of which you did partitioning?

Report message to a moderator

Re: Unique Index [message #250339 is a reply to message #250333] Mon, 09 July 2007 12:51 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
NO that column is Process_date. i am also sending you the Create table statment.

Much MUch appreciated

CREATE TABLE ORDER_ASSIGNMENT_NEW
(
ORDER_CODE VARCHAR2(40 BYTE) NOT NULL,
EXECUTION_CODE VARCHAR2(40 BYTE) NOT NULL,
ASSIGNED_DATETIME DATE,
MODIFIED_DATETIME DATE,
ASSIGNED_QUANTITY NUMBER(20,4),
DAILY_ACTIVE_FLAG CHAR(1 BYTE),
ACTIVE_FLAG CHAR(1 BYTE),
ORDER_ASSIGNMENT_ID NUMBER NOT NULL,
SOURCE_ID NUMBER,
PROCESS_DATE DATE,
LAST_UPDATE DATE,
ORDER_ID NUMBER,
EXECUTION_ID NUMBER
)
TABLESPACE LC_MONITOR_DATA_1
PARTITION BY RANGE(PROCESS_DATE)
(PARTITION P2006_M01 VALUES LESS THAN (TO_DATE(' 2006-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M02 VALUES LESS THAN (TO_DATE(' 2006-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M03 VALUES LESS THAN (TO_DATE(' 2006-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M04 VALUES LESS THAN (TO_DATE(' 2006-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M05 VALUES LESS THAN (TO_DATE(' 2006-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M06 VALUES LESS THAN (TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M07 VALUES LESS THAN (TO_DATE(' 2006-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M08 VALUES LESS THAN (TO_DATE(' 2006-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M09 VALUES LESS THAN (TO_DATE(' 2006-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M10 VALUES LESS THAN (TO_DATE(' 2006-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M11 VALUES LESS THAN (TO_DATE(' 2006-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2006_M12 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M01 VALUES LESS THAN (TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M02 VALUES LESS THAN (TO_DATE(' 2007-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M03 VALUES LESS THAN (TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M04 VALUES LESS THAN (TO_DATE(' 2007-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M05 VALUES LESS THAN (TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED),

PARTITION P2007_M06 VALUES LESS THAN (TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE LC_MONITOR_DATA_1
STORAGE(MAXEXTENTS UNLIMITED)
)
NOCACHE
NOPARALLEL
NOMONITORING;

Report message to a moderator

Re: Unique Index [message #250340 is a reply to message #250339] Mon, 09 July 2007 12:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
check this
  1  CREATE UNIQUE  INDEX TS_STATS_U_IDX1 ON
  2   TS_STATS (TS_TRANSACTION,SERVER)
  3   PCTFREE 20
  4   LOCAL
  5             (
  6              PARTITION ESPSVCS_TS_STATS_1 TABLESPACE ESPSVCS_TS_STATS_IDX1,
  7              PARTITION ESPSVCS_TS_STATS_2 TABLESPACE ESPSVCS_TS_STATS_IDX2,
  8              PARTITION ESPSVCS_TS_STATS_3 TABLESPACE ESPSVCS_TS_STATS_IDX3,
  9              PARTITION ESPSVCS_TS_STATS_4 TABLESPACE ESPSVCS_TS_STATS_IDX4,
 10              PARTITION ESPSVCS_TS_STATS_5 TABLESPACE ESPSVCS_TS_STATS_IDX5,
 11              PARTITION ESPSVCS_TS_STATS_6 TABLESPACE ESPSVCS_TS_STATS_IDX6,
 12              PARTITION ESPSVCS_TS_STATS_7 TABLESPACE ESPSVCS_TS_STATS_IDX7,
 13              PARTITION ESPSVCS_TS_STATS_8 TABLESPACE ESPSVCS_TS_STATS_IDX8,
 14              PARTITION ESPSVCS_TS_STATS_9 TABLESPACE ESPSVCS_TS_STATS_IDX9,
 15              PARTITION ESPSVCS_TS_STATS_10 TABLESPACE ESPSVCS_TS_STATS_IDX10
 16*            )
SQL> /
 TS_STATS (TS_TRANSACTION,SERVER)
 *
ERROR at line 2:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index


because mine partition key is REQUEST_TIME THATS y it thwros error now

)
Partition by hash ( request_time)
 (
Partition ts_stats_p1  Tablespace ESPSVCS_TS_STATS_1,



 1  CREATE UNIQUE  INDEX TS_STATS_U_IDX1 ON
  2   TS_STATS (TS_TRANSACTION,REQUEST_TIME,SERVER)
  3   PCTFREE 20
  4   LOCAL
  5             (
  6              PARTITION ESPSVCS_TS_STATS_1 TABLESPACE ESPSVCS_TS_STATS_IDX1,
  7              PARTITION ESPSVCS_TS_STATS_2 TABLESPACE ESPSVCS_TS_STATS_IDX2,
  8              PARTITION ESPSVCS_TS_STATS_3 TABLESPACE ESPSVCS_TS_STATS_IDX3,
  9              PARTITION ESPSVCS_TS_STATS_4 TABLESPACE ESPSVCS_TS_STATS_IDX4,
 10              PARTITION ESPSVCS_TS_STATS_5 TABLESPACE ESPSVCS_TS_STATS_IDX5,
 11              PARTITION ESPSVCS_TS_STATS_6 TABLESPACE ESPSVCS_TS_STATS_IDX6,
 12              PARTITION ESPSVCS_TS_STATS_7 TABLESPACE ESPSVCS_TS_STATS_IDX7,
 13              PARTITION ESPSVCS_TS_STATS_8 TABLESPACE ESPSVCS_TS_STATS_IDX8,
 14              PARTITION ESPSVCS_TS_STATS_9 TABLESPACE ESPSVCS_TS_STATS_IDX9,
 15              PARTITION ESPSVCS_TS_STATS_10 TABLESPACE ESPSVCS_TS_STATS_IDX10
 16*            )
 17  /

Index created.



[Updated on: Mon, 09 July 2007 12:54]

Report message to a moderator

Re: Unique Index [message #250342 is a reply to message #250340] Mon, 09 July 2007 12:59 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
SO i have to create the unique index on the column too which i am using for my partitioning the table. Is that right?

Then i have another question. I also need to create the unique key, do i need to include also the partitioned column.


THanks alot

Report message to a moderator

Re: Unique Index [message #250351 is a reply to message #250342] Mon, 09 July 2007 13:59 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
SO i have to create the unique index on the column too which i am using for my partitioning the table. Is that right?


Yes

Quote:
Then i have another question. I also need to create the unique key, do i need to include also the partitioned column.


I guess Not.

Report message to a moderator

Previous Topic: Do we need to have a DBA privilege inorder to use IMP/EXP
Next Topic: Export tables and data from 8192(Block Size) to 4096(Block Size) database
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 22 22:36:55 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.