I will explain Storage Index which is magic feature of Oracle Exadata in this article.
Before this post, If you don’t know What is the Exadata and want to learn, you can read following Exadata Tutorials.
Storage indexes, one of the unique features of Exadata, is a feature that prevents excessive physical physical I/O in the Cell Server. Storage indexes are a structure that stores up to 8 column data to avoid making unnecessary I/O on memory.
Each disk in each Cell Server has a storage space of 1MB which holds an index ( pointer, location info ) where all the data stored in the storage is written. The storage index is the objects that keep the distribution map of the data on the disk created automatically in memory for the related SQL.
Storage indexes are completely memory based, do not keep physical space, and these are lost when Cell Server is restarted. After starting the cell server, Exadata will create them according to running queries.
The Storage Index is the part of Smart Scan because it helps the Client send only the requested data in the query.
To enable storage index for any query, it must meet the following requirements.
- Queries can use Storage index that are using Smart Scan.
- Where condition like , where id > 200
- Storage index can use ” =, <, >, BETWEEN, IN, IS NULL, IS NOT NULL, >=, <= “operators.
- Storage indexes can support number,date,varchar2 data type.
- Storage indexes can not support nls data type.