Oracle 11g数据库索引专家指南：提升查询性能

需积分: 9 12 浏览量更新于2024-07-26 收藏 2.16MB PDF 举报

"Apress.Expert.Indexing.in.Oracle.Database.11g.Jan.2012.pdf" 是一本深入讲解Oracle数据库11g索引技术的专业书籍。该书详细阐述了如何在Oracle环境中有效地利用和管理索引，以提升查询性能。索引作为数据库对象的一个可选组件，其核心功能是加速数据检索过程，类比于书籍中的目录，帮助用户快速定位到相关信息。第一章"OracleIndexes"是全书的起点，介绍了索引的基本概念，解释了为何在数据库中创建索引以及它对查询效率的重要性。索引通过将数据行与特定值关联起来，使得当执行查询时，系统可以直接查找索引而无需扫描整个表，从而节省时间和资源。第二章至第五章分别探讨了不同类型的索引结构：B-tree索引是最常见的索引类型，以其高效的数据组织和搜索能力著称；Bitmap索引适合处理大量重复数据，但对空间需求较大；Index-Organized Tables (IOT) 将索引和数据存储在一起，提供更高效的读取操作；而Specialized Indexes则是针对特定场景设计的高级索引技术，如位图索引和分区索引。第六章详细讨论了分区索引，这是一种将大表分割成多个物理部分的方法，每个部分都有独立的索引，提高了大型数据集的查询性能。这章节还涵盖了如何根据数据分布和查询模式来优化分区策略。第七章关注索引的使用优化，包括如何选择合适的索引类型、调整索引统计信息、理解索引覆盖和非覆盖查询，以及如何通过分析工具（如SQL Tuning Advisor）来改善查询性能。第八章涵盖了索引的维护，包括重建、修复、合并和删除索引等操作，以及如何在不同情况下平衡性能和资源消耗。第九章和第十章着重于SQL Tuning Advisor和SQL Access Advisor，前者是Oracle数据库自带的自动调优工具，用于检测和优化SQL查询，后者则帮助开发者理解和改进查询计划，确保数据访问效率。这本书提供了丰富的实践经验和理论知识，对于Oracle数据库管理员、开发人员以及对提高查询性能感兴趣的读者来说，是一本宝贵的参考资料。通过阅读这本书，读者可以深入了解Oracle 11g中索引的各个方面，并掌握如何在实际项目中应用和优化这些技术。

CHAPTER 1 ■ ORACLE INDEXES

 Note Domain indexes, B-tree cluster indexes, and hash clusters are not covered extensively in this book. If you

need more information regarding these index types, see Oracle’s SQL Reference Guide at

http://otn.oracle.com.

Determining Which Columns to Index

Now we turn our attention to figuring out which columns should be indexed. For starters, we

recommend for most applications you create indexes in the following situations:

• Define a primary key constraint for each table: This results in an index

automatically being created on the columns specified in the primary key.

• Create unique key constraints on columns that are required to be unique and are

different from the primary key columns: Each unique key constraint results in an

index automatically being created on the columns specified in the constraint.

• Manually create indexes on foreign key columns: This is done for better

performance to avoid certain locking issues (see Chapter 2 for complete details).

Each of these bulleted items is detailed in the following subsections.

 Tip See Chapter 9 for obtaining indexing advice from the SQL Tuning Advisor. See Chapter 10 for generating

indexing recommendations from the SQL Access Advisor.

Indexes on Primary Key and Unique Key Columns

In most situations, you should create a primary key constraint for every table. If there is not already an

index defined on the primary key columns, then Oracle will automatically create a B-tree index for you.

Similarly, for any unique key constraints you define on a table, if there is not already an index

defined on the unique key columns, Oracle will create an appropriate B-tree index. Here’s a simple

example:

create table cust

(cust_id number primary key

,last_name varchar2(30)

,first_name varchar2(30)

,ssn varchar2(16) unique);

See Chapter 2 for complete details regarding primary key and unique key constraints and how they

related to indexes.

CHAPTER 1 ■ ORACLE INDEXES

Indexes on Foreign Key Columns

Oracle doesn’t automatically create indexes on foreign key columns. We recommend that you do create

B-tree indexes on foreign key columns, one reason being that foreign key columns are often referenced

in WHERE clauses and therefore performance can be improved with these queries

When indexes exist on foreign key columns this also reduces locking issues. Namely, when inserting

or deleting from the child table, this will put a table level lock on the parent table, which will prevent

other processes from inserting or deleting from the parent table. In OLTP databases, this can be an issue

when you have multiple processes inserting and deleting data simultaneously from parent and child

tables. In data warehouse environments, it’s less of an issue because data is loaded in a more systematic

manner (scheduled batch jobs) and data is typically not deleted.

Here’s a simple example of creating a table that has a foreign key and then manually creating an

index:

create table address

(address_id number primary key

,cust_id number references cust(cust_id)

,address varchar2(1000)

);

create index address_fk1 on address(cust_id);

Other Suitable Columns

When choosing an index, keep in mind this basic rule: the indexing strategy should be based on the

columns you use when querying the table. You can create more than one index on a table and have an

index contain multiple columns. You will make better decisions if you first consider what types of

queries you execute on a table. If you’ve identified a poorly performing SQL query, also consider creating

indexes for the following columns:

• Create indexes on columns used often as predicates in the WHERE clause; when

multiple columns from a table are used in the WHERE clause, consider using a

concatenated (multi-column) index.

• Create a covering index on columns used in the SELECT clause.

• Consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or

DISTINCT clauses.

Oracle allows you to create an index that contains more than one column. Multicolumn indexes are

known as concatenated indexes (sometimes referred to as composite indexes). These indexes are

especially effective when you often use multiple columns in the WHERE clause when accessing a table.

Concatenated indexes are oftentimes more efficient in this situation than creating separate single

column indexes.

Columns included in the SELECT and WHERE clauses are also potential candidates for indexes. Recall

that a covering index is one that includes all columns returned by the query. In this situation, Oracle can

use the index structure itself (and not the table) to satisfy the results of the query. Also, if the column

values are selective enough, Oracle can use an index on columns referenced in the WHERE clause to

improve query performance.

Also consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or DISTINCT

clauses. This may result in more efficient queries that frequently use these SQL constructs.

CHAPTER 1 ■ ORACLE INDEXES

It’s okay to have multiple indexes per table. However, the more indexes you place on a table, the

slower DML statements will run (as Oracle has more and more indexes to maintain when the table

column values change). Don’t fall into the trap of randomly adding indexes to a table until you stumble

upon the right combination of indexed columns. Rather, verify the performance of an index before you

create it in a production environment. (See Chapter 7 for details on validating performance benefits.)

 Note You can have a column appear in multiple indexes on the same table. However, Oracle doesn’t allow

multiple indexes in one table on the exact same combination of columns.

Indexing Guidelines

Oracle indexes provide efficient access to large data sets. Deciding on using an index involves

determining whether the improvement in performance SELECT statements is worth the cost of space

consumed and overhead when the table is updated. Table 1-2 summarizes the guidelines for efficiently

using indexes.

Table 1-2. Guidelines for Creating Indexes

Guideline Reasoning

Create as many indexes as you need, but try to keep

the number to a minimum. Add indexes

judiciously. Test first to determine quantifiable

performance gains.

Indexes increase performance, but also consume

disk space and processing resources. Don’t add

indexes unnecessarily.

The required performance of queries you execute

against a table should form the basis of your

indexing strategy.

Indexing columns used in SQL queries will help

performance the most.

Consider using the SQL Tuning Advisor or the SQL

Access Advisor for indexing recommendations.

These tools provide recommendations and a

second set of eyes on your indexing decisions.

Create primary key constraints for all tables. This will automatically create a B-tree index (if

the columns in the primary key aren’t already

indexed).

Create unique key constraints where appropriate. This will automatically create a B-tree index (if

the columns in the unique key aren’t already

indexed).

Create indexes on foreign-key columns. Foreign-key columns are usually included in the

WHERE clause when joining tables and thus

improve performance of SQL SELECT statements.

Creating a B-tree index on foreign key columns

CHAPTER 1 ■ ORACLE INDEXES

also reduces locking issues when updating and

inserting into child tables.

Carefully select and test indexes on small tables

(small being less than a few thousand rows).

Even on small tables, indexes can sometimes

perform better than full table scans.

Use the correct type of index. Correct index usage maximizes performance. See

Table 1-1 for more details.

Use the basic B-tree index type if you don’t have a

verifiable performance gain from using a different

index type.

B-tree indexes are suitable for most applications

where you have high cardinality column values.

Consider using bitmap indexes in data warehouse

environments.

These indexes are ideal for low cardinality

columns where the values aren’t updated often.

Bitmap indexes work well on foreign key columns

on Star schema fact tables where you often run

queries that use AND and OR join conditions.

Consider using a separate tablespace for indexes

(separate from tables).

Table and index data may have different storage

and/or backup and recovery requirements. Using

separate tablespaces lets you manage indexes

separately from tables.

Let the index inherit its storage properties from the

tablespace.

This makes it easier to manage and maintain

index storage.

Use consistent naming standards. This makes maintenance and troubleshooting

easier.

Don’t rebuild indexes unless you have a solid

reason to do so.

Rebuilding indexes is generally unnecessary

unless an index is corrupt or you want to move an

index to different tablespace.

Monitor your indexes, and drop indexes that aren’t

used.

Doing this frees up physical space and improves

the performance of data manipulation language

(DML) statements.

Before dropping an index, consider marking it as

unusable or invisible.

This allows you to better determine if there are

any performance issues before you drop the

index. These options let you rebuild or re-enable

the index without requiring the data definition

language (DDL) creation statement.

Refer to these guidelines as you create and manage indexes in your databases. These

recommendations are intended to help you correctly use index technology.

剩余264页未读，继续阅读

clark37211

粉丝: 14

Oracle 11g数据库索引专家指南：提升查询性能

《Apress.Expert.JavaScript》书籍深度解读

《Expert Oracle Database 11g Administration》：2009年权威指南

《Apress.Pro.Expression.Blend.4》：精通微软WPF与Silverlight开发的全面指南

Apress.Expert.Performance.Indexing.for.SQL.Server.2012.Jul.2012

Apress.Expert.Oracle.Database.11g.Administration.2009.rar

Apress.Expert.Oracle9i.Database.Administration

Apress.Practical.DWR.2.Projects.Expert's.Voice.in.Java.Jan.2008.pdf

Apress.Expert.Oracle.Database.Architecture.2nd.Edition

《Oracle9i&10g编程艺术》完整英文版PDF(apress.expert.oracle.database.architecture.9i.and.10g.programming.techniques.and.solutions.sep.2005.)

Apress.Expert.Oracle.Database.Architecture.2nd.Edition.Jul.2010.rar

最新资源