1

After searching for a solution on the web for a slow query. I notice that converting the query to a dynamic query would make it execute faster. It seems to do with the wrong execution plan being used when different parameters are passed.

This is slow

SELECT ProductID, ProductName FROM TransactionHistory
WHERE (ProductID = pProductId OR pProductId IS NULL)
AND (ReferenceOrderID = pOrderId OR pOrderId IS NULL)
AND (TransactionType = pTransactionType OR pTransactionType IS NULL)

This is fast (please ignore the bad query and injection)

vSql := 'SELECT ProductID, ProductName FROM TransactionHistory WHERE 1=1 '

IF pProductId IS NOT NULL THEN
    vSql := vSql || ' AND ProductID = ' || pProductId
END IF;

IF pOrderId IS NOT NULL THEN
    vSql := vSql || ' AND ReferenceOrderID = ' || pOrderId
END IF;

IF pTransactionType IS NOT NULL THEN
    vSql := vSql || ' AND TransactionType = ' || pTransactionType
END IF;

OPEN pCursor FOR vSql;

I'm currently working on 10g but will be moving to 11g soon. With 11g, is there a way to have the query run fast without the dynamic query?

Community
  • 1
  • 1
the_lotus
  • 12,668
  • 3
  • 36
  • 53
  • Are you talking about bind variable peeking creating a suboptimal plan if the first values used are not representative? ([As in this story](http://tkyte.blogspot.co.uk/2012/05/another-debugging-story.html)). And you want a hard parse with a unique query instead, which will affect the SQL statement cache? If so then 11g's adaptive cursor sharing may help, perhaps? – Alex Poole Feb 02 '15 at 20:52
  • @AlexPoole still doing some research about this. You might be giving me a good path here. – the_lotus Feb 03 '15 at 17:09
  • @the_lotus The answers in the linked question were partially wrong. It is possible to use a static SQL statement for this kind of logic and have it perform well. See my new answer in that question. TLDR; use `NVL` and the optimizer will add a `FILTER` operation and choose a different execution plan at run time. – Jon Heller Feb 03 '15 at 20:19

0 Answers0