Simple question about nvl or-expansion

From: McPeak, Matt (Consultant) <"McPeak,>
Date: Thu, 22 Aug 2019 17:55:41 +0000
Message-ID: <BL0PR04MB4467D88E370F44AF1AD9EEB6DDA50_at_BL0PR04MB4467.namprd04.prod.outlook.com>



To the Oracle gurus that dwell here:

I recently came across a query with a predicate containing this line:

     AND t.column1 LIKE nvl(:b1,'%')

The query in question performed very poorly when :b1 was null. Changing it to

     AND t.column1 LIKE nvl(:b1,t.column1)

.. improved it immensely and I could see the plan changed to benefit from nvl or-expansion. Similar variants were all equally effective at fixing the performance, e.g.:

    AND ( t.column1 LIKE :b1 OR :b1 IS NULL )

My question is: what is the reason why Oracle's CBO was not able to use nvl or-expansion in the original version? Is it just "they didn't implement it that way"? Or is there something fundamental that makes it impossible?

Thanks,
Matt

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 22 2019 - 19:55:41 CEST

Original text of this message