Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, HARI PRASAD.

Asked: September 06, 2018 - 5:20 am UTC

Last updated: September 06, 2018 - 5:55 am UTC

Version: 11GR2

Viewed 1000+ times

You Asked

Hi Tom,

Can you please explain me difference between dequeue options dbms_aq.remove and dbms_aq.remove_nodata using in dbms_aq.dequeue.

2. Does dbms_aq.dequeue generate more redolog log that normal delete statement.

Is it advisable to have delete on queue table using MSG_ID or dbms_aq.dequeue using MSGID.
Can you please advise me.


Thank You.

and Connor said...

1) From

https://docs.oracle.com/database/121/ADQUE/aq_intro.htm#CHDCEDDG

"When a message is dequeued using REMOVE_NODATA mode, the payload of the message is not retrieved. This mode can be useful when the user has already examined the message payload, possibly by means of a previous BROWSE dequeue."

So in effect we are dequeuing without getting the message, because we either already have it or don't care about it.

2) Not really. It is similar to a delete on a table with a few indexes (because under the covers that's how we implement the message store)

Don't every do direct DML against the queue tables, because we reserve the right to change the implementation whenever we want. One day we might store *other* stuff in the MSG_ID columns besides the message etc.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library