Database 版 (精华区)

发信人: rhine (风雨无阻), 信区: Database
标  题: Oracle DBA Guide, V8, part 14
发信站: 哈工大紫丁香 (2000年07月08日20:33:54 星期六), 站内信件

发信人: chendu (Oracle Only), 信区: Database
标  题: Oracle DBA Guide, V8, part 14
发信站: BBS 水木清华站 (Sat Nov 13 15:32:48 1999)

Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here.

Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.

Hoping everyone enjoy it!

Good luck!


----------------------------------------------------------------------------
----
14
Managing Indexes
This chapter describes various aspects of index management, and includes the
 following topics:
Guidelines for Managing Indexes
Creating Indexes
Altering Indexes
Monitoring Space Use of Indexes
Dropping Indexes
Before attempting tasks described in this chapter, familiarize yourself with
 the concepts in Chapter 10, Guidelines for Managing Schema Objects.
Guidelines for Managing Indexes
This section describes guidelines to follow when managing indexes, and inclu
des the following topics:
Create Indexes After Inserting Table Data
Limit the Number of Indexes per Table
Specify the Tablespace for Each Index
Specify Transaction Entry Parameters
Specify Index Block Space Use
Parallelize Index Creation
Consider Creating UNRECOVERABLE Indexes
Estimate Index Size and Set Storage Parameters
An index is an optional structure associated with tables and clusters, which
 you can create explicitly to speed SQL statement execution on a table. Just
 as the index in this manual helps you locate information faster than if the
re were no index, an Oracle index provides a faster access path to table dat
a.
a.
The absence or presence of an index does not require a change in the wording
 of any SQL statement. An index merely offers a fast access path to the data
; it affects only the speed of execution. Given a data value that has been i
ndexed, the index points directly to the location of the rows containing tha
t value.
Indexes are logically and physically independent of the data in the associat
ed table. You can create or drop an index at anytime without effecting the b
ase tables or other indexes. If you drop an index, all applications continue
 to work; however, access to previously indexed data might be slower. Indexe
s, being independent structures, require storage space.
Oracle automatically maintains and uses indexes after they are created. Orac
le automatically reflects changes to data, such as adding new rows, updating
 rows, or deleting rows, in all relevant indexes with no additional action b
y users.
See Also: For information about performance implications of index creation,
see Oracle8 Tuning.
For more information about indexes, see Oracle8 Concepts.
Create Indexes After Inserting Table Data
You should create an index for a table after inserting or loading data (via
SQL*Loader or Import) into the table. It is more efficient to insert rows of
 data into a table that has no indexes and then create the indexes for subse
quent access. If you create indexes before table data is loaded, every index
 must be updated every time a row is inserted into the table. You should als
o create the index for a cluster before inserting any data into the cluster.

When an index is created on a table that already has data, Oracle must use s
ort space. Oracle uses the sort space in memory allocated for the creator of
 the index (the amount per user is determined by the initialization paramete
r SORT_AREA_SIZE), but must also swap sort information to and from temporary
 segments allocated on behalf of the index creation.
If the index is extremely large, you may want to perform the following tasks
.
To Manage a Large Index
Create a new temporary segment tablespace.
Alter the index creator's temporary segment tablespace.
Create the index.
Remove the temporary segment tablespace and re-specify the creator's tempora
ry segment tablespace, if desired.
See Also: Under certain conditions, data can be loaded into a table with SQL
*Loader's "direct path load" and an index can be created as data is loaded;
see Oracle8 Utilities for more information.
Limit the Number of Indexes per Table
A table can have any number of indexes. However, the more indexes there are,
 the more overhead is incurred as the table is modified. Specifically, when
 the more overhead is incurred as the table is modified. Specifically, when
rows are inserted or deleted, all indexes on the table must be updated as we
ll. Also, when a column is updated, all indexes that contain the column must
 be updated.
Thus, there is a trade-off between the speed of retrieving data from a table
 and the speed of updating the table. For example, if a table is primarily r
ead-only, having more indexes can be useful; but if a table is heavily updat
ed, having fewer indexes may be preferable.
Specify Transaction Entry Parameters
By specifying the INITRANS and MAXTRANS parameters during the creation of ea
ch index, you can affect how much space is initially and can ever be allocat
ed for transaction entries in the data blocks of an index's segment.
See Also: For more information about setting these parameters, see "Setting
Storage Parameters".
Specify Index Block Space Use
When an index is created for a table, data blocks of the index are filled wi
th the existing values in the table up to PCTFREE. The space reserved by PCT
FREE for an index block is only used when a new row is inserted into the tab
le and the corresponding index entry must be placed in the correct index blo
ck (that is, between preceding and following index entries). If no more spac
e is available in the appropriate index block, the indexed value is placed i
n another index block. Therefore, if you plan on inserting many rows into an
 indexed table, PCTFREE should be high to accommodate the new index values.
