Exadata Storage Index Part-1

• It is not like traditional index which is in database. This is the feature of storage software and useful to eliminate IOs.
• Storage Index filter out data from the consideration. 

• It works by storing minimum and maximum column values for disk storage units, which are 1 Megabyte (MB) by default.
• Because SQL predicates are passed to the storage servers when Smart Scans are performed, the storage software can check the predicates against the Storage Index metadata (maximum and minimum values) before doing the requested I/O.

• Any storage region that cannot possibly have a matching row is skipped.

• There are no tuning technique or documents for storage index as it maintain by storage software only.

Storage Indexes consist of a minimum and a maximum value for up to eight columns. This structure is maintained for 1MB chunks of storage (storage regions). Storage Indexes are stored in memory only and are never written to disk.

Storage index is not static, whenever we shutdown or reboot storage server it will be removed and once server comes up storage server automatically create index.

Now we will see that how storage index actually work while executing the query on the DB server.

Click on image to enlarge.

Now we are executing below query on this table.

SQL> select * from TABLE where c>7;

In this case first query will go to the storage index and it will check min and max number for rows of first set but 7 is not fitting in Min and Max for rows of first set so, it will eliminate to check the data of that rows and it will move to second set of rows. 

On second set of rows it will check min and max , here it finds max is 9 so, it will go inside and read the rows and give the appropriate output for the query.

Here we can conclude that it has eliminated reading of 3 rows which are not matching with the query.

This is how it eliminate the IOs.

In next blog we will explain how it's benefiting in real time scenario with Example of real time data.