I just ran a query through the SQL Optimizer (version 7.5.0, with both intelligence levels set to 2) and it altered a filter in the query as follows:
AND dv.setid = ‘USA01’
AND dv.setid = NVL (‘USA01’, UID)
“NOTE: UID is a pseudo-column returning a numeric value identifying the current user.”
This resulted in my query explain plan cost dropping from 141,888 to 14,972, which is great, but I have a few questions about this. The original and optimized explain plans are attached.
The column in question is set to NOT NULL on the table, so why does adding this result in lowering the explain plan cost and making the query run faster?
Isn’t there a danger in the Optimizer suggesting this as an improvement? After all, if someone came along later and altered the column from NOT NULL to NULL, using the UID function could produce some really strange query results.
Is there a way to tell the Optimizer not to do this?
Thanks in advance for any assistance, and if additional details are needed, please don’t hesitate to ask.
Optimized Explain Plan.txt (7.21 KB)
Original Explain Plan.txt (7.51 KB)