ITPub博客

Bitmap indexes can include rows that have NULL values, unlike most other types ofindexes. Indexing of nulls can be useful for some types of SQL statements, such asqueries with the aggregate function COUNT.位图索引与空值1. 与其他大多数索引不同，位图索引、可以包含键值为NULL的行。2. 将键值为空的行进行索引对有些 SQL 语句是有用处的，例如包含COUNT聚合函数的查询。[@more@]

Like other indexes, you can create bitmap indexes on partitioned tables. The onlyrestriction is that bitmap indexes must be local to the partitioned table—they cannot beglobal indexes. Global bitmap indexes are supported only on nonpartitioned tables.分区表上的位图索引1. 分区表上只支持本地的位图索引 ，不支持全局位图索引[@more@]

eg ：婚姻状态 (已婚 ，未婚 ，离婚)，地区 (中部 ，西部 ，东部)建立在婚姻状态列上的位图索引将包含3个位 ，对应的每条记录为0或者1在低基数上建立位图索引 ，在查询中使用AND , OR将显著提高性能SELECT COUNT(*) FROM CUSTOMERWHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');[@more@]

The advantages of using bitmap indexes are greatest for low cardinality columns: thatis, columns in which the number of distinct values is small compared to the number ofrows in the table. If the number of distinct values of a column is less than 1% of thenumber of rows in the table, or if the values in a column are repeated more than 100times, then the column is a candidate for a bitmap index. Even columns with a lowernumber of repetitions and thus higher cardinality can be candidates if they t

Bitmap indexing benefits data warehousing applications which have large amounts ofdata and ad hoc queries but a low level of concurrent transactions. For suchapplications, bitmap indexing provides:■ Reduced response time for large classes of ad hoc queries■ A substantial reduction of space use compared to other indexing techniques■ Dramatic performance gains even on very low end hardware■ Very efficient parallel DML and loadsFully indexing a large table with a traditional B-tree index can be

The purpose of an index is to provide pointers to the rows in a table that contain agiven key value. In a regular index, this is achieved by storing a list of rowids for eachkey corresponding to the rows with that key value. Oracle stores each key valuerepeatedly with each stored rowid. In a bitmap index, a bitmap for each key value isused instead of a list of rowids.Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it meansthat the row with the corresponding rowid

Creating a reverse key index, compared to a standard index, reverses the bytes of eachcolumn indexed (except the rowid) while keeping the column order. Such anarrangement can help avoid performance degradation with Real Application Clusterswhere modifications to the index are concentrated on a small set of leaf blocks. Byreversing the keys of the index, the insertions become distributed across all leaf keys inthe index.Using the reverse key arrangement eliminates the ability to run an index rang

