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

and, randomly, using the ORDER BY DBMS_RANDOM.VALUE clause, for the second table. We will then issue the same
query against each table using a query predicate on the OBJECT_NAME table. First, we'll create our test tables:
SQL> create table d14.myobj_ordered
2 tablespace tbs_test nologging
3 as select * from d14.myobj_uncomp
4 order by object_name
5 /
Table created.
SQL> create table d14.myobj_unordered
2 tablespace tbs_test nologging
3 as select * from d14.myobj_uncomp
4 order by dbms_random.value;
Table created.
SQL>
Next, we will run a full table scan that uses Smart Scan against both of our tables, searching for the number of
rows that match a range of OBJECT_NAME values and measure the storage index savings using the script in Listing 19-1,
introduced in Recipe 19-1:
SQL> select count(*) from d14.myobj_ordered
2 where object_name between 'a' and 'b';
COUNT(*)
----------
3000
Elapsed: 00:00:00.09
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,242.82
cell physical IO interconnect bytes .06
cell physical IO interconnect bytes returned by smart scan .04
SQL>
SQL> select count(*) from d14.myobj_unordered
2 where object_name between 'a' and 'b';
COUNT(*)
----------
3000
Elapsed: 00:00:00.80
SQL> SQL> @lst19-01-exastorind-mysess.sql
Statistic Value (MB)
-------------------------------------------------------------------- -----------------------
cell physical IO bytes eligible for predicate offload 2,254.38
cell physical IO bytes saved by storage index .00
cell physical IO interconnect bytes .77
cell physical IO interconnect bytes returned by smart scan .74
SQL>