ORACLE SQL总结三：DDL语句(二)

位图索引

www.2cto.com

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

在位图索引中，数据库为每个index key都存储了一个位图(bitmap)。在一般的B-tree索引中，一个索引实体(entry)指向一条记录(a single row),但是在位图索引中，每个index key指向多条记录(multiple rows)

Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:

The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.

The indexed table is either read-only or not subject to significant modification by DML statements.

位图索引主要设计用于数据仓库或类似的有随机方式分布的大量的列的数据环境，可以使用位图索引的情形包括：

-索引列有较低的相似度，这就意味着，在表的记录中相同值的数据的数量比较少。

-被索引的表或者只读或者不是DML语句重点照顾的对象

www.2cto.com

For a data warehouse example, the sh.customer table has a cust_gender column with only two possible values: M and F. Suppose that queries for the number of customers of a particular gender are common. In this case, the customer.cust_gender column would be a candidate for a bitmap index.

举一个数据仓库的例子，sh.customer表有一个cust_gender列，这个列里仅仅包括两个值：M或F，假设没有其他的性别(中性 ),在这个例子中 customer.cust_gender列有比较少的相似度，可以作为位图索引。

Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation.

在位图中每位(bit)对应一个可能的rowid，如果位被指定，该rowid对应的记录(row)也就对应了值，一个映射函数将位的位置转变成一个确切的rowid，这样位图索引就以B-TREE索引不同方式提供了相同的功能。

If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows. For this reason, bitmap indexes are not appropriate for many OLTP applications.

如果被索引的列中有一条记录被修改，那么数据库就会锁定索引值(比如M或F)，因为一个索引值对应很多记录，即数据库不会只锁定被修改的记录，连那些与该记录有相同索引值的记录也会一起被锁定。因为这个原因，位图索引不适合于OLTP应用。

Index-Organized Tables

索引组织表

An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

www.2cto.com

索引组织表(index-organized table)是采用B-TREE索引结构存储方式的表的变种之一，在一个栈组织表(heap-organized table)中，哪里有多余的空间，记录(row)就会被插入到哪里(也就是说记录不以顺序的方式存储，物理上是随机的)。但是在index-organized table中，记录被存储在索引中，并按主键的顺序排列。在B-TREE每个索引实体也存储非主键的列，因此，索引就是表，表也就是索引。但应用操作index-organized table与操作 heap-organized tables没有不同，也使用SQL语句。

For an analogy of an index-organized table, suppose a human resources manager has a book case of cardboard boxes. Each box is labeled with a number—1, 2, 3, 4, and so on—but the boxes do not sit on the shelves in sequential order. Instead, each box contains a pointer to the shelf locatio