index-organized partitioned tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: index-organized partitioned tables

  1. #1
    Join Date
    May 2000
    Location
    Delhi
    Posts
    108

    Question

    Hi Friends,
    I have a very large table, which has only two columns - one is a VARCHAR2(15) and the other is
    NUMBER. The primary key of this table consists of both columns. In addition to the index created
    for the primary key, I have also created a separate index (non-unique) on the second column. Here
    are the specifics:

    Table A: 367001600 bytes; 18600797 rows
    Primary key index: 524288000 bytes
    Index on column 2: 367001600 bytes

    The way I understand indexes, they are essentially storing the same data in the column(s) being
    indexed, in sorted order. This makes me wonder about a few things in the above example:

    1. Why is the index on column 2 the same size (in bytes) as the original table? If it is only
    storing the data from column 2 (sorted), shouldn't it be smaller than the table, which has two
    columns worth of data?

    2. Why is the primary key index quite a bit larger than the original table? Since this index is on
    both columns of the table, I would think that it would be storing the same data as the table does,
    just sorted in a different order. Shouldn't this index and the table be the same size?

    3. In this case, where I have a primary key (index) that contains all fields of the table that it
    is indexing, it seems wasteful to store this data twice. After all, if the primary key index stores
    the same data that is in the table, but sorted, what is the use of the original table? Is there
    some way to combine the table and the index into one "entity" so that this data is only stored in
    the database once?

    Thanks for any insights on this matter.
    sahib





  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    2.
    Your index is bigger then you table because in the index-blocks the ROWID is also stored.
    So,
    in the table-blocks you have VARCHAR2(15)+Number
    in the index-blocks you have ROWID+VARCHAR2(15)+Number

    1.
    Not sure but,
    may be the space occupied by the ROWID in the index, equals
    the space occupied by the VARCHAR2(15) in the table ???

    3.
    use an Index Organized Table
    but, it depends on your version if you will be able to make a second index on that IOT.

    Hope this helps.
    Gert

  3. #3
    Join Date
    Nov 2000
    Posts
    178
    Hi,

    How did you decide the space occupied by the index (in bytes)?

    Ac

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width