SQL Optimization in Oracle

问题:

We are using Oracle 11 and I recently acquired a Dell SQL Optimizer (included with the Xpert Toad package). We had a statement this morning that was taking longer than normal to run, and after we eventually got it running (missing some conditions from when it was created) I was curious, having never used any SQL optimizer before, what it would change it to. It came back with over 150 variations of the same statement, but the one with the lowest cost simply added to the following line.

AND o.curdate > 0 + UID * 0

We already had o.curdate > 0, and the "+ UID * 0" was added. This decreased the runtime from over a minute to 3 seconds. I assume it has something to do with how Oracle translates and processes the conditions, but I was curious if any of the Oracle gurus would be able to provide some insight as to how this addition to the greater than zero check decreased the runtime by 15 times. Thanks!

回答1:

The UID * 0 is used to hide the 0 from the optimizer. The optimizer would use its statistic data to find out whether using an index scan on o.curdate > 0 makes sense. As long as the optimizer knows the value in o.curdate > value it will do so. But when the value is unknown (here because the function UID will be called on execution and somehow mathed into the value), the optimizers cannot foresee what percentage of rows may be accessed and thus choses an avarage best access method.

Example: You have a table with IDs 1 to 100. Asking for ID > 0 will result in a full table scan, whereas asking for ID > 99 will likely result in an index range scan. When asking for ID > 0 + UID * 0 suddenly makes the optimizer blind to the value, and it may chose the index plan rather then full table scan.