Function based Indexes

[RedInTheCorner]

I want to use function based index below, but when I tried to crate index, I got following errors;
ORA-03113 and ORA-03114,

CREATE UNIQUE INDEX Ndx_XTableonAB
ON XTable
(CASE WHEN Status = 'T' THEN A END,
CASE WHEN Status = 'T' THEN B END)
TABLESPACE NUCL_NDX
STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0)
PCTFREE 5 ONLINE;

I tried many times with different Database servers but each time I got these errors. And after that I stuck cannot drop or rebuild index Any idea?

Thanks,

Atilla

 

[jimirvine]

what errors?

 

[Dagon]

Try a DECODE instead of CASE and see if you get the same problem e.g.

DECODE(status, 'T', 'A', null)

 

[RedInTheCorner]

Errors was ORA-03113 and ORA-03114,
Many thanks, I solved the problem. Some of the rows have
same A and B values for Status 'T'
A B Status
1 2 'T'
1 2 'T'
Thats why I cannot create a unique index and oracle crashs . I update old data and re-create index.

 

[Dagon]

It's obviously a problem with Oracle, though, because it should give you a sensible error message and you shouldn't be getting disconnected from the database.