If the table is relatively static without many inserts, PCTFREE for an assoc
iated index can be low so that fewer blocks are required to hold the index d
ata.
See Also: PCTUSED cannot be specified for indexes. See "Managing Space in Da
ta Blocks" for information about the PCTFREE parameter.
Specify the Tablespace for Each Index
Indexes can be created in any tablespace. An index can be created in the sam
e or different tablespace as the table it indexes.
If you use the same tablespace for a table and its index, then database main
tenance may be more convenient (such as tablespace or file backup and applic
ation availability or update) and all the related data will always be online
 together.
Using different tablespaces (on different disks) for a table and its index p
roduces better performance than storing the table and index in the same tabl
espace, due to reduced disk contention.
If you use different tablespaces for a table and its index and one tablespac
e is offline (containing either data or index), then the statements referenc
ing that table are not guaranteed to work.
Parallelize Index Creation
If you have the parallel query option installed, you can parallelize index c
reation. Because multiple processes work together to create the index, Oracl
e can create the index more quickly than if a single server process created
e can create the index more quickly than if a single server process created
the index sequentially.
When creating an index in parallel, storage parameters are used separately b
y each query server process. Therefore, an index created with an INITIAL of
5M and a PARALLEL DEGREE of 12 consumes at least 60M of storage during index
 creation.
See Also: For more information on the parallel query option and parallel ind
ex creation, see Oracle8 Tuning.
Consider Creating UNRECOVERABLE Indexes
You can create an index without generating any redo log records by specifyin
g UNRECOVERABLE in the CREATE INDEX statement.
----------------------------------------------------------------------------
----
Note:
Because indexes created using UNRECOVERABLE are not archived, you should per
form a backup after you create the index.
----------------------------------------------------------------------------
----
Creating an unrecoverable index has the following benefits:
Space is saved in the redo log files.
The time it takes to create the index is decreased.
Performance improves for parallel creation of large indexes.
In general, the relative performance improvement is greater for larger unrec
overable indexes than for smaller ones. Creating small unrecoverable indexes
 has little affect on the time it takes to create an index. However, for lar
ger indexes the performance improvement can be significant, especially when
you are also parallelizing the index creation.
Estimate Index Size and Set Storage Parameters
Appendix A, "Space Estimations for Schema Objects", contains equations that
help estimate the size of indexes.
Estimating the size of an index before creating one is useful for the follow
ing reasons:
You can use the combined estimated size of indexes, along with estimates for
 tables, rollback segments, and redo log files, to determine the amount of d
isk space that is required to hold an intended database. From these estimate
s, you can make correct hardware purchases and other decisions.
You can use the estimated size of an individual index to better manage the d
isk space that the index will use. When an index is created, you can set app
ropriate storage parameters and improve I/O performance of applications that
 use the index.
For example, assume that you estimate the maximum size of a table before cre
ating it. If you then set the storage parameters when you create the table,
fewer extents will be allocated for the table's data segment, and all of the
 table's data will be stored in a relatively contiguous section of disk spac
e. This decreases the time necessary for disk I/O operations involving this
e. This decreases the time necessary for disk I/O operations involving this
table.
The maximum size of a single index entry is roughly one-half the data block
size minus some overhead.
As with tables, you can explicitly set storage parameters when creating an i
ndex. If you explicitly set the storage parameters for an index, try to stor
e the index's data in a small number of large extents rather than a large nu
mber of small extents.
See Also: For specific information about storage parameters, see "Setting St
orage Parameters".
For specific information about estimating index size, see Appendix A, "Space
 Estimations for Schema Objects".
Considerations Before Disabling or Dropping Constraints
Because unique and primary keys have associated indexes, you should factor i
n the cost of dropping and creating indexes when considering whether to disa
ble or drop a UNIQUE or PRIMARY KEY constraint. If the associated index for
a UNIQUE key or PRIMARY KEY constraint is extremely large, you may save time
 by leaving the constraint enabled rather than dropping and re-creating the
large index.
Creating Indexes
This section describes how to create an index, and includes the following to
pics:
Creating an Index Associated with a Constraint
Creating an Index Explicitly
Re-creating an Existing Index
To enable a UNIQUE key or PRIMARY KEY (which creates an associated index), t
he owner of the table needs a quota for the tablespace intended to contain t
he index, or the UNLIMITED TABLESPACE system privilege.
LONG and LONG RAW columns cannot be indexed.
Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint by creating
 a unique index on the unique key or primary key. This index is automaticall
y created by Oracle when the constraint is enabled; no action is required by
 the issuer of the CREATE TABLE or ALTER TABLE statement to create the index
. This includes both when a constraint is defined and enabled, and when a de
fined but disabled constraint is enabled.
In general, it is better to create constraints to enforce uniqueness than it
 is to use the CREATE UNIQUE INDEX syntax. A constraint's associated index a
