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

5星 · 超过95%的资源需积分: 9 144 浏览量更新于2024-07-21 收藏 2.39MB PDF 举报

Oracle索引技术是IT领域中的关键知识点，特别是在数据库管理中发挥着至关重要的作用。《Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database》是一本权威且经典的书籍，由Darl Kuhn、Sam R. Alapati和Bill Padfield合著，针对Oracle 11g版本，专为提升数据库性能而设计。本书深入讲解了索引在Oracle数据库中的各种类型和设计策略，帮助读者理解和优化数据查询效率。索引是数据库管理系统中用于加速数据检索的技术，通过预先对表中的数据进行排序和存储，使得特定字段的查找速度显著提升。书中首先会介绍基础的索引概念，包括B树索引、位图索引、哈希索引以及函数索引等，每种类型的索引都有其适用场景和优缺点。理解这些索引类型对于选择正确的索引策略至关重要。索引设计是本书的核心部分，它涵盖了如何根据业务需求和查询模式来创建、维护和调整索引。作者会讲解如何衡量索引的有效性，比如选择合适的索引策略（如覆盖索引），避免不必要的开销（如过度索引）。此外，还会讨论如何处理频繁更改的数据，以及如何处理大型表和分区表的索引优化问题。在性能优化方面，本书可能会涵盖索引维护的最佳实践，包括定期重建、合并和重新组织索引，以及如何利用Oracle提供的分析工具（如SQL Tuning Advisor）进行调优。此外，书中还会探讨如何在并发环境下管理和保护索引，以确保数据一致性。最后，版权信息强调了所有内容受版权保护，只能在法律许可的范围内复制或使用，例如在学术研究或个人计算机系统上作为专有材料使用。了解并尊重版权法律对于合法使用和传播这本书的内容至关重要。《Expert Indexing in Oracle Database 11g》是一本深入剖析Oracle索引技术的实用指南，适合数据库管理员、开发人员和性能优化专家阅读，能帮助读者提高Oracle数据库的性能和效率。通过学习本书，读者将掌握如何设计、维护和利用索引来最大化数据库的运行效能。

■ ACKNOWLEDGMENTS

xvi

This is the second book that I wrote with Bill and Darl, and I’m truly fortunate to have had the

opportunity of working with such great professionals on this project. Both of them are superb Oracle

database administrators and they’re also personally great. Constant cheer and good humor on behalf of

my two co-authors, not to speak of their extreme generosity and willingness when I requested their

assistance, has made writing this book a very cheerful task.

I’d like to (quite belatedly) acknowledge the great help provided in my career by Ram Janardhanan

and Anil Sinha of Citicorp, New York.

As is usual when I write a book, my family has made quite a few sacrifices to enable me to put my

best possible effort into the planning and writing of the book. I gratefully acknowledge the wonderful

support and help from my wife, Valerie, and my children Shannon, Nicholas, and Nina. Finally, I’d like

to thank my other family: my mother, Swarna Kumari; my father, Appa Rao; my brothers, Hari Hara

Prasad and Siva Sankara Prasad; as well as Aruna, Vanaja, Ashwin, Teja, Aparna, and Soumya for their

constant support, encouragement, affection, and love.

Sam R. Alapati

I’d like to thank my gracious co-authors, Sam R. Alapati and Darl Kuhn, for all of their help and support

and for taking on a rookie for this project. I couldn’t have made it without their help.

There are so many people I can thank that have helped me over the years in my career, so please

know that I appreciate every single individual who has encouraged and helped me along. First of all, I’d

like to thank Bob Ranney for giving me the opportunity to be a DBA. I also would like to thank some of

my key managers over the years that have helped me, including Beth Bowen, Larry Wyzgala, John

Zlamal, Linda Scheldrup, Amy Neff, and Maureen Frazzini.

Of course, there are many DBAs, developers, system administrators, and architects that have helped

me greatly in my career. First, I need to thank the DBAs on my current team who make the everyday

grind a blast. These folks have helped me so much professionally and have become great friends over the

many years we have worked together. This includes Dave Carter, Debbie Fitzgerald, Pankaj Guleria, Pete

Sardaczuk, Brad Strom, and Rebecca Western.

Over the years, I’ve learned an awful lot from the following folks, who have always been generous

with their time and help, and patient with my questions: Mark Nold, Mick McMahon, Sandra Montijo,

Jerry Sanderson, Glen Sanderson, Jose Fernandez, Mike Hammontre, Pat Cain, Dave Steep, Gary

Whiting, Ron Fullmer, Becky Enter, John Weber, Avanish Gupta, Scott Bunker, Paul Mayes, Bill Read,

Rod Ermish, Rick Barry, Sun Yang, Sue Wagner, Glenn Balanoff, Linda Lee Burau, Deborah Lieou-

