The mechanism of Exadata’s storage index.

Storage Index (SI) is a unique feature of Oracle Exadata, but unfortunately Storage index has a few restriction.

Basic
Storage index (SI) is a unique feature of Oracle Exadata. The main purpose of SI is elimination of unnecessary disk I/O when a performing smart scan in order that SI was maintained in the memory of cellsrv. The concept of SI is very simple and efficient. SI maintains column’s MIN/MAX values of each 1M unit. So, if predicate value doesn’t belong to MIN/MAX boundary, then oracle can bypass that unit. It’s because all data in that unit isn’t related to that predicate value. And, we can measure the effectiveness of SI by “cell physical IO bytes saved by storage index” statistics.

Why well-ordered is important?
As mentioned in Basic section, SI maintains column’s MIN/MAX values of each 1M unit. So, well-ordered column is more benefit by SI. For example, in case of Case#1, oracle can bypass 2nd, 3rd unit. Because, the MIN values of these are greater than 3 (predicate value). But, in case of Case#2, oracle can’t bypass any unit.

The restriction of SI
In my opinion, Below are the restrictions of SI (X2 version).
(1) SI isn’t permanent.
(2) SI can maintain only 8 columns.
(3) SI turns to invalid, when DML is occurred.
(4) SI doesn’t work until cellsrv restarted, if DATA_OBJECT_IS is changed.

(1) SI isn’t permanent.
SI is stored in memory, so whenever cellsrv is shutdown, SI disappear.

(2) SI can maintain only 8 columns.
Most tables have more than 8 columns. BTW, why SI can maintain only 8 columns? Actually, I don’t know that reason. But, I learned the maintain mechanism using field test. In conclusion, more efficient SI is more likely to survive.
For example, suppose that T1 table has 12 columns (c1~c12), and then run 8 sql statements like following. “select count(*) from t1 where c1=:b1;” ~ ” select count(*) from t1 where c8=:b1;”. Then, SI was created for columns c1~c8. Since then, run sql statement “select count(*) from t1 where c9=:b1;” What will happen? Oracle compare efficiency of SI for c9 column with SI that was created for T1 table. If c9 column is more efficient, then oracle drop the most inefficient SI. So, more efficient SI is more likely to survive.

(3) SI turns to invalid, when DML is occurred.
SI can’t apply the change in real time. So, if DML is occurred then SI turns to invalid. And, after next smart scan occurs, SI will be created.

(4) If DATA_OBJECT_IS is changed, SI doesn’t work until cellsrv restarted.
for example, TRUNCATE command change the DATA_OBJECT_ID. So, after then SI doesn’t work. Actually, I don’t know, that is design-intended function.