lways assumes the name of the constraint; you cannot specify a specific name
 for a constraint index.
If you do not specify storage options (such as INITIAL and NEXT) for an inde
x, the default storage options of the host tablespace are automatically used
.
Creating an Index Associated with a Constraint
You can set the storage options for the indexes associated with UNIQUE key a
nd PRIMARY KEY constraints using the ENABLE clause with the USING INDEX opti
ng the ENABLE clause with the USING INDEX opti
 an index in this manner allows you to change storage characteristics or mov
e to a new tablespace. Re-creating an index based on an existing data source
 also removes intra-block fragmentation. In fact, compared to dropping the i
ndex and using the CREATE INDEX command, re-creating an existing index offer
s better performance.
Issue the following statement to re-create an existing index:
ALTER INDEX index name REBUILD;
The REBUILD clause must immediately follow the index name, and precede any o
ther options. Also, the REBUILD clause cannot be used in conjunction with th
e DEALLOCATE STORAGE clause.
See Also: For more information on the ALTER INDEX command and optional claus
es, see the Oracle8 SQL Reference.
Altering Indexes
To alter an index, your schema must contain the index or you must have the A
LTER ANY INDEX system privilege. You can alter an index only to change the t
ransaction entry parameters or to change the storage parameters; you cannot
change its column structure.
Alter the storage parameters of any index, including those created by Oracle
 to enforce primary and unique key integrity constraints, using the SQL comm
and ALTER INDEX. For example, the following statement alters the EMP_ENAME i
ndex:
ALTER INDEX emp_ename
      INITRANS 5
      INITRANS 5
      MAXTRANS 10
      STORAGE (PCTINCREASE 50);
When you alter the transaction entry settings (INITRANS, MAXTRANS) of an ind
ex, a new setting for INITRANS only applies to data blocks subsequently allo
cated, while a new setting for MAXTRANS applies to all blocks (currently and
 subsequently allocated blocks) of an index.
The storage parameters INITIAL and MINEXTENTS cannot be altered. All new set
tings for the other storage parameters affect only extents subsequently allo
cated for the index.
For indexes that implement integrity constraints, you can also adjust storag
e parameters by issuing an ALTER TABLE statement that includes the ENABLE cl
ause with the USING INDEX option. For example, the following statement chang
es the storage options of the index defined in the previous section:
ALTER TABLE emp
     ENABLE PRIMARY KEY USING INDEX
     PCTFREE 5;
Monitoring Space Use of Indexes
If key values in an index are inserted, updated, and deleted frequently, the
 index may or may not use its acquired space efficiently over time. Monitor
an index's efficiency of space usage at regular intervals by first analyzing
 the index's structure and then querying the INDEX_STATS view:
SELECT pct_used FROM sys.index_stats WHERE name = 'indexname';
The percentage of an index's space usage will vary according to how often in
dex keys are inserted, updated, or deleted. Develop a history of an index's
average efficiency of space usage by performing the following sequence of op
erations several times: validating the index, checking PCT_USED, and droppin
g and re-creating the index. When you find that an index's space usage drops
 below its average, you can condense the index's space by dropping the index
 and re-creating or rebuilding it.
See Also: For information about analyzing an index's structure, see "Analyzi
ng Tables, Indexes, and Clusters".
Dropping Indexes
To drop an index, the index must be contained in your schema, or you must ha
ve the DROP ANY INDEX system privilege.
You might want to drop an index for any of the following reasons:
The index is no longer required.
The index is not providing anticipated performance improvements for queries
issued against the associated table. (For example, the table might be very s
mall, or there might be many rows in the table but very few index entries.)
Applications do not use the index to query the data.
The index has become invalid and must be dropped before being rebuilt.
The index has become too fragmented and must be dropped before being rebuilt
.
When you drop an index, all extents of the index's segment are returned to t
When you drop an index, all extents of the index's segment are returned to t
he containing tablespace and become available for other objects in the table
space.
How you drop an index depends on whether you created the index explicitly wi
th a CREATE INDEX statement, or implicitly by defining a key constraint on a
 table.
----------------------------------------------------------------------------
----
Note:
If a table is dropped, all associated indexes are dropped automatically.
----------------------------------------------------------------------------
----
You cannot drop only the index associated with an enabled UNIQUE key or PRIM
ARY KEY constraint. To drop a constraint's associated index, you must disabl
e or drop the constraint itself.
DROP INDEX emp_ename;
See Also: For information about analyzing indexes, see "Analyzing Tables, In
dexes, and Clusters".
For more information about dropping a constraint's associated index, see "Ma
naging Integrity Constraints".
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
--

--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.166]

--
           海纳百川，
                   有容乃大，
                           壁立千尺，
                                   无欲则刚。    

※ 来源:·哈工大紫丁香 bbs.hit.edu.cn·[FROM: dip.hit.edu.cn]