a) Yes. Anything that will touch *all* block for the table can yield a storage index.
b) The algorithm is not public but the storage servers decide on the "best" columns to index. The definition of "best" is internal, but I would expect levels of usage in predicates, and the clustering of the data to be dominant factors.
c) I don't know of a view or similar structure that exposes that. The only way I know would be to run a test query for each column. There are some trace settings that can be applied on the storage servers - you'd need to talk to Support about them because obviously tracing *every* I/O is generally a bad idea :-)
d) Here's an example
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.
--
-- lets do a scan on C1
--
SQL> select max(data) from t where c1 < 1000;
MAX(DATA)
------------------------------------------------------------
999999
--
-- we can see that just one scan was not enough to convince
-- the cells to make an index
--
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
--
-- Now we'll do 20 scans on C1
--
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.
--
-- and we can see that we got some savings
--
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
--
-- now I'll do 20 scans on columns C1.. C12, to see if they ALL can get an index
--
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
--
-- So this is our starting point (3950481989632), now I'll run a scan on
-- each column C1.. C12, and see if with each one we got a savings
--
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
--
-- And just to double check, lets make sure that C1 still has its index
--
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
SQL>
SQL>
SQL>
SQL>
So you can see, we have (at least) 12 indexes. This routine could be scaled up to as many columns as you like.