I have a table in which messages are constantly appearing...
Table messages: (message_id,item_id, time_stamp, field1, field2, ...) unique PK on message_id. There can be multiple messages for a given item_id.
I periodically check this table, to build up an item_list table:
Table item_list: (item_id,field1,field2,...) unique PK on item_id
The way I do this (or try to) is with a SQL statement sometheng like this:
INSERT /*+ ignore_row_on_dupkey_index(item_list(item_id)) */ INTO item_list
(item_id,field1,field2)
SELECT item_id,field1,field2
FROM messages
WHERE time_stamp > :time_stamp_of_last_time_I_checked
The idea being that only new items end up in item_list. It's possible for the same item_id to appear more than once in a batch of messages from
the messages table, so approaches like using MERGE or a SELECT ... WHERE NOT EXISTS ... won't do the job for me. The ignore_row_on_dupkey_index hint, however, does exactly what I want it to do.
Or at least it should do, but having had this set-up running for a while I find I have multiple rows in item_list with the same item_id - despite there being a unique index on that column. It's not supposed to be possible, but it's happening on my database - causing all sorts of problems elsewhere. I've tried creating a test case to reproduce this problem, but the cases I've produced work perfectly - potential rows that would create duplicate keys are discarded just like they should be.
Has anybody else experienced this (mis)behaviour? Any clues as to what causes it?
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production