What is a Storage Index?
To answer that, let's first take a look at a database. The purpose of a database is to store information in columns called fields and rows called records.
For example, let's say you're looking for all blue shoe purchases. Here's how you would do it.
First at the compute layer …
You run a query asking for all the transactions done between January-March where someone has bought blue shoes.
A signal is sent by that query at the compute layer own to what is called the storage layer. This is where all your blocks of data from your database are stored.
When you've run a query asking for blue shoes but your storage says to the compute guys that "I cannot filter through my blocks of storage to give you just blue shoe purchases.”
So your storage reads and sends up all the blocks of data for all shoes. This causes I/O bottlenecks in your storage and network because too many blocks of data are being pushed up.
And now, you sit there waiting for results.
There is a better way to get faster results and eliminate performance bottlenecks with Oracle Exadata’s storage indexes.
Oracle Exadata’s smart storage can actually figure out which blocks of storage will definitely not contain blue shoes.
How does this work?
The idea of storage indexes is that each region of storage holds information called key statistics, which describe the data in each region. In this example, the descriptive information, or key statistics, are about colored and no colored shoes. The storage index tells that the key statistics about this region reveal colored shoes. We now have to read that region and process it to find blue shoes. This allows us to skip irrelevant regions where the Exadata storage indexes tell us the value cannot be there.
The storage index uses the key statistics of this region to tell us there are no colored shoes, so we don't have to ever read that region or process it.
Exadata’s smart storage indexes dramatically increase performance and accelerate the speed of a query
This is because we automatically ignore data blocks in regions where there are no colored shoes. And this eliminates all the I/O and CPU needed to search that region. It's like having fine-grained partitioning, but on multiple columns instead.
You get the descriptive information, or key statistics, about shoes across many columns. For example, you can get the type of shoe, the size of a shoe, and the brand of a shoe - all at once and automatically.
And the best part?
While most storage indexes incur an overhead expense, particularly on updates which get borne by the database server. Exadata storage indexes have a near-zero cost! As a result, the CPU and memory cost of maintaining a storage index is very very small and it is offloaded to the storage servers.
To learn more, speak to an Oracle Exadata Sales Specialist today.