Oracle Storage Index

If you are on Exadata or taking advantage of Database In-Memory it’s possible your queries will benefit for the automatically created and maintained Storage Indexes.

What are Storage Indexes?

Let me start by describing what Storage Indexes are in relation to Database In-Memory but remember they behavior in exactly the same way on the Exadata storage cell.

A Storage Index keeps track of minimum and maximum values for each column in an In-Memory Compression Unit (IMCU) or 1MB chunk on the Exadata storage cells. When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column(s) is examined to determine if any entries with the specified value exist.

Each IMCU is evaluated in turn by comparing the specified value(s) to the minimum and maximum values maintained in the Storage Index. If the column value is outside the MIN/MAX range for an IMCU, the scan of that IMCU is avoided. Let’s look at an example where we are looking for the total amount of sales we had in our store where the STORE_ID is 8.

The SALES table is made up of 4 IMCUs. Therefore the storage index will consist of 4 MIN/MAX ranges, one for each IMCU. The value 8 is outside the MIN/MAX range for both IMCU A and IMCU B above. So, we can avoid reading those IMCUs altogether for this query.

The value 8 is the MIN value for IMCU C, so we will definitely have to read that IMCU, as we know for sure there is at least one entry in that IMCU where the store_id is 8.

But what about IMCU D?

The value 8 does fall inside the MIN/MAX range for IMCU D and if we were scanning the data on the Exadata storage cells prior to 12.2.1.1.0, we would have to scan this section of data. But with Database In-Memory an additional level of data pruning is possible for equality, in-list, and some range predicates.

The additional level of data pruning is possible via the metadata dictionary created for each IMCU when dictionary-based compression is used. The metadata dictionary contains a list of the distinct values for each column within that IMCU. Thus dictionary-based pruning allows Oracle Database to determine if the value being searched for actually exists within an IMCU, ensuring only the necessary IMCUs are scanned.

Starting with Exadata 12.2.1.1.0, the storage cells can also do an additional level of pruning thanks to Storage Index Set Membership. For columns with a low cardinality (less than 400 distinct values according to the optimizer statistics) a new bloom filter can be created for the distinct values in each 1MB chunk. So, when SmartScan comes across a 1MB chunk where the value falls within the MIX/MAX range, it checks the specified value against the bloom filter and only reads the entire 1MB chunk if it finds a match.

How do I know if Storage Index pruning has occurred or not?

The easiest way to confirm if Storage Index pruning occurred is to check this system or session-level metrics or statistics. Typically I query the V$MYSTAT view. This view includes metrics for the current session including the following metrics that show if the current session benefited from Storage Index pruning:

Cell physical IO bytes saved by storage index – This metric shows how many bytes of I/O were eliminated by the application of storage indexes at the storage cell level.

IM scan segments minmax eligible – This metric shows the number of IMCUs that are eligible for Storage Index pruning.

IM scan CUs pruned – This metric shows the number of IMCUs that were eliminated by Storage Index pruning.

Let’s look at another example to see when we benefit from Storage Index pruning and when we don’t’.

We’ll begin by creating a table called BIG_TABLE based off of the dictionary table ALL_OBJECTS and populate it in the In-Memory column store.

CREATE TABLE big_table AS SELECT * FROM all_objects WHERE rownum<10000001;
 
TABLE created.
 
ALTER TABLE big_table inmemory;
 
TABLE Altered.
 
SELECT /*+ full(b) */ COUNT(*) FROM big_table b;
 
COUNT(*)
----------
10000000
 
SELECT segment_name, populate_status, bytes_not_populated FROM v$im_segments;
 
SEGMENT_NAME             POPULATE_STAT  BYTES_NOT_POPULATED
------------------------ -------------- -------------------
BIG_TABLE                   COMPLETED      0

Now’s that our BIG_TABLE is fully populated in the In-Memory column store, let’s find the maximum OBJECT_ID for the objects owned by SCOTT. The value SCOTT occurs less than 1% of the time in our BIG_TABLE, so we should see some Storage Index pruning.

SELECT COUNT(*) FROM big_table WHERE owner='SCOTT';
 
COUNT(*)
----------
540

Before and after we issue the query let’s check the appropriate metrics in V$MYSTAT view.

SELECT t1.name, t2.value
FROM   v$sysstat t1, v$mystat t2
WHERE  t1.name LIKE 'IM%'
AND    t1.statistic# = t2.statistic#
AND    t1.name IN ('IM scan CUs memcompress for query low', 
                   'IM scan CUs pruned', 
                   'IM scan segments minmax eligible');
 
NAME                                            VALUE
--------------------------------------------- ----------
IM scan CUs memcompress FOR query low              0
IM scan CUs pruned                                 0
IM scan segments minmax eligible                   0
 
 
SELECT MAX(object_id) FROM big_table WHERE owner ='SCOTT';
 
MAX(OBJECT_ID)
-------------
91774
 
