Storage Indexes - Oracle Exadata Recipes: A Problem-Solution Approach - page 549

Now, we will run tests searching for conditions when our OBJECT_NAME column is null:
SQL> select count(*) from d14.myobj_ordered
2 where object_name is null;
COUNT(*)
----------
99
Elapsed: 00:00:00.06
SQL> @lst19-01-exastorind-mysess.sql
Statistic Value (MB)
-------------------------------------------------------------------- -----------------------
cell physical IO bytes eligible for predicate offload 2,254.73
cell physical IO bytes saved by storage index 2,252.82
cell physical IO interconnect bytes .03
cell physical IO interconnect bytes returned by smart scan .00
Not only does Exadata use storage indexes for our first query, but it also benefits significantly when querying our
99 rows with null values. This means that cellsrv handles null values and can eliminate I/O requests successfully in
situations when null values exist in the predicate column as well as eliminate I/O requests when the IS NULL
clause is used.
Storage Indexes and Unbounded Range Scans
In the first section of this recipe, we demonstrated how storage indexes can be used when supplying a range criteria
with the BETWEEN predicate. What happens when you execute a query with an unbounded range criterion?
SQL> select count(*) from d14.myobj_ordered
2 where object_name > 'T';
COUNT(*)
----------
4247000
Elapsed: 00:00:00.47
SQL> @lst19-01-exastorind-mysess.sql
Statistic Value (MB)
-------------------------------------------------------------------- -----------------------
cell physical IO bytes eligible for predicate offload 2,254.73
cell physical IO bytes saved by storage index 1,375.70
cell physical IO interconnect bytes 50.98
cell physical IO interconnect bytes returned by smart scan 50.95
As you can see, storage indexes are used with unbounded range criteria and the I/O savings is proportional to the
number of rows selected in this case, in which our data is well ordered with respect to the query predicate.