Advanced Queuing and Table Indexing for Performance

6f4264c5-ccf9-4655-8243-757b4720d40a

I am working in an application environment that uses  Oracle Pipes for processing of transactional data.   Recently, in order to address performance problems in our production environment we had to drop indexes on certain tables because our performance on Oracle forms dragged.  I am trying to develop a functional test plan for the migration to Advance Queuing because it is my understanding that Oracle Pipes are deprecated in Oracle 12. Given these circumstances and what AQ provides, I suspect that indexes are heavily used and that we may be able to put our indexes back in place.  Any thoughts would be welcome.

gdanby

I wouldn't expect the presence of the indexes to affect query performance as such, it's more likely to be the information available to the optimizer (i.e. statistics) which ends up in an execution plan using the indexes. Unless you mean the performance of DML statements involving tables with many indexes, which can be an overhead on highly active systems.

It sounds like you are asking more than one question, one about whether to move from pipes to AQ and anther about indexes. Can you give some more details about your main problem, e.g. are you currently on Oracle 11 ?

gdanby

One other issue to keep in mind is that pipes are not transactional. From the Oracle Doc "Caution: Pipes are independent of transactions. Be careful using pipes when transaction control can be affected.". Oracle AQ is transactional and a rollback will mean that the message is never committed to the queue.