Home » RDBMS Server » Server Utilities » Unique Index
Unique Index [message #250333] |
Mon, 09 July 2007 12:40 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Unique Index [message #250334 is a reply to message #250333] |
Mon, 09 July 2007 12:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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;
|
|
|
Re: Unique Index [message #250336 is a reply to message #250335] |
Mon, 09 July 2007 12:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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?
|
|
|
Re: Unique Index [message #250339 is a reply to message #250333] |
Mon, 09 July 2007 12:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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;
|
|
|
Re: Unique Index [message #250340 is a reply to message #250339] |
Mon, 09 July 2007 12:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Unique Index [message #250351 is a reply to message #250342] |
Mon, 09 July 2007 13:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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.
|
|
|
Goto Forum:
Current Time: Tue Sep 10 07:18:31 CDT 2024
|