Exadata storage indexes are purely memory only structures located on
exadata storage servers, care need to taken in how much memory they can
potentially consume. While on exadata storage cells, there is a limit on the
number of storage index created on each table, prior to Exadata storage
software 12.2.1.1.0 there was a limit of 8 columns per table, with 12.2.1.1.0
the limit has been increased to 24 columns per table. Which means there a high
chances for all of the critical columns used in the where clause predicates
will be covered in storage indexes.
Here is a test case to demonstrate that.
First, will create a table of 24+ columns of nicely clustered data,
which will make them the obvious candidates for storage indexes as queries occur
on them.
c##rajesh@PDB1>
create table t as
2
select trunc(rownum/1000) as c1,
3
trunc(rownum/1000) as c2,
4
trunc(rownum/1000) as c3,
5
trunc(rownum/1000) as c4,
6
trunc(rownum/1000) as c5,
7
trunc(rownum/1000) as c6,
8
trunc(rownum/1000) as c7,
9
trunc(rownum/1000) as c8,
10
trunc(rownum/1000) as c9,
11
trunc(rownum/1000) as c10,
12
trunc(rownum/1000) as c11,
13
trunc(rownum/1000) as c12,
14
trunc(rownum/1000) as c13,
15
trunc(rownum/1000) as c14,
16
trunc(rownum/1000) as c15,
17
trunc(rownum/1000) as c16,
18
trunc(rownum/1000) as c17,
19
trunc(rownum/1000) as c18,
20
trunc(rownum/1000) as c19,
21
trunc(rownum/1000) as c20,
22
trunc(rownum/1000) as c21,
23
trunc(rownum/1000) as c22,
24
trunc(rownum/1000) as c23,
25
trunc(rownum/1000) as c24,
26
trunc(rownum/1000) as c25,
27
rpad('*',100,'*') as
some_other_data
28 from
big_table
29
where rownum <= 10000000;
Table created.
c##rajesh@PDB1>
--Warming up the storage region to build storage indexes
c##rajesh@PDB1>
c##rajesh@PDB1>
declare
2
l_data t.some_other_data%type;
3
begin
4 for i in 1..24
5
loop
6
for k in 1..20
7
loop
8
dbms_application_info.set_client_info( ' i='||i||' k='||k);
9 execute immediate ' select
max(some_other_data) from t where c'||i||' < :b1 '
10 into l_data
11 using
((k*10)+1000) ;
12
end loop;
13
end loop;
14 end;
15 /
PL/SQL
procedure successfully completed.
Procedure to accept the column name as input parameter, and prepare a
dynamic sql and execute it.
c##rajesh@PDB1>
create or replace procedure validate_si(p_colName in varchar2)
2 as
3
l_data t.some_other_data%type;
4
begin
5
execute immediate ' select max(some_other_data) from t where '||
p_colName|| ' < 2000 '
6
into l_data;
7
dbms_output.put_line( l_data );
8 end;
9 /
Procedure
created.
Here is the script; we can use to see the storage index in play.
c##rajesh@PDB1>
$ type script05.sql
conn
c##rajesh/Password-1@PDB1
col name format
a60
col value
format 999999999999999
select
s.name,m.value
from v$statname
s ,
v$mystat m
where
s.statistic# = m.statistic#
and s.name in
('cell physical IO bytes saved by storage index',
'cell physical
IO interconnect bytes returned by smart scan' );
exec
validate_si('&1');
select
s.name,m.value
from v$statname
s ,
v$mystat m
where
s.statistic# = m.statistic#
and s.name in
('cell physical IO bytes saved by storage index',
'cell physical
IO interconnect bytes returned by smart scan' );
c##rajesh@PDB1>
Invoking the script for the column C1 produced a result like this.
c##rajesh@PDB1>
@script05.sql C1
Connected.
NAME VALUE
------------------------------------------------------------
----------------
cell physical
IO bytes saved by storage index 0
cell physical
IO interconnect bytes returned by smart scan 0
PL/SQL
procedure successfully completed.
NAME
VALUE
------------------------------------------------------------
----------------
cell
physical IO bytes saved by storage index 1869717504
cell physical
IO interconnect bytes returned by smart scan 2045376
This confirms that storage index was been used while scanning against
column C1.
The same happens for column C2.
c##rajesh@PDB1>
@script05.sql C2
Connected.
NAME
VALUE
------------------------------------------------------------
----------------
cell physical
IO bytes saved by storage index 0
cell physical
IO interconnect bytes returned by smart scan 0
PL/SQL
procedure successfully completed.
NAME
VALUE
------------------------------------------------------------
----------------
cell
physical IO bytes saved by storage index 1869717504
cell physical
IO interconnect bytes returned by smart scan 2045376
The results were same when invoking for every other columns in the
table. Here is the results for the 24th column in the table.
c##rajesh@PDB1>
@script05.sql C24
Connected.
NAME
VALUE
------------------------------------------------------------
----------------
cell physical
IO bytes saved by storage index 0
cell physical
IO interconnect bytes returned by smart scan 0
PL/SQL
procedure successfully completed.
NAME VALUE
------------------------------------------------------------
----------------
cell
physical IO bytes saved by storage index 1869717504
cell physical
IO interconnect bytes returned by smart scan 2045376
as you can see we get the storage index benefit for all the 24 columns,
thus proving that we have indeed lifted the limit from 8 storage indexes.