McCall, Bob Zumpf, Kristi Sargent, Sandy Hass, George Huner, Pad Kail, Curtis Gay, Ross Bartholomay,

Carol Rosenow, Scott Richards, Sheryl Gross, Lachelle Shambe, John Piel, Rob Grote, Rex Ellis, Zane

Warton, Steve Pearson, Jim Barclay, Jason Hermstad, Shari Plantz-Masters, Denise Duncan, Bob Mason,

Brad Blake, Mike Nims, Cathie Wilson, Rob Coates, Shirley Amend, Rob Bushlack, Cindy Patterson,

Debbie Chartier, Blair Christensen, Meera Ganesan, Kedar Panda, Srivatsan Muralidaran, Kevin

Tomimatsu, John Townley, and Brent Wagner.

Bill Padfield

CHAPTER 1

■ ■ ■

Oracle Indexes

An index is an optionally created database object used primarily to increase query performance. The

purpose of a database index is similar to an index in the back of a book. A book index associates a topic

with a page number. When you’re locating information in a book, it’s usually much faster to examine the

index first, find the topic of interest, and identify associated page numbers. With this information, you

can navigate directly to specific page numbers in the book. If the topic only appears on a few pages

within the book, then the number of pages to read is minimal. In this manner, the usefulness of the

index decreases with an increase in the number of times a topic appears in a book.

Similar to a book index, a database index stores the column value of interest along with its row

identifier (ROWID). The ROWID contains the physical location of the table row on disk that stores the

column value. With the ROWID in hand, Oracle can efficiently retrieve table data with a minimum of disk

reads. In this way, indexes function like a shortcut to the table data. If there is no available index, then

Oracle reads each row in the table to determine if the row contains the desired information.



Note In addition to improving performance, Oracle uses indexes to help enforce enabled primary key and

unique key constraints. Additionally, Oracle can better manage table locking scenarios when indexes are placed on

foreign key columns.

While it’s possible to build a database application devoid of indexes, without them you’re almost

guaranteeing poor performance. Indexes allow for excellent scalability even with very large data sets. So

if indexes are so important to database performance, why not place them on all tables and column

combinations? The answer is short: indexes are not free. They consume disk space and system resources.

As column values are modified, any corresponding indexes must also be updated. In this way, indexes

use storage, I/O, CPU, and memory resources. A poor choice of indexes leads to wasted disk usage and

excessive consumption of system resources. This results in a decrease in database performance.

For these reasons, when you design and build an Oracle database application, expert consideration

must be given to your indexing strategy. As an application architect, you must understand the physical

properties of an index, what types of indexes are available, and strategies for choosing which table and

column combinations to index. A correct indexing methodology is central to achieving maximum

performance for your database.

This chapter introduces you to Oracle indexing concepts. We begin with a to-the-point example of

how an index improves query performance. We then explain index types available within Oracle and

CHAPTER 1 ■ ORACLE INDEXES

provide guidelines and recommendations for choosing which columns to index. If you’re new to indexes

or require a refreshing, start here.

Improving Performance with Indexes

How exactly does an index improve query performance? To understand how an index works, consider

the following simple example. Suppose you create a table to hold customer information, like so:

create table cust

(cust_id number

,last_name varchar2(30)

,first_name varchar2(30));

Your business grows quickly; after a short time, millions of customers are created. You run daily

reports against this table and notice that performance has progressively decreased when issuing queries

like this:

select cust_id, last_name, first_name

from cust

where last_name = 'STARK';

When there was hardly any data in the table, this query returned in sub-seconds. Now, with over a

million rows and growing, this query is taking longer and longer. What’s going on here?

When a SQL select statement executes, the Oracle query optimizer quickly calculates a step-by-step

execution plan detailing how it will retrieve column values specified in the query. In calculating the plan,

the optimizer determines which tables and indexes will be used to retrieve data.

When no index exists, the table itself is the only access path available to satisfy the results of the

query. In this scenario, Oracle has no choice but to inspect every row within every used block in the table

(this is known as a full table scan) to see if there are rows with the last name of STARK. As more data is

inserted into this table, the query takes longer. The cost of this query (as a measure of CPU, memory, and

I/O resources consumed) is proportional to the number of table blocks. The only way to make this query

run faster is to buy better hardware...or use a performance enhancing feature such as an index.

You can peak ahead in this chapter and determine that an index on columns that appear in the

WHERE clause of a SQL query might improve performance and decide to create an index on the CUST

table’s LAST_NAME column, like so:

create index cust_idx1

on cust(last_name);

This statement creates a B-tree index (more on this later). This is the default index type in Oracle.

After creating the index, the performance of queries selecting by last name returns to sub-second timing.

Life is good.

To understand how the index improves performance, recall that an index stores two types of

