Notes on ‘Expert Oracle’ — No.10.2: Table — HOT & IOT

1. HOT => Heap Organized Table

   IOT => Index Organized Table


  Heap Organzied


  a.The data is index Organized

  b.The data is the index, the index is the data

     i.No extra cost for managing index

    ii.When you get the index, you get the data. No "table access" is needed in querys.

   iii.Related data will be put together => Fewer blocks are accessed for queries aimed for related data => Less I/O


    i.NOCOMPRESS: Every combination of the value of indexed-columns requires an occupation in the storage. <abc,1> and <abc,2> will need 2*2 rooms

   ii.COMPRESS N: <abc,1> and <abc,2> will be stored as <abc>, <1>,<2> = 3 rooms.

      Less room => more data in a block => Fewer blocks needed => Less I/O

  d.Overflow Segment

     Q: Where does Oralce put non-indexed row data?

     A: It may be put in index-blocks along with the indexed keys, or put in an "Overflow" segment if its size is too large.

     Q: How does Oracle decide where to put then?

     A: Oracle can refer to PCTTHRESHOLD for a max percentage, or check "INCLUDING" to see which columns should be retained in index blocks.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.