ITPub博客

You can partition an index-organized table by RANGE, HASH, or LIST on columnvalues. The partitioning columns must form a subset of the primary key columns. Justlike ordinary tables, local partitioned (prefixed and non-prefixed) index as well asglobal partitioned (prefixed) indexes are supported for partitioned index-organizedtables.分区索引组织表1. 用户可以采用 RANGE，HASH，或 LIST 的方式对索引表进行分区2. 分区列必须是属于主键列3. 可在分区索引表上建立本地全局索引[@more@]

The superior query performance for primary key based access, high availabilityaspects, and reduced storage requirements make index-organized tables ideal for thefollowing kinds of applications:■ Online transaction processing (OLTP)■ Internet (for example, search engines and portals)■ E-commerce (for example, electronic stores and catalogs)■ Data warehousing■ Analytic functions索引组织表的应用因为索引组织表的优越查询性能（通过主键访问），高可用性，且能节省空间需求，使他非常适合以下类型的应用

The mapping table is a heap-organized table that stores logical rowids of theindex-organized table. Specifically, each mapping table row stores one logical rowidfor the corresponding index-organized table row. Thus, the mapping table providesone-to-one mapping between logical rowids of the index-organized table rows andphysical rowids of the mapping table rows.A bitmap index on an index-organized table is similar to that on a heap-organizedtable except that the rowids used in the bitmap index on

Oracle supports bitmap indexes on partitioned and nonpartitioned index-organizedtables. A mapping table is required for creating bitmap indexes on an index-organizedtable.1. Oracle支持在分区的或非分区的索引组织表上创建位图索引。2. 为索引组织表创建位图索引时需要使用一个映射表。[@more@]

Secondary index support on index-organized tables provides efficient access toindex-organized table using columns that are not the primary key nor a prefix of theprimary key.Oracle constructs secondary indexes on index-organized tables using logical rowidentifiers (logical rowids) that are based on the table's primary key. A logical rowidincludes a physical guess, which identifies the block location of the row. Oracle canuse these physical guesses to probe directly into the leaf block of the ind

B-tree index entries are usually quite small, because they only consist of the key valueand a ROWID. In index-organized tables, however, the B-tree index entries can be large,because they consist of the entire row. This may destroy the dense clustering propertyof the B-tree index.Oracle provides the OVERFLOW clause to handle this problem. You can specify anoverflow tablespace so that, if necessary, a row can be divided into the following twoparts that are then stored in the index and in the over

Index-organized tables provide faster access to table rows by the primary key or anykey that is a valid prefix of the primary key. Presence of nonkey columns of a row inthe B-tree leaf block itself avoids an additional block access. Also, because rows arestored in primary key order, range access by the primary key (or a valid prefix)involves minimum block accesses.In order to allow even faster access to frequently accessed columns, you can use a rowoverflow segment (as described later) to push o

Ordinary Table * Rowid uniquely identifies a row. Primary key can be optionally specified* Physical rowid in ROWID pseudocolumn allows building secondary indexes* Access is based on rowid* Sequential scan returns all rows* Can be stored in a cluster with other tables* Can contain a column of the LONG datatype and columns of LOB datatypesIndex-Organized Table* Primary key uniquely identifies a row.Primary key must be specified* Logical rowid in ROWID pseudoco

An index-organized table has a storage organization that is a variant of a primaryB-tree. Unlike an ordinary (heap-organized) table whose data is stored as anunordered collection (heap), data for an index-organized table is stored in a B-treeindex structure in a primary key sorted manner. Besides storing the primary keycolumn values of an index-organized table row, each index entry in the B-tree storesthe nonkey column values as well.The index-organized table is somewhat similar to a configurati

全文摘自http://ningoo.itpub.net/post/2149/223616Bitmap index的主要思想就是，针对每一个可能的值x，建立一个或一组位图映射，每个bit为1代表这个位置的值等于x，为0则不等于x。而每个位置都可以直接映射到某一行的rowid。由于在执行DML操作时，锁定的是整个bitmap，而不是bitmap中的某个位，所以bitmap index对于并发DML的性能很差，而且频繁的DML操作会使得bitmap index的空间效率大打折扣，所以OLTP系统并不合适使用bitmap index。对于基本没有DML操作，有大量ad hoc查询的Data warehouse环境则相当有效。从oracle9i 起，oracle又引

全文引用至http://www.oracle-base.com/articles/9i/BitmapJoinIndexes.phpIn Oracle8i performance improvements were made using materialized views to store the resulting rowsof queries. The benefits of this mechanism are still relevant, but a certain subset of the queriesused in a data warehouse may benefit from the use of Bitmap Join Indexes.How It WorksIn a Bitmap Index, each distinct value for the specified column is associated with a bitmap whereeach bit represents a row in the table. A '1' means

PurposeDUMP returns a VARCHAR2 value containing the datatype code, length in bytes, andinternal representation of expr. The returned result is always in the database characterset.The argument return_fmt specifies the format of the return value and can have anyof the following values:■ 8 returns result in octal notation.■ 10 returns result in decimal notation.■ 16 returns result in hexadecimal notation.■ 17 returns result as single characters.By default, the return value contains no character

"北国风光，千里冰封，万里雪飘"北国的冬天已下雪 ，好冷啊！[@more@]

In addition to a bitmap index on a single table, you can create a bitmap join index,which is a bitmap index for the join of two or more tables. A bitmap join index is aspace efficient way of reducing the volume of data that must be joined by performingrestrictions in advance. For each value in a column of a table, a bitmap join indexstores the rowids of corresponding rows in one or more other tables. In a datawarehousing environment, the join condition is an equi-inner join between theprimary ke

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