information: the value of the table column(s) and the corresponding ROWID. The ROWID uniquely

identifies a row (for heap-organized tables) within a database and contains its physical location (datafile,

block, and row position within block). Once the index is created and subsequent queries execute, the

query optimizer considers whether the index will reduce the amount of resources required to return the

results of the query.

CHAPTER 1 ■ ORACLE INDEXES



Tip The ROWID uniquely identifies a row for heap-organized tables. However, with table clusters, it is possible

to have rows in different tables that are physically located in the same block and have identical ROWIDs.

In the prior example, suppose there are millions of records in the CUST table but only one record in

the table with the last name of STARK. The query optimizer can inspect the index and within a few disk

reads locate the exact location (via the ROWID) of the one block within the table that contains the record

of interest. This results in very fast performance. In this case, it wouldn’t matter if there were millions

and millions more records in the table; as long as the value contained in the index is fairly unique, Oracle

will be able to return the required rows with a minimal amount of disk reads.

Conversely, consider if the value in the LAST_NAME column wasn’t very unique. Suppose millions of

records in the CUST table had the value of LEE. If the query optimizer did use the index, it would have to

read from the index millions of times, retrieve the ROWIDs, and then also read from the table millions of

times. In this situation, it’s faster to bypass the index and instead scan every block in the table. For this

reason, sometimes the optimizer calculates that the index isn’t beneficial to performance and ignores it.



Tip The higher the degree of uniqueness, the more efficient a B-tree index becomes. In database jargon, a very

selective (unique) column value compared to the total number of rows in a table is said to have high cardinality.

Conversely, low cardinality refers to few unique values compared to the total rows for the table.

There’s another interesting scenario we should point out. Suppose instead of selecting all column

values out of the CUST table, you only select the LAST_NAME column.

select last_name

from cust

where last_name = 'STARK';

In this scenario, since the index contains all of the values in the SELECT clause, Oracle is able to

satisfy the results of the query by only accessing the index. Oracle doesn’t have to read the table

structure itself. When the SELECT clause columns are all contained with an index, this is known as a

covering index. These indexes are particularly efficient because only the index blocks need to be read.

Before reading on, let’s review the concepts introduced up to this point in the chapter.

• Indexes are optional objects defined on a table and one or more columns.

• Indexes consume resources.

• A B-tree index is the default index type in Oracle.

• A fairly unique column value compared to all other rows in a table results in a

more efficient B-tree index.

• When appropriately created, indexes improve performance.

CHAPTER 1 ■ ORACLE INDEXES

• In some scenarios, the query optimizer will choose not to use an index. In other

words, the query optimizer calculates that the cost of a full table scan is less than

the cost when using an index.

• In some situations, Oracle can retrieve data for a query by only accessing the

index; the table doesn’t have to be accessed.

An understanding of these index fundamentals provide a good foundation for the rest of the

concepts introduced in this chapter and book. We now turn our attention to determining which type of

index to use.

Determining Which Type of Index to Use

Oracle provides a wide range of index types and features. The correct use of indexes results in well

performing and scalable database application. Conversely, if you incorrectly or unwisely implement a

feature, there may be detrimental performance implications. Table 1-1 summarizes the various Oracle

index types available. At first glance, this is a long list and may be somewhat overwhelming to somebody

new to Oracle. Deciding which index type to use isn’t as daunting as it might initially seem. For most

applications, you should simply use the default B-tree index type.



Note Several of the index types listed in Table 1-1 are actually just variations on the basic, B-tree index. A

reverse-key index, for example, is merely a B-tree index optimized for evenly spreading I/O when the index value

is sequentially generated and inserted with similar values.

Table 1-1.

Oracle Index Types and Feature Descriptions

Index Type Usage

B-tree Default, balanced tree index; good for high-cardinality (high degree of distinct

values) columns. Use a normal B-tree index unless you have a concrete reason

to use a different index type or feature.

Index organized table Efficient when most of the column values are included in the primary key. You

access the index as if it were a table. The data is stored in a B-tree like

structure.

Unique A form of B-tree index; used to enforce uniqueness in column values. Often

used with primary key and unique key constraints, but can be created

independently of constraints.

Reverse-key A form of B-tree index; useful to balance I/O in an index that has many

sequential inserts.

剩余262页未读，继续阅读

Dior-19

粉丝: 0
资源: 1

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

Oracle索引技术

ORACLE 索引技术

oracle索引技术（中）

oracle 索引技术（下）

oracle索引技术-（英文版）

性能调优——Oracle索引技术的应用与剖析

Oracle索引技术 ((美)Darl Kuhn) 中文.pdf

Oracle索引技术在油田数据管理中的研究与应用.pdf

Oracle索引技术在性能调优中的应用分析

Oracle 索引

最新资源