Storage indexes (SI) are created automatically if the software sees they can be useful. They are also single column structures. And you can have 8 of them per 1MB of disk space.
So, if you have 24 columns in a table, you may end up with SI created on column A on part of the data, and on column B on another.
Hi Sivakumar A,
By default oracle will use 8k blocks to store table data. So the data off all 24 colums will be stored in 1MB (=128 blocks).
Only 8 columns can be "indexed" in a in-memory regional index by the storage.
will indexes be created based on each 1MB of data or for every 8 columns in a table?
The answer is no.
Storage indexes will only be created on a maximum of 8 columns of table data.
I could not post a comment on your blog, but here's what I have to say:
Just to comment on the SI doesn't work section:
SI can be created for 8 columns in each 1MB region, so that's not 8 columns per table. Also, if you consider that SIs are effective if data is ordered by some column, then an SI on columns A and B will be effective only if data is ordered by A AND B. This usually happens if A and B are correlated columns, and having 8 correlated columns is a long stretch
If you truncate a segment (table or partition), then there's no more data, so the SI is wiped out, what's the problem here?
Restriction number 5 is not true: to start your database, cellserv must be started, so you create objects after starting cellserv, so either SIs never work, or the restriction is not true.
The content of blog is depend on previous experience in my customer environment. And now, my office doesn't have exadata machine. so i can't test.
The scenario of Restriction #4 is as in the following.
1) select count(*) from t1 where c1=:b1 --> maybe SI is created for columns c1 and after that time, we can use SI.
2) truncate table t1; --> data_object_id is changed.
3) insert into t1 (many rows)..
4) select count(*) from t1 where c1=:b1; --> SI doesn't work.
so, I think that every time cellsrv start, oracle manages the data_object_id for all objects and if data_object_id is changed then SI doesn't work.
But, Actually i don't know, that is only port specific problem.
For the scenario you're proposing you say in 1, that SI may be created, and yes it may, or may not.
If it was, it surely cannot be used in in 4, and it is in fact dropped when the segment is truncated.
In 4, it may be rebuilt again, for the new data_object_id.
Did you test it? if you have a reproducible test case, please open a bug.
When a cellsrv starts, it has no idea about data_object_ids, and it has no SIs built yet. And it will surely not go and scan all the blocks to get them and see if the data_object_id changed.
As for it being a port-specific problem, there are no ports here: storage servers are always running Linux, so there are no different ports, but there surely are different versions.