I'm quite new to Oracle Advanced Queueing and I was just browsing the manuals (11G). In the prformance and scalabiltiy manual http://docs.oracle.com/cd/E11882_01/server.112/e11013/perform.htm#i1005693 it's stated that "With retention specified, the history table as well as the queue table can grow to be quite big". Now I'm wondering how to create a "history table". I created a queue table for testing with retention time set to infinite - but I can't figure out how to define where the retained messages are stored - in the queue table or in the history table. Any help is very much appreciated!
The history table is created when you define a multi-consumer queue - single consumer queues only have one dequeue per message, so the main queue table contains the dequeue information. Multi-consumer queues have multiple dequeues per message and so need a history table.
The history table is
_H, an index organized table (IOT) for storing dequeue history data