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

Storage Indexes with LIKE and Wildcard Predicates
What happens when your query predicates use the LIKE condition with wildcards?
SQL> select count(*) from d14.myobj_ordered
2 where object_type like 'SOU%';
COUNT(*)
----------
0
Elapsed: 00:00:00.84
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 .00
cell physical IO interconnect bytes .35
cell physical IO interconnect bytes returned by smart scan .33
In this output, we can see that when using LIKE conditions with wildcards, storage indexes are not used.
Storage Indexes and MultiColumn Predicates
We will demonstrate the impact of using multiple predicates for a single table query. First, we will show the
performance statistics when using a single predicate:
SQL> select count(*) from d14.myobj_ordered
2 where object_name between 'S' and 'T';
COUNT(*)
----------
2858901
SQL>
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 888.61
cell physical IO interconnect bytes 34.52
cell physical IO interconnect bytes returned by smart scan 34.35
Now, we will add another predicate condition:
SQL> select count(*) from d14.myobj_ordered
2 where object_name between 'S' and 'T'
3 and object_type='TABLE';
COUNT(*)
----------
186901
SQL>
SQL> @lst19-01-exastorind-mysess.sql