没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
iv
Contents at a Glance
Contents .................................................................................................................... v
About the Authors .................................................................................................. xiii
About the Technical Reviewer ............................................................................... xiv
Acknowledgments .................................................................................................. xv
Chapter 1: Oracle Indexes .........................................................................................1
Chapter 2: B-tree Indexes .......................................................................................19
Chapter 3: Bitmap Indexes .....................................................................................49
Chapter 4: Index-Organized Tables ........................................................................69
Chapter 5: Specialized Indexes ..............................................................................85
Chapter 6: Partitioned Indexes .............................................................................115
Chapter 7: Tuning Index Usage .............................................................................141
Chapter 8: Maintaining Indexes ............................................................................171
Chapter 9: SQL Tuning Advisor .............................................................................205
Chapter 10: SQL Access Advisor ...........................................................................233
Index .....................................................................................................................249
CHAPTER 1
■ ■ ■
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
2
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.
剩余264页未读,继续阅读
sidy_db
- 粉丝: 15
- 资源: 31
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
- 1
- 2
前往页