SELECT * FROM TABLE(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID g9r6qxh4dczd4, child NUMBER 0
-------------------------------------
 
SELECT MAX(object_id) FROM big_table WHERE owner ='SCOTT'
Plan hash VALUE: 599409829
-------------------------------------------------------------------------------
| Id | Operation                   | Name     | ROWS | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |      |       | 10730 (100)| 
|   1| SORT AGGREGATE              |          |     1|   11  |            | 
|* 2 |   TABLE ACCESS INMEMORY FULL| BIG_TABLE| 1024 | 11264 | 10730   (3)|
-------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - inmemory("OWNER"='SCOTT')
    FILTER("OWNER"='SCOTT')
 
20 ROWS selected.
 
SELECT t1.name, t2.value
FROM   v$sysstat t1, v$mystat t2
WHERE  t1.name LIKE 'IM%'
AND    t1.statistic# = t2.statistic#
AND    t1.name IN ('IM scan CUs memcompress for query low', 
                   'IM scan CUs pruned', 
                   'IM scan segments minmax eligible');
 
NAME                                            VALUE
--------------------------------------------- ----------
IM scan CUs memcompress FOR query low             19
IM scan CUs pruned                                 0
IM scan segments minmax eligible                  19

Even with the value SCOTT only occurs 1% of the time in our table, Storage Index pruning didn’t take place for our query.

Why?

The value SCOTT fell between the MIX/MAX range for all 19 IMCUs that make up our BIG_TABLE.

But how could that happen if the value SCOTT appears less than 1% of the time?

Take for example a MIX/MAX range of [APEX, XDB] for the owner column on each IMCU. The value SCOTT falls within that range for every IMCU. Therefore no pruning will occur.

Is there any way I can improve the chances of Storage Index pruning occurring?

The only way to improve the chances of Storage Index pruning occurring would be to sort the data within the BIG_TABLE on the owner column. It won’t guarantee pruning but it will certainly increase our chances.

CREATE TABLE big_table_sorted AS SELECT * FROM big_table ORDER BY owner;
 
TABLE created.
 
SELECT /*+ full(b) */ COUNT(*) FROM big_table_sorted b;
 
COUNT(*)
----------
10000000
 
SELECT segment_name, populate_status, bytes_not_populated FROM v$im_segments
 
SEGMENT_NAME             POPULATE_STAT  BYTES_NOT_POPULATED
------------------------ -------------- -------------------
BIG_TABLE_SORTED            COMPLETED      0
BIG_TABLE                   COMPLETED      0

Let’s now try our query again, but this time against the sorted table.

SELECT t1.name, t2.value
FROM   v$sysstat t1, v$mystat t2
WHERE  t1.name LIKE 'IM%'
AND    t1.statistic# = t2.statistic#
AND    t1.name IN ('IM scan CUs memcompress for query low', 
                   'IM scan CUs pruned', 
                   'IM scan segments minmax eligible');
 
NAME                                            VALUE
--------------------------------------------- ----------
IM scan CUs memcompress FOR query low              0
IM scan CUs pruned                                 0
IM scan segments minmax eligible                   0
 
SELECT MAX(object_id) FROM big_table_sorted WHERE owner ='SCOTT'; 
 
MAX(OBJECT_ID) 
-------------- 
91774 
 
SELECT * FROM TABLE(dbms_xplan.display_cursor); 
PLAN_TABLE_OUTPUT 
------------------------------------------------------------------------------- 
SQL_ID f4a2z8wsqjdm0, child NUMBER 0 
------------------------------------- 
SELECT MAX(object_id) FROM big_table_sorted WHERE owner ='SCOTT' 
Plan hash VALUE: 3625205436 
------------------------------------------------------------------------------- 
| Id | Operation                   | Name             | ROWS | Bytes | Cost (%CPU)|     
| ------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT           |                  |       |        4184      |  
|   1 | SORT AGGREGATE             |                  |     1 |   11 |           | 
|* 2  |  TABLE ACCESS INMEMORY FULL| BIG_TABLE_SORTED |   904K| 9714K| 4184      |
------------------------------------------------------------------------------- 
Predicate Information (IDENTIFIED BY operation id): 
2 - inmemory("OWNER"='SCOTT') 
    FILTER("OWNER"='SCOTT') 
 
20 ROWS selected. 
 
SELECT t1.name, t2.value
FROM   v$sysstat t1, v$mystat t2
WHERE  t1.name LIKE 'IM%'
AND    t1.statistic# = t2.statistic#
AND    t1.name IN ('IM scan CUs memcompress for query low', 
                   'IM scan CUs pruned', 
                   'IM scan segments minmax eligible');
 
NAME                                            VALUE
--------------------------------------------- ----------
IM scan CUs memcompress FOR query low             19
IM scan CUs pruned                                18
IM scan segments minmax eligible                  19

As you can see the distribution of the data within the columns has a big impact on the efficiency of the Storage Index. With the data sorted on our WHERE CLAUSE predicated, only one IMCU needs to be read to satisfy the query. So. if you want to tip the odds in your favor, you should consider ordering the data within your tables on the most frequently used, selective column (s) in the WHERE CLAUSE predicates of your workload.

Two easy ways to order the data would be to put an ORDER BY CLAUSE on any bulk load operation done into the table or consider using Attribute Clustering.

Why doesn’t Oracle automatically order the data for me when it’s been brought into the In-Memory column store?

Although there are a number of benefits to sorting the data before it’s populated into the In-Memory column store including better compression ratios and better Storage Index pruning, Oracle does not do it. The data is populated into the In-Memory column store in the exact same order as it appears on disk to ensure row numbers are preserved.

Are there any other differences between Storage Indexes In-Memory and on the Exadata storage cells?

In the In-Memory column store, every column on every table has a Storage Index automatically created and maintained on it. While on the Exadata storage cells there is a limit on the number of Storage Indexes created on each table. Prior Exadata 12.2.1.1.0 the limit was 8 columns per table. From Exadata 12.2.1.1.0 onwards the limit has been increased to 24 columns per table, which means all of the critical columns used in the where clause predicates of your queries will definitely be covered!