Some Oracle Basics

I am currently reading Chapter 10 and 11 of Tom Kyte's Oracle Book to brush up my database knowledge and would like to summarize the terms about tables and indexes for my future reference.

Row Migration: Suppose the size of row X is 50 bytes and is stored in block A. After an update to row X, the row grows to 100 bytes and can't fit into block A. Oracle will move the updated row to another block where it can fit, say block D, and replace the original row X in block A with a pointer to point to block D. After the update completes, Row X migrates from block A to block D.

Index Organized Table(IOT): For IOT, data are stored in an index structure. Data are automacially sorted according to the keys defined by the index structure. Querying an IOT usually takes one scan because data, not rowids, are stored in the leaf nodes.

Secondary Index: It's an index on index. When one creates an index for an IOT, that index is a secondary index. Leaf nodes contain logical rowids pointing to the data in the IOT. Since leaf nodes in IOT would change places due to shape and size change in IOT, the logical rowids can be stale. When a query is hit in such a situation, it takes 2 scans to locate data, one on the secondary index and the other on the IOT, slower than querying an index on a regular table, which takes one scan to locate rowids and one read to retrieve data.

Index Clustered Table(ICT): For ICT, data are clustered together according to cluster keys. ICT is different from IOT in several ways. First, data in ICT are not sorted by cluster keys as in IOT. data are stored in a heap. Second, data from different tables can be clustered together by the same columns in a ICT. Third, a cluster index takes a cluster key value and returns a block address, not rowid, in the cluster.

Bitmap index: An entry in a bitmap index points to many rows in a table, while an entry in a B*Tree index points to a row in a table. A bitmap index is suitable for indexing data of low cardinality, like gender, blood type, etc, and is especially suitable for aggregation queries, like count the number of women with blood type A. However, since a bitmap entry points to many rows in the table, a update to the entry will cause Oracle lock all the rows pointed to by the entry. So, bitmap indexes are ill suited for a write-intensive environment.

Function-based indexes: Suppose I have a query like the following

SELECT *
FROM USER
WHERE UPPER(USERNAME)  = :USERNAME

Usually, Oracle performs a full-table scan to retrieve data from USER table. By running the following statement, Oracle will build a function-based index for us.

CREATE INDEX USER_UPPER_IDX on USER(UPPER(USERNAME));

For the above query, Oracle scans the index first, and then reads the rows pointed by the index. Essentially, Oracle creates a case-insensitive index for us.

Further readings: Understanding Indexes and Clusters