This question was asked to my friend in interview, please excuse me for such dumb question,
By any way, (even by modifying the table definition) can we have unique index on duplicated column??? in below case b1 is the column where there are duplicate rows, but I was asked if I can create a unique index?
Answer is simple NO. but I heard from interviewer that there is a way to do it via list partitioning I guess. But to my knowledge that is not possible, but always don't want to ignore / take comments loose, so checking if it really possible.
CREATE TABLE nsk_list
(
a1 NUMBER (5),
b1 VARCHAR2 (10)
)
PARTITION BY LIST
(b1)
(
PARTITION b1_ab VALUES ('A', 'B'),
PARTITION b1_cde VALUES ('C', 'D', 'E'),
PARTITION b1_fg VALUES ('F', 'G'),
PARTITION B1_OTHER VALUES (DEFAULT));
TRUNCATE TABLE NSK_LIST;
INSERT INTO NSK_LIST
VALUES (10, 'A');
INSERT INTO NSK_LIST
VALUES (11, 'G');
INSERT INTO NSK_LIST
VALUES (12, 'I');
INSERT INTO NSK_LIST
VALUES (13, 'A');
COMMIT;