This discussion is archived
8 Replies Latest reply: May 24, 2013 12:13 PM by tychos RSS

columns in storage indexes

706543 Newbie
Currently Being Moderated
Hi All,

Exadata storage indexes stores eight columns in a table.

I am bit confused here, what will happen if I am having a table with 24 columns. In this case how does Storage indexes use these 24 columns.

whether three indexes will be created for this table?

Kindly explain.
  • 1. Re: columns in storage indexes
    gsalem Explorer
    Currently Being Moderated
    Sivakumar,
    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.

    rgds
  • 2. Re: columns in storage indexes
    706543 Newbie
    Currently Being Moderated
    Hi Salem,

    I am not clear with your answer.

    Single SI will be having value (i.e Min & Max value) for each column upto eight columns in a table for each 1MB of size.

    here my question is whether index will be created based on each 1MB of data or for every 8 columns in a table
  • 3. Re: columns in storage indexes
    tychos Expert
    Currently Being Moderated
    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.
    Regards,
    Tycho
  • 4. Re: columns in storage indexes
    1006154 Newbie
    Currently Being Moderated
    Hi~
    I hope that this article helpful to you. http://dbmstuning.tistory.com/24

    :) sorry for my pool english skill.
  • 5. Re: columns in storage indexes
    gsalem Explorer
    Currently Being Moderated
    Hi,
    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.
    --------
    rgds
  • 6. Re: columns in storage indexes
    1006154 Newbie
    Currently Being Moderated
    Thanks for your comment.

    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.
    Thank you!~

    Edited by: seankim on 2013. 4. 30 오전 7:57

    Edited by: seankim on 2013. 4. 30 오전 7:58

    Edited by: seankim on 2013. 4. 30 오전 7:58
  • 7. Re: columns in storage indexes
    gsalem Explorer
    Currently Being Moderated
    Hi,
    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.

    Regards
  • 8. Re: columns in storage indexes
    tychos Expert
    Currently Being Moderated
    Hi Sivakumar A,
    how does Storage indexes use these 24 columns. whether three indexes will be created for this table?
    This is the information answering how SI works.
    http://richardfoote.wordpress.com/2013/05/01/storage-indexes-vs-database-indexes-iv-8-column-limit-eight-line-poem/
    Rgds,
    Tycho

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points