Key compression is useful in many different scenarios, such as:■ In a nonunique regular index, Oracle stores duplicate keys with the rowidappended to the key to break the duplicate rows. If key compression is used,Oracle stores the duplicate key as a prefix entry on the index block without therowid. The rest of the rows are suffix entries that consist of only the rowid.■ This same behavior can be seen in a unique index that has a key of the form (item,time stamp), for example (stock_ticker, tr

Oracle Containers for J2EE 10g Release 3 (10.1.3), or OC4J, provides a complete Java 2 EnterpriseEdition (J2EE) 1.4-compliant environment. OC4J provides all the containers, APIs, and servicesmandated by the J2EE specification.OC4J is distributed in two configurations: *A standalone configuration, in which OC4J is installed as a single, "standalone" instance andis managed, started and stopped directly as a self-contained component. *A managed configuration, in which OC4J is instal

Key compression can lead to a huge saving in space, letting you store more keys ineach index block, which can lead to less I/O and better performance.Although key compression reduces the storage requirements of an index, it canincrease the CPU time required to reconstruct the key column values during an indexscan. It also incurs some additional storage overhead, because every prefix entry hasan overhead of 4 bytes associated with it.性能以及存储上的考虑1. 可以在索引块中存储跟过的索引键 ，减少

Key compression breaks the index key into a prefix entry (the grouping piece) and asuffix entry (the unique piece). Compression is achieved by sharing the prefix entriesamong the suffix entries in an index block. Only keys in the leaf blocks of a B-treeindex are compressed. In the branch blocks the key suffix can be truncated, but the keyis not compressed.Key compression is done within an index block but not across multiple index blocks.Suffix entries form the compressed version of index rows. E

Key compression lets you compress portions of the primary key column values in anindex or index-organized table, which reduces the storage overhead of repeatedvalues.Generally, keys in an index have two pieces, a grouping piece and a unique piece. Ifthe key is not defined to have a unique piece, Oracle provides one in the form of arowid appended to the grouping piece. Key compression is a method of breaking offthe grouping piece and storing it so it can be shared by multiple unique pieces.键压缩

Index range scan is a common operation for accessing selective data. It can bebounded (bounded on both sides) or unbounded (on one or both sides). Data isreturned in the ascending order of index columns. Multiple rows with identical valuesare sorted (in ascending order) by the ROWIDs.索引范围1. 索引范围扫描可以是封闭或者不风不的2. 数据是以按照索引列的升序排列的[@more@]

Index unique scan is one of the most efficient ways of accessing data. This accessmethod is used for returning the data from B-tree indexes. The optimizer chooses aunique scan when all columns of a unique (B-tree) index are specified with equalityconditions.索引唯一扫描当一个唯一索引的某些与查询体条件表达式相等时，优化器将选择使用索引唯一扫描[@more@]

The B-tree structure has the following advantages:■ All leaf blocks of the tree are at the same depth, so retrieval of any record fromanywhere in the index takes approximately the same amount of time.■ B-tree indexes automatically stay balanced.■ All blocks of the B-tree are three-quarters full on the average.■ B-trees provide excellent retrieval performance for a wide range of queries,including exact match and range searches.■ Inserts, updates, and deletes are efficient, maintaining key or

All leaf blocks are at the same depth from the root branch block. Leafblocks store the following:■ The complete key value for every row■ ROWIDs of the table rowsAll key and ROWID pairs are linked to their left and right siblings. They are sorted by(key, ROWID).叶子节点1. 所有的叶子节点相对于其根分支块具有相同的深度2. 叶子节点存储以下信息 ：每行完成的键值 和 数据行的ROWID所有的 键值-ROWID 对都与其左右的兄弟节点链接，并按照（key，ROWID）的顺序排序[@more@]

Branch blocks store the following: ■ The minimum key prefix needed to make a branching decision between two keys ■ The pointer to the child block containing the key If the blocks have n keys then they have n+1 pointers. The number of keys and pointers is limited by the block size. 分支块 分之块存储以下信息 1. 最小的键值前缀 ， 在两个键值之间做出分支选择 2. 指向包含所查找键值的子块的指针 包含n个键值的分支块对应有n+1个指针 。键值和指针数有块大小所限制[@more@]

The two kinds of blocks:■ Branch blocks for searching■ Leaf blocks that store the values索引属性包含两种类型的块 ：分支块用来搜索　，　叶子块则存储值[@more@]

Oracle uses B-trees to store indexes to speed up data access. With no indexes, you haveto do a sequential scan on the data to find a value. For n rows, the average number ofrows searched is n/2. This does not scale very well as data volumes increase.Consider an ordered list of the values divided into block-wide ranges (leaf blocks). Theend points of the ranges along with pointers to the blocks can be stored in a search treeand a value in log(n) time for n entries could be found. This is the basi

Space available for index data is the Oracle block size minus block overhead, entryoverhead, rowid, and one length byte for each value indexed.When you create an index, Oracle fetches and sorts the columns to be indexed andstores the rowid along with the index value for each row. Then Oracle loads the indexfrom the bottom up. For example, consider the statement:CREATE INDEX employees_last_name ON employees(last_name);Oracle sorts the employees table on the last_name column. It then loads the ind