oracle10g索引使用策略

1）As you upgrade Oracle versions, be sure to test your application’s queries to determine whether the execution paths for your queries still use the indexes that were used prior to the upgrade. See if the execution plan has changed and if it is better or worse.

　升级oracle版本的时候，应该测试下升级后的sql语句执行计划仍然使用用升级之前的索引，看下执行计划是否变了，是变的更好或者更糟。

2）Query DBA_INDEXES and DBA_IND_COLUMNS to retrieve a list of the indexes on a given table. Use USER_INDEXES and USER_IND_COLUMNS to retrieve information for only your schema.

   通过查询DBA_INDEXES 和 DBA_IND_COLUMNS 两个视图来获取数据库表的索引及索引字段，使用USER_INDEXES 和 USER_IND_COLUMNS 查询当前用户的索引和索引字段。
3）Avoid hard-coding Oracle’s ROWID into specific code. The ROWID structure in the past has changed from version to version, and it will probably change again in future releases. I recommend against ever hard-coding a ROWID.
   避免直接在代码中使用ROWID，ROWID的结构在过去版本升级时发生变化，将来也会发生变化，所以推荐避免直接使用ROWID进行编码。  
4）You can create your indexes and analyze them in a single step by using the COMPUTE STATISTICS clause of the CREATE INDEX command.

   可以在create index创建索引的时候指定compute statistics分析它。
5）Using the default values clause for a column of a table will cause NULL values to be disallowed and eliminate the performance problems associated with using NULL values.

   可以为一个表的字段因为空值导致效率问题指定一个默认值（null无法使用索引）。

6）By using functions (such as a TO_DATE or TO_CHAR) that alter the values being compared to a column and not the columns themselves, the indexes become available for use that might have been suppressed had you used the function on the column itself.

    尽量避免对列使用函数，而是对值使用函数，可是使用索引。

7）Comparing mismatched data types can cause Oracle to internally suppress an index.Even an EXPLAIN PLAN on the query will not help you understand why a full table scan is being performed.

   数据类型不匹配，oracle会忽略使用索引。

8）The selectivity of an index is what helps the cost-based optimizer determine an execution path. The more selective, the fewer number of rows that will be returned.Improve the selectivity by creating concatenated/composite (multicolumn)

indexes.
     索引的选择性会有助于CBO生成执行计划，选择性越高，返回的数据行数越少，可以通过建立联合索引提高选择性。
9）In general, the larger the database block size, the smaller the binary height of the index.

     一般情况下，数据库块越大，索引的二元高度越小。

10） Each additional level in the blevel adds additional performance costs during DML.

   

11）The clustering of data within the table can be used to improve the performance of statements that perform range scan

type operations. By determining how the column is being used in the statements, indexing these column(s) may be a great

benefit.

12）Analyzing the index or the table will provide the binary height of the index. Use the blevel column in the

USER_INDEXES view to check the binary height of the indexes.
   可以通过分析表得到索引的二元高度，使用user_indexes的blevel字段查询此值。
13） If the number of deleted rows within an index approaches 20–25 percent, rebuild the indexes to help reduce the

binary height and the amount of empty space that is being read during an I/O.

如果一个表的数据被删除20%--25%，重新索引来减少索引的二元高度和空余的空间来减少I/O

14）If the data in a table is skewed, histograms will provide the cost-based optimizer a picture of the distribution. Using

the histograms on columns that are not skewed will not provide an increase in performance but will probably degrade it.

15）By default, Oracle creates 75 buckets in a histogram. You can specify SIZE values ranging from 1 to 254.

16）For large tables with concatenated indexes, the index skip-scan feature can provide quick access even when the

leading column of the index is not used in a limiting condition.

对于大表上的联合索引，skip-scan index可以快速查询数据即使索引的前导字段没有在查询条件中。

17）The values of the indexed columns are stored in an index. For this reason, you can build concatenated (composite) indexes that can be used to satisfy a query without accessing the table. This eliminates the need to go to the table to retrieve

the data, reducing I/O.

18）Use bitmap indexes for columns with a low cardinality. An example is a column called *** with two possible values of

male or female (the cardinality is only 2).

19）To query a list of your bitmap indexes, query the USER_INDEXES view.

20）Don’t use bitmap indexes on tables that are heavily inserted into primarily in heavy OLTP environments; learn the

restrictions associated with bitmap indexes.

21）Caution should be taken before implementing hash clusters. The application should be reviewed carefully to ensure that enough information is known about the tables and data before implementing this option. Generally speaking, hashing is

best for static data with primarily sequential values.

22）Hash indexes are most useful when the limiting condition specifies an exact value rather than a range of values.
  
23）Consider using index-organized tables for tables that are always accessed using exact matches or range scans on

the primary key.

24）If you have a limited number of disks and large concurrent sequential loads to perform,reverse key indexes may be

aviable solution.
  
25）For function-based indexes to be used by the optimizer, you must set the QUERY_REWRITE_ENABLED initialization

parameter to TRUE.

   为了使用函数索引，必须设置初始化参数QUERY_REWRITE_ENABLED =true

26）Local prefixed indexes allow Oracle to quickly prune unneeded partitions. The partitions that do not contain any of the

values appearing in the WHERE clause will not need to be accessed, thus improving the performance of the statement.

27）For a non-prefixed index to be unique, it must contain a subset of the partitioning key.

28）Specify the UPDATE GLOBAL INDEXES clause of the ALTER TABLE command when modifying a partitioned table. By default, you will need to rebuild global indexes when altering a partitioned table.
   当使用alter table修改一个分区表指定update global indexes，默认情况下，修改一个分区表的时候，都需要重建全局索引。
29）If a global index is going to be equipartitioned, it should be created as a local index to allow Oracle to maintain the

index and use it to help prune partitions that will not be needed.

如果全局索引等同于分区索引，它应该被指定为一个local index，这样oracle在维护索引索引的时候也比较方便。

30）Use bitmap join indexes to improve the performance of joins within data warehousing environments.

    使用bitmap join索引可以提高数据仓库的表关联查询性能。

31）Use the REBUILD option of the ALTER INDEX statement for quickly rebuilding an index using the existing index

instead of the table.
    使用alter index 的rebuild选项可以利用存在的索引快速重建索引。
32）You can use the REBUILD ONLINE option to allow DML operations on the table or partition during the index rebuild.

You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.

可以使用rebuild online允许在表或分区上的DML操作当索引重建时，但是位图索引、参照完整性约束索引不能指定rebuild online.