Toad World® Forums

Optimizer Use of Oracle UID Function


#1

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:

ORIGINAL
AND dv.setid = ‘USA01’

OPTIMIZED
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.

  1. 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?

  2. 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.

  3. 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.

–Mitch

Optimized Explain Plan.txt (7.21 KB)

Original Explain Plan.txt (7.51 KB)


#2

Hi,

  1. I can’t tell what’s exactly happened in Oracle optimizer, as the changes also related to your database environment such as column index and table statistics. But in generally, the function NVL which causes effect at the cost estimation of this condition, and finally make change to reorder the HASH JOIN RIGHT OUTER between REF_DISCIPLINE and DIM_VENDOR.

  2. The NVL seems fine in here as the constant can’t be null at all. Within the engine, we have some criteria checking before applying it to SQL. Take NVL as an example, this rule will not applied to any null able expression, such as function or null able table column. About your quest at column definition changed from NOT NULL to NULL, the impact should be considered before the alter table done, and it causes no difference on SQL turning between human and our optimizer. By the ways, if user really concern about it, they may choice the second best alternative, it also the reason we provide number of rewritten SQL. User may select the best fit SQL with their need.

  3. At the current version, we didn’t have this requirement from user yet. Basically, we would like to keep the optimizer automatic as simple and easy for every one. And user may find the best alternative from actual testing to see the real performance. We prefer to let user choice the best fit alternative with themselves rather than limit the number of rewritten SQL product.

The purpose of SQL turning is to improve the performance. Our first consideration is rewritten SQL with same meaning and same result return (in some case the result is same but in difference return order).

Thanks,

Tony Ng


#3

I would like to add some comments on some points:

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.

Comment:
Yes, In this case the cost is dropped and the SQL is running faster, but it does not mean cost drop will always result into performance improvement, we still need test run to verify the improvement, in some cases, higher cost may be better, since the Oracle cost estimation may not be correct especially for complex SQL.

  1. 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?

Comment:
Since Oracle optimizer will try to peek “AND dv.setid = ‘USA01’” during SQL Optimization, if Oracle make mistake in cost estimation with the peeked value ‘USA01’, after we changed the NVL(‘USA01’,UID) to the SQL, it will hide the value of ‘USA01’ during SQL optimization, so, Oracle will use the normal assumption to do the cost estimation, it may result into a different cost estimation and select different plan to execute, sometimes it can help to find a better execution plan in certain environment.

  1. 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.

Comment:
The column in DIM_VENDOR table is set to NOT NULL or not does not affect the meaning of this ”AND dv.setid = ‘USA01’” condition, as long ‘USA01’ is not null, the transformed condition “ AND dv.setid = NVL (‘USA01’, UID)” will be still valid, if your SQL is dynamic constructed during runtime, please make sure there is no NULL valid on the right hand side like this “AND dv.setid =NULL”, since “AND dv.setid = NVL (NULL, UID)” will result into this condition “AND dv.setid =UID”, so the result may be incorrect.

Thanks,

Richard