Posted by We had a question on AskTOM inquiring about how to handle the issue of only 8 storage indexes being possible on an Exadata engineered system. If you are unfamiliar with what a storage index is, they are part of the suite of features often referred to as the “secret sauce” that can improve query performance on Exadata systems by holding more metadata about the data that is stored on disk. You can get an introduction to the concept of storage indexes here.
Well..the good news is, the limit of 8 has been lifted in recent versions of the Exadata software up to 24. When I replied to the questioner, they demanded proof 
, which I was glad to see (even though it means more work for me). Because one of my philosophies for AskTOM is that people should never just trust what they read on a forum (AskTOM or otherwise).
There is no dictionary view or performance view that is a direct path into the storage indexes, that is, you cannot see what is “in” them at any given point in time. However, there is a statistic “cell physical IO bytes saved by storage index” which gives cumulative information about how useful a storage index was, so whenever that statistic increments after a query execution, we know that some savings were made.
To keep the source code down to a manageable size, I’ll do the proof with just 12 columns, but you can easily ramp this up to 24 columns and you will see the same results. First I’ll create a table with 12 columns of nicely clustered data, which will make them obvious candidates for storage indexes as queries occur on them.
SQL> create table t as
2 select
3 trunc(rownum/1000) c1,
4 trunc(rownum/1000) c2,
5 trunc(rownum/1000) c3,
6 trunc(rownum/1000) c4,
7 trunc(rownum/1000) c5,
8 trunc(rownum/1000) c6,
9 trunc(rownum/1000) c7,
10 trunc(rownum/1000) c8,
11 trunc(rownum/1000) c9,
12 trunc(rownum/1000) c10,
13 trunc(rownum/1000) c11,
14 trunc(rownum/1000) c12,
15 rpad(rownum,500) data
16 from
17 ( select 1 from dual connect by level < 5000 ),
18 ( select 1 from dual connect by level < 5000 );
Table created.
With an initial query, you can see that there are no storage index benefits in play yet.
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell XT granule IO bytes saved by storage index 0
So to ensure that my storage indexes are working at all, I’ll run a series of queries against column C1 and check before and after values for the “cell physical IO bytes saved by storage index” statistic.
SQL> declare
2 x varchar2(1000);
3 begin
4 for i in 1 .. 20
5 loop
6 execute immediate 'select max(data) from t where c1 < '||(i*10+1000) into x;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- ----------------
cell physical IO bytes saved by storage index 316601630720
cell XT granule IO bytes saved by storage index 0
At this point we know that storage indexes are available and in use on this database. Because the creation and usage of storage indexes is not in the hands of the developer, but a decision made by the Exadata software layer, I’ll now throw a selection of queries at the database on columns C1 to C12 with varying ranges to “let the database know” that these columns are important to me, and hence that storage indexes are going to be of benefit.
SQL> declare
2 x varchar2(1000);
3 begin
4 for c in 1 .. 12 loop
5 for i in 1 .. 20
6 loop
7 execute immediate 'select max(data) from t where c'||c||' < '||(i*10+1000) into x;
8 end loop;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 3950481989632
cell XT granule IO bytes saved by storage index 0
Now all is in readiness to prove that we have storage indexes for all 12 columns. I can now issue a simple query against each of the 12 columns C1 to C12, and each time I would expect to see the cell physical IO bytes saved by storage index” statistic increment. Once I have got to column C12, I’ll loop back around to C1 just to guard against the scenario that perhaps storage indexes are being thrown away as I move to other column queries.
SQL> select max(data) from t where c1 < 2000;
MAX(DATA)
-------------------------------
999999
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 3965752631296
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c2 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 3981023272960
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c3 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 3996293914624
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c4 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4011564556288
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c5 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4026835197952
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c6 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4042105839616
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c7 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4057376481280
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c8 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4072647122944
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c9 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4087917764608
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c10 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4103188406272
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c11 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4118459047936
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c12 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4133729689600
cell XT granule IO bytes saved by storage index 0
SQL>
SQL> select max(data) from t where c1 < 2000;
MAX(DATA)
-------------------------------
999999
SQL>
SQL> select name, value
2 from v$mystat s, v$statname n
3 where s.statistic# = n.statistic#
4 and name like '%storage index%';
NAME VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index 4149000331264
cell XT granule IO bytes saved by storage index 0
As you can see, I got storage index benefit from all 12 queries, thus proving that we have indeed lifted the limit from 8 storage indexes. It is now up to 24 columns….trust me
Learning is not a spectator sport 
Well – that was my question from asktom, got into this blog post. nice.
https://asktom.oracle.com/pls/apex/asktom.search?tag=12c-table-clustering-feature-and-storage-indexes-bloom-filters-and-where-filters#9541990000346959756
Any specific reason to not have this sql as bind friendly ?
execute immediate ‘select max(data) from t where c1 < '||(i*10+1000) into x;
No reason, just how it came off the keyboard 🙂
I thought that storage indexes are actually decided on a storage region basis i.e a 1mb chunk of the table. What this implies that different chunks might use indexes for different columns so it’s quite possible that overall for one table more that 24 columns have storage indexes against them since choice per region is independant.
From which version this is possible?
v12 I think
Hi Connor – I think it’s great that the number of storage indexes has increased. Just one question, is it the version of Oracle DB that is critical to when the number increases or is it down the to version of Exdata (eg X6-2, X7-2 or X8-2) or do you even have to dig down to the version of the cell server software?
12.2 of the *storage* software I’m pretty sure