ORACLE数据库数据操作优化Data Access讲.doc

Data Access Methods Data access methods that can enhance performance Situations to avoid How to use hints to force various approaches Using Indexes When to Create Indexes Improve the performance of queries that select a small percentage of rows from a table(less than 2% or 4% of the tables rows) Value may be higher: All data can be retrieved from an index；The indexed columns and expressions can be used for joining to other tables. Tuning the Logical Structure CBO avoids the use of nonselective indexes within query execution(unused: see EXPLAIN PLAN) SQL engine must continue to maintain all indexes defined against a table regardless of whether they are used Index maintenance can present a significant CPU and I/O resource demand Open question: There is table with too many rows and there are many operations (including select, insert, update and delete) on it, what’s the index policies? (first: reconstruct table and index; second: dynamic index policy) Choosing Columns and Expressions to Index keys that are frequently used in WHERE clauses. keys that are frequently used to join tables in SQL statements. keys that have high selectivity (using the ANALYZE statement). Do not use standard B*-tree indexes on keys or expressions with few distinct values. Do not index columns that are frequently modified. Do not index keys that appear only in WHERE clauses with functions or operators (other than MIN or MAX). Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index. Choosing Composite Indexes Provide additional advantages over single-column indexes: Improved selectivity Sometimes two or more columns or expressions can be combined to form a composite index with more accurate selectivit