Exadata Storage Server and the Query Optimizer – Part 2

In the first post of this series I shared with you some basics about smart scan and gave some details about projection. The aim of this post is to cover the second basic technique: restriction. Simply put, the aim of this technique is to offload to a cell the processing of predicates found in the WHERE clause. And, as a result, to reduce the amount of data that a cell has to send back to a database instance.

To check whether offloading is used for a given execution plan, you can have a look to the predicate information section of the DBMS_XPLAN output. If offloading takes place, a restriction based on a STORAGE predicate is displayed. Here an example (in this case the restriction “N=1” is offloaded to a cell):

------------------------------------------
| Id  | Operation                 | Name |
------------------------------------------
|   0 | SELECT STATEMENT          |      |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |
------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - storage("N"=1)
       filter("N"=1)

As you can see in the previous example, it is interesting to notice that the STORAGE predicate is used along with a FILTER predicate based on the same restriction. Honestly, I don’t know why this is necessary. And, I guess, it’s not because of false positives. In fact the cell should be able to correctly apply this kind of restrictions.

It is essential to point out that offloading is only applied to predicates that in a non-Exadata system would be processed as FILTER predicates. In fact, when an ACCESS predicate has to be applied (e.g. an index look-up is performed), smart scan cannot be used.

Now that we have seen how a simple predicate is offloaded to a cell, it is interesting to ask ourselves the following questions: Which predicates are supported by a cell? What happens when predicates contain functions or expressions?

To answer this question I tested about a hundred single-row functions to find out whether all of them can be offloaded. Here is a summary of what I observed:

• Numeric functions: they can all be offloaded with a single exception, the WIDTH_BUCKET function. For example the predicate “width_bucket(n,1,10,100) = 1” is not offloaded.
• Character functions returning character values: they can all be offloaded.
• Character functions returning number values: they can all be offloaded.
• Datetime functions: the offloading of these functions is, in my opinion, not very consistent. On the one hand, only the DATE datatype seems to be supported. In fact, when a TIMESTAMP datatype is involved, offloading almost never happens. And, be careful, this is also true for simple predicates like “t = systimestamp” (note that “t” is a column of TIMESTAMP datatype). On the other hand, while predicates like “d = sysdate” (note that “d” is a column of DATE datatype) and “add_months(d,1) = to_date(’01-01-2010′,’DD-MM-YYYY’)” can be offloaded, something like “add_months(d,1) = sysdate” cannot. What I observed is that basically every datetime function can be offloaded provided that it is not used along with SYSDATE or CURRENT_DATE.
• NULL-related functions: they can all be offloaded.

According to my tests, except for datetime functions, offloading can take place most of the time. This is a good thing. However, the limitations related to datetime functions are tough and, therefore, I expect that Oracle addresses this issue very soon.

Addenda 2010-08-09: have a look to this post for further discussion of the problem related to datetime functions.