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

5星 · 超过95%的资源需积分: 10 162 浏览量更新于2024-07-26 收藏 4.12MB PDF 举报

《Expert Indexing in Oracle Database 11g》是一本由Apress出版的专业书籍，主要针对Oracle数据库11g中的索引技术进行深入探讨。该书旨在帮助读者理解和优化数据库性能，通过创建和管理各种类型的索引来提升查询效率。索引在数据库中起着至关重要的作用，如同书籍的目录，它将数据项与物理位置关联起来，使得数据检索变得更快速、高效。书中涵盖了多个关键章节，包括： 1. **第1章：Oracle索引** - 对索引的基本概念进行了介绍，阐述了索引的创建目的，即为了加速查询操作，使其类似于书籍中的索引功能，帮助用户更快定位到所需的数据。 2. **第2章：B-树索引** - 详细讲解了B-树索引结构，这是Oracle中最常用的索引类型，其优点在于查找速度快，支持范围查询。 3. **第3章：位图索引** - 介绍了位图索引，这种索引适用于数据分布均匀且查询涉及大量数据范围的情况，但对插入和更新操作的支持相对较低。 4. **第4章：索引组织表（Index-Organized Tables, IOTs）** - 这种特殊类型的表将索引与数据存储在一起，提供了更高效的访问速度，适合频繁的全表扫描。 5. **第5章：专门化索引** - 包括了如分区索引、函数索引等高级索引类型，针对特定场景提供定制化的性能优化。 6. **第6章：分区索引** - 针对大型数据库，讲述了如何利用分区来分割索引，提高查询效率和管理复杂性。 7. **第7章：调优索引使用** - 介绍了如何评估和调整索引策略，以达到最佳查询性能，包括索引选择、创建和维护的策略。 8. **第8章：维护索引** - 讲解了索引的日常管理和维护任务，包括索引重建、清理和优化等。 9. **第9章：SQL Tuning Advisor** - 提供了工具和技术，用于自动分析查询性能并提出改进建议。 10. **第10章：SQL Access Advisor** - 探讨了如何利用这个功能来优化SQL语句，以充分利用索引。最后，本书还附有详尽的索引，方便读者快速查找相关内容。对于希望深入了解Oracle数据库索引技术和性能调优的IT专业人士来说，这本书是一个不可或缺的参考资料。无论是初次接触还是经验丰富的数据库管理员，都能从中获益良多。

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页未读，继续阅读

kaijiemn

粉丝: 0

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

Apress.Expert.Indexing.in.Oracle.Database.11g.Jan.2012.pdf

Expert.Oracle.Indexing.and.Access.Paths.2nd.epub

ORACLE 19c 考试号：1Z0-082

Oracle数据库11g索引专家指南

Oracle 11g索引优化经典指南：提升数据库性能

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

优化Oracle数据库性能：索引与访问路径策略

专家级Oracle数据库架构：高性能与生产力策略

Quest Toad for Oracle DBA Suite: 简化数据库管理与优化

AI从头到脚详解如何创建部署Azure Web App的OpenAI项目源码

最新资源