Home » SQL & PL/SQL » SQL & PL/SQL » Error while partitioning the Index: GLOBAL partitioned index must be prefixed??
Error while partitioning the Index: GLOBAL partitioned index must be prefixed?? [message #18556] Tue, 05 February 2002 07:29 Go to next message
CATHYBEE
Messages: 20
Registered: January 2002
Junior Member
GLOBAL PARTITIONED INDEX MUST BE PREFIXED..
WHAT DOES THAT MEAN?

my TABLE
SQL> desc tbl_claimtrack;
Name Null? Type
------------------------------- -------- ----
HOSTKEY NOT NULL TIMESTAMP(4)
CLAIMID NOT NULL RAW(32)
TRANSMITTIME NOT NULL TIMESTAMP(4)

WHERE

HOSTKEY PK/FK
CLAIMID PK/FK
TRANSMITTIME REGULAR COLUMN

MY INDEX:
CREATE UNIQUE INDEX idxAK_ClaimTrack2 ON tbl_ClaimTrack
(HostKey,ClaimID,TransmitTime)
GLOBAL PARTITION BY RANGE(TramsmitTime)
(PARTITION D0200201
VALUES LESS THAN(TO_DATE('FEB-2002','MON-YYYY')),
PARTITION D0200202
VALUES LESS THAN(TO_DATE('MAR-2002','MON-YYYY')),
PARTITION D0200212 VALUES LESS THAN(MAXVALUE));

ERROR:
gLObAL PARTITION BY RANGE(TransmitTime)
*
ERROR at line 7:
ORA-14038: GLOBAL partitioned index must be prefixed

ANY HELP IS APPRECIATED.
THANX!
Re: Error while partitioning the Index: GLOBAL partitioned index must be prefixed?? [message #18559 is a reply to message #18556] Tue, 05 February 2002 07:48 Go to previous messageGo to next message
Frank Naude
Messages: 4569
Registered: April 1998
Senior Member
Hi,

Global non-prefixed indexes are not supported by Oracle. This means that the index partitioning key must always be the leftmost index column. Anything else will raise ORA-14038. If you wish the index key to be different to the partition key i.e. a non prefixed index, then the index must be created a Local index.

In your query, the index columns are (HostKey,ClaimID,TransmitTime) and the partition key is (TramsmitTime).

Error: ORA-14038
Text: GLOBAL partitioned index must be prefixed
Cause: User attempted to create a GLOBAL non-prefixed partitioned index which is illegal.
Action: If the user, indeed, desired to create a non-prefixed index, it must be created as LOCAL; otherwise, correct the list of key and/or partitioning columns to ensure that the index is prefixed.

Hope it helps.

Frank Naude
Re: Error while partitioning the Index: GLOBAL partitioned index must be prefixed?? [message #18567 is a reply to message #18559] Tue, 05 February 2002 08:53 Go to previous messageGo to next message
CATHYBEE
Messages: 20
Registered: January 2002
Junior Member
Frank:
Can you give me an example!!

Maybe i am not understanding it correctly..
But i tried changing the order of my index columns and making TranmitTime as my leftmost index column.. But it still gives me the same error..

I TRIED:
CREATE UNIQUE INDEX idxAK_ClaimTrack2 ON tbl_ClaimTrack
(TransmitTime,HostKey,ClaimID)
GLOBAL PARTITION BY RANGE(TramsmitTime)
(PARTITION D0200201
VALUES LESS THAN(TO_DATE('FEB-2002','MON-YYYY')),
PARTITION D0200212 VALUES LESS THAN(MAXVALUE));

SAME ERROR..

I cannot use a LOCAL INDEX BECAUSE.. Oracle doesn't support partitioning in the LOCAL indexes..
Re: Error while partitioning the Index: GLOBAL partitioned index must be prefixed?? [message #18581 is a reply to message #18567] Wed, 06 February 2002 05:45 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

see the lines

(TransmitTime,HostKey,ClaimID)
GLOBAL PARTITION BY RANGE(TramsmitTime)

there is spelling mistake .
TransmitTime!=TramsmitTime

cheers
pratap
Previous Topic: error while paritioning an index?? Very Urgent!!
Next Topic: call procedure/function from web-page
Goto Forum:
  


Current Time: Sat Sep 25 01:42:04 CDT 2021