This chapter provides an overview of data access methods using indexes and clusters that can enhance or degrade performance.
The chapter contains the following sections:
To maintain optimal performance, drop indexes that an application is not using. You can find indexes that are not being used by using the
Also, indexes within an application sometimes have uses that are not immediately apparent from a survey of statement execution plans. An example of this is a foreign key index on a parent table, which prevents share locks from being taken out on a child table.
If you are deciding whether to create new indexes to tune statements, then you can also use the
When the statement is next parsed, the optimizer automatically chooses a new execution plan that could potentially use the new index. If you create new indexes on a remote database to tune a distributed statement, then the optimizer considers these indexes when the statement is next parsed.
Note that creating an index to tune one statement can affect the optimizer's choice of execution plans for other statements. For example, if you create an index to be used by one statement, then the optimizer can choose to use that index for other statements in the application as well. For this reason, reexamine the application's performance and execution plans, and rerun the SQL trace facility after you have tuned those statements that you initially identified for tuning.
A leading portion of an index is a set of one or more columns that
were specified first and consecutively in the list of columns in the
However, there are cases where it can be beneficial to use the base table instead of the existing index. Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table. In this case, it is faster to use the base table rather than the index to re-create the index.
Use the
Usually,
Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column already is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the
If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur, because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation. The enabled novalidated state prevents operations violating the constraint from being performed on the table.
An enabled novalidated constraint can be validated with a parallel, consistent-read query of the table to determine whether any data violates the constraint. No locking is performed, and the enable operation does not block readers or writers to the table. In addition, enabled novalidated constraints can be validated in parallel: Multiple constraints can be validated at the same time and each constraint's validity check can be determined using parallel query.
Use the following approach to create tables with constraints and indexes:
Defining a function-based index on the transformed column or expression allows that data to be returned using the index when that function or expression is used in a
Oracle treats descending indexes as function-based indexes. The columns marked
For example, function-based indexes defined with the
Oracle supports both range and hash partitioned global indexes. In a range partitioned global index, each index partition contains values defined by a partition bound. In a hash partitioned global index, each partition contains values determined by the Oracle hash function.
The hash method can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. In some OLTP applications, index insertions happen only at the right edge of the index. This could happen when the index is defined on monotonically increasing columns. In such situations right edge of the index becomes a hotspot because of contention for index pages, buffers, latches for update, and additional index maintenance activity, which results in performance degradation.
With hash partitioned global indexes index entries are hashed to different partitions based on partitioning key and the number of partitions. This spreads out contention over number of defined partitions, resulting in increased throughput. Hash-partitioned global indexes would benefit TPC-H refresh functions that are executed as massive PDMLs into huge fact tables because contention for buffer latches would be spread out over multiple partitions.
With hash partitioning, an index entry will be mapped to a particular index partition based on the hash value generated by Oracle. The syntax to create hash-partitioned global index is very similar to hash-partitioned table. Queries involving equality and
A parent/child relationship is an example of a situation that may warrant an index-organized table. For example, a members table has a child table containing phone numbers. Phone numbers for a member are changed and added over time. In a heap-organized table, rows are inserted in data blocks where they fit. However, when you query the members table, you always retrieve the phone numbers from the child table. To make the retrieval more efficient, you can store the phone numbers in an index-organized table so that phone records for a given member are inserted near each other in the data blocks.
In some circumstances, an index-organized table may provide a performance advantage over a heap-organized table. For example, if a query requires fewer blocks in the cache, then the database uses the buffer cache more efficiently. If fewer distinct blocks are needed for a query, then a single physical I/O may retrieve all necessary data, requiring a smaller amount of I/O for each query.
Global hash-partitioned indexes are supported for index-organized tables and can provide performance benefits in a multiuser OLTP environment. Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order.
Bitmap join indexes are much more efficient in storage than materialized join views, an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowids of the fact tables.
The cartridge determines the parameters you can specify in creating and maintaining the domain index. Similarly, the performance and storage characteristics of the domain index are presented in the specific cartridge documentation.
Refer to the appropriate cartridge documentation for information such as the following:
To create a cluster, use the
Follow these guidelines when deciding whether to cluster tables:
Follow these guidelines for choosing when to use hash clusters:
The chapter contains the following sections:
14.1 Understanding Index Performance
This section describes the following:14.1.1 Tuning the Logical Structure
Although query optimization helps avoid the use of nonselective indexes within query execution, the SQL engine must continue to maintain all indexes defined against a table, regardless of whether they are used. Index maintenance can present a significant CPU and I/O resource demand in any write-intensive application. In other words, do not build indexes unless necessary.To maintain optimal performance, drop indexes that an application is not using. You can find indexes that are not being used by using the
ALTER
INDEX
MONITORING
USAGE
functionality over a period of time that is representative of your
workload. This monitoring feature records whether or not an index has
been used. If you find that an index has not been used, then drop it.
Make sure you are monitoring a representative workload to avoid dropping
an index which is used, but not by the workload you sampled.Also, indexes within an application sometimes have uses that are not immediately apparent from a survey of statement execution plans. An example of this is a foreign key index on a parent table, which prevents share locks from being taken out on a child table.
See Also:
-
Oracle Database SQLQW Language Reference for information on the
ALTER
INDEX
MONITORING
USAGE
statement
-
Oracle Database Advanced Application Developer's Guide for information on foreign keys
EXPLAIN
PLAN
statement to determine whether the optimizer will choose to use these
indexes when the application is run. If you create new indexes to tune a
statement that is currently parsed, then Oracle invalidates the
statement.When the statement is next parsed, the optimizer automatically chooses a new execution plan that could potentially use the new index. If you create new indexes on a remote database to tune a distributed statement, then the optimizer considers these indexes when the statement is next parsed.
Note that creating an index to tune one statement can affect the optimizer's choice of execution plans for other statements. For example, if you create an index to be used by one statement, then the optimizer can choose to use that index for other statements in the application as well. For this reason, reexamine the application's performance and execution plans, and rerun the SQL trace facility after you have tuned those statements that you initially identified for tuning.
14.1.2 Index Tuning using the SQLAccess Advisor
The SQL Access Advisor is an alternative to manually determining which indexes are required. This advisor recommends a set of indexes when invoked from Oracle Enterprise Manager or run through theDBMS_ADVISOR
package APIs. The SQL Access Advisor either recommends using a workload
or it generates a hypothetical workload for a specified schema. Various
workload sources are available, such as the current contents of the SQL
Cache, a user defined set of SQL statements, or a SQL Tuning set. Given
a workload, the SQL Access Advisor generates a set of recommendations
from which you can select the indexes that are to be implemented. An
implementation script is provided which can be executed manually or
automatically through Oracle Enterprise Manager. For information on the
SQL Access Advisor, see "Overview of the SQL Access Advisor".14.1.3 Choosing Columns and Expressions to Index
A key is a column or expression on which you can build an index. Follow these guidelines for choosing keys to index:-
Consider indexing keys that are used frequently in
WHERE
clauses.
-
Consider indexing keys that are used frequently to join tables in SQL
statements. For more information on optimizing joins, see the section "Using Hash Clusters for Performance".
-
Choose index keys that have high selectivity. The selectivity of an index
is the percentage of rows in a table having the same value for the
indexed key. An index's selectivity is optimal if few rows have the same
value.
Note:Oracle automatically creates indexes, or uses existing indexes, on the keys and expressions of unique and primary keys that you define with integrity constraints.
-
Do not use standard B-tree indexes on keys or expressions with few
distinct values. Such keys or expressions usually have poor selectivity
and therefore do not optimize performance unless the frequently selected
key values appear less frequently than the other key values. You can
use bitmap indexes effectively in such cases, unless the index is
modified frequently, as in a high concurrency OLTP application.
-
Do not index columns that are modified frequently.
UPDATE
statements that modify indexed columns andINSERT
andDELETE
statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.
-
Do not index keys that appear only in
WHERE
clauses with functions or operators. AWHERE
clause that uses a function, other thanMIN
orMAX
, or an operator with an indexed key does not make available the access path that uses the index except with function-based indexes.
-
Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent
INSERT
,UPDATE
, andDELETE
statements access the parent and child tables. Such an index allowsUPDATE
s andDELETE
s on the parent table without share locking the child table.
-
When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for
INSERT
s,UPDATE
s, andDELETE
s and the use of the space required to store the index. You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.
See Also:Oracle Database Advanced Application Developer's Guide for more information on the effects of foreign keys on locking
14.1.4 Choosing Composite Indexes
A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:-
Improved selectivity
Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with higher selectivity.
-
Reduced I/O
If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.
Note:
This is no longer the case with index skip scans. See "Index Skip Scans".CREATE
INDEX
statement that created the index. Consider this CREATE
INDEX
statement:CREATE INDEX comp_ind ON table1(x, y, z);
-
x
,xy
, andxyz
combinations of columns are leading portions of the index
-
yz
,y
, andz
combinations of columns are not leading portions of the index
14.1.4.1 Choosing Keys for Composite Indexes
Follow these guidelines for choosing keys for composite indexes:-
Consider creating a composite index on keys that are used together frequently in
WHERE
clause conditions combined withAND
operators, especially if their combined selectivity is better than the selectivity of either key individually.
-
If several queries select the same set of keys based on one or more
key values, then consider creating a composite index containing all of
these keys.
14.1.4.2 Ordering Keys for Composite Indexes
Follow these guidelines for ordering keys in composite indexes:-
Create the index so the keys used in
WHERE
clauses make up a leading portion.
-
If some keys are used in
WHERE
clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.
-
If all keys are used in the
WHERE
clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.
14.1.5 Writing Statements That Use Indexes
Even after you create an index, the optimizer cannot use an access path that uses the index simply because the index exists. The optimizer can choose such an access path for a SQL statement only if it contains a construct that makes the access path available. To allow the query optimizer the option of using an index access path, ensure that the statement contains a construct that makes such an access path available.14.1.6 Writing Statements That Avoid Using Indexes
In some cases, you might want to prevent a SQL statement from using an access path that uses an existing index. You might want to do this if you know that the index is not very selective and that a full table scan would be more efficient. If the statement contains a construct that makes such an index access path available, then you can force the optimizer to use a full table scan through one of the following methods:-
Use the
NO_INDEX
hint to give the query optimizer maximum flexibility while disallowing the use of a certain index.
-
Use the
FULL
hint to instruct the optimizer to choose a full table scan instead of an index scan.
-
Use the
INDEX
orINDEX_COMBINE
hints to instruct the optimizer to use one index or a set of listed indexes instead of another.
See Also:Chapter 19, "Using Optimizer Hints" for more information on theNO_INDEX
,FULL
,INDEX
, andINDEX_COMBINE
and hints
14.1.7 Re-creating Indexes
You might want to re-create an index to compact it and minimize fragmented space, or to change the index's storage characteristics. When creating a new index that is a subset of an existing index or when rebuilding an existing index with new storage characteristics, Oracle might use the existing index instead of the base table to improve the performance of the index build.However, there are cases where it can be beneficial to use the base table instead of the existing index. Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table. In this case, it is faster to use the base table rather than the index to re-create the index.
Use the
ALTER
INDEX
... REBUILD
statement to reorganize or compact an existing index or to change its storage characteristics. The REBUILD
statement uses the existing index as the basis for the new one. All index storage statements are supported, such as STORAGE
(for extent allocation), TABLESPACE
(to move the index to a new tablespace), and INITRANS
(to change the initial number of entries).Usually,
ALTER
INDEX
... REBUILD
is faster than dropping and re-creating an index, because this
statement uses the fast full scan feature. It reads all the index blocks
using multiblock I/O, then discards the branch blocks. A further
advantage of this approach is that the old index is still available for
queries while the rebuild is in progress.
See Also:
Oracle Database SQLQW Language Reference for more information about the CREATE
INDEX
and ALTER
INDEX
statements and restrictions on rebuilding indexes14.1.8 Compacting Indexes
You can coalesce leaf blocks of an index by using theALTER
INDEX
statement with the COALESCE
option. This option lets you combine leaf levels of an index to free blocks for reuse. You can also rebuild the index online.
See Also:
Oracle Database SQLQW Language Reference and Oracle Database Administrator's Guide for more information about the syntax for this statement14.1.9 Using Nonunique Indexes to Enforce Uniqueness
You can use an existing nonunique index on a table to enforce uniqueness, either forUNIQUE
constraints or the unique aspect of a PRIMARY
KEY
constraint. The advantage of this approach is that the index remains
available and valid when the constraint is disabled. Therefore, enabling
a disabled UNIQUE
or PRIMARY
KEY
constraint does not require rebuilding the unique index associated with
the constraint. This can yield significant time savings on enable
operations for large tables.Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column already is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the
UNIQUE
key; otherwise, Oracle creates an additional unique index to enforce the constraint.14.1.10 Using Enabled Novalidated Constraints
An enabled novalidated constraint behaves similarly to an enabled validated constraint for new data. Placing a constraint in the enabled novalidated state signifies that any new data entered into the table must conform to the constraint. Existing data is not checked. By placing a constraint in the enabled novalidated state, you enable the constraint without locking the table.If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur, because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation. The enabled novalidated state prevents operations violating the constraint from being performed on the table.
An enabled novalidated constraint can be validated with a parallel, consistent-read query of the table to determine whether any data violates the constraint. No locking is performed, and the enable operation does not block readers or writers to the table. In addition, enabled novalidated constraints can be validated in parallel: Multiple constraints can be validated at the same time and each constraint's validity check can be determined using parallel query.
Use the following approach to create tables with constraints and indexes:
-
Create the tables with the constraints.
NOT
NULL
constraints can be unnamed and should be created enabled and validated. All other constraints (CHECK
,UNIQUE
,PRIMARY
KEY
, andFOREIGN
KEY
) should be named and created disabled.
Note:By default, constraints are created in theENABLED
state. -
Load old data into the tables.
-
Create all indexes, including indexes needed for constraints.
-
Enable novalidate all constraints. Do this to primary keys before foreign keys.
-
Allow users to query and modify data.
-
With a separate
ALTER
TABLE
statement for each constraint, validate all constraints. Do this to primary keys before foreign keys. For example,
CREATE TABLE t (a NUMBER CONSTRAINT apk PRIMARY KEY DISABLE, b NUMBER NOT NULL); CREATE TABLE x (c NUMBER CONSTRAINT afk REFERENCES t DISABLE);
t
.CREATE UNIQUE INDEX tai ON t (a); CREATE INDEX tci ON x (c); ALTER TABLE t MODIFY CONSTRAINT apk ENABLE NOVALIDATE; ALTER TABLE x MODIFY CONSTRAINT afk ENABLE NOVALIDATE;At this point, users can start performing
INSERT
s, UPDATE
s, DELETE
s, and SELECT
s on table t
.ALTER TABLE t ENABLE CONSTRAINT apk; ALTER TABLE x ENABLE CONSTRAINT afk;Now the constraints are enabled and validated.
See Also:
Oracle Database Concepts for a complete discussion of integrity constraints14.2 Using Function-based Indexes for Performance
A function-based index includes columns that are either transformed by a function, such as theUPPER
function, or included in an expression, such as col1
+ col2
. With a function-based index, you can store computation-intensive expressions in the index.Defining a function-based index on the transformed column or expression allows that data to be returned using the index when that function or expression is used in a
WHERE
clause or an ORDER
BY
clause. This allows Oracle to bypass computing the value of the expression when processing SELECT
and DELETE
statements. Therefore, a function-based index can be beneficial when
frequently-executed SQL statements include transformed columns, or
columns in expressions, in a WHERE
or ORDER
BY
clause.Oracle treats descending indexes as function-based indexes. The columns marked
DESC
are sorted in descending order.For example, function-based indexes defined with the
UPPER
(column_name
) or LOWER
(column_name
) keywords allow case-insensitive searches. The index created in the following statement:CREATE INDEX uppercase_idx ON employees (UPPER(last_name));facilitates processing queries such as:
SELECT * FROM employees WHERE UPPER(last_name) = 'MARKSON';
See Also:
-
Oracle Database Advanced Application Developer's Guide and Oracle Database Administrator's Guide for more information on using function-based indexes
-
Oracle Database SQLQW Language Reference for more information on the
CREATE
INDEX
statement
14.3 Using Partitioned Indexes for Performance
Similar to partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes).Oracle supports both range and hash partitioned global indexes. In a range partitioned global index, each index partition contains values defined by a partition bound. In a hash partitioned global index, each partition contains values determined by the Oracle hash function.
The hash method can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. In some OLTP applications, index insertions happen only at the right edge of the index. This could happen when the index is defined on monotonically increasing columns. In such situations right edge of the index becomes a hotspot because of contention for index pages, buffers, latches for update, and additional index maintenance activity, which results in performance degradation.
With hash partitioned global indexes index entries are hashed to different partitions based on partitioning key and the number of partitions. This spreads out contention over number of defined partitions, resulting in increased throughput. Hash-partitioned global indexes would benefit TPC-H refresh functions that are executed as massive PDMLs into huge fact tables because contention for buffer latches would be spread out over multiple partitions.
With hash partitioning, an index entry will be mapped to a particular index partition based on the hash value generated by Oracle. The syntax to create hash-partitioned global index is very similar to hash-partitioned table. Queries involving equality and
IN
predicates on index partitioning key can efficiently use global hash partitioned index to answer queries quickly.
See Also:
Oracle Database Concepts and Oracle Database Administrator's Guide for more information on global indexes tables14.4 Using Index-Organized Tables for Performance
An index-organized table differs from an ordinary table in that the data for the table is held in its associated index. Changes to the table data, such as adding new rows, updating rows, or deleting rows, result only in updating the index. Because data rows are stored in the index, index-organized tables provide faster key-based access to table data for queries that involve exact match or range search or both.A parent/child relationship is an example of a situation that may warrant an index-organized table. For example, a members table has a child table containing phone numbers. Phone numbers for a member are changed and added over time. In a heap-organized table, rows are inserted in data blocks where they fit. However, when you query the members table, you always retrieve the phone numbers from the child table. To make the retrieval more efficient, you can store the phone numbers in an index-organized table so that phone records for a given member are inserted near each other in the data blocks.
In some circumstances, an index-organized table may provide a performance advantage over a heap-organized table. For example, if a query requires fewer blocks in the cache, then the database uses the buffer cache more efficiently. If fewer distinct blocks are needed for a query, then a single physical I/O may retrieve all necessary data, requiring a smaller amount of I/O for each query.
Global hash-partitioned indexes are supported for index-organized tables and can provide performance benefits in a multiuser OLTP environment. Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order.
See Also:
Oracle Database Concepts and Oracle Database Administrator's Guide for more information on index-organized tables14.5 Using Bitmap Indexes for Performance
Bitmap indexes can substantially improve performance of queries that have all of the following characteristics:-
The
WHERE
clause contains multiple predicates on low- or medium-cardinality columns.
-
The individual predicates on these low- or medium-cardinality columns select a large number of rows.
-
The bitmap indexes used in the queries have been created on some or all of these low- or medium-cardinality columns.
-
The tables in the queries contain many rows.
WHERE
clauses. Bitmap indexes can also provide optimal performance for aggregate queries and for optimizing joins in star schemas.
See Also:
Oracle Database Concepts and Oracle Database Data Warehousing Guide for more information on bitmap indexing14.6 Using Bitmap Join Indexes for Performance
In addition to a bitmap index on a single table, you can create a bitmap join index, which is a bitmap index for the join of two or more tables. A bitmap join index is a space-saving way to reduce the volume of data that must be joined, by performing restrictions in advance. For each value in a column of a table, a bitmap join index stores the rowids of corresponding rows in another table. In a data warehousing environment, the join condition is an equi-inner join between the primary key column(s) of the dimension tables and the foreign key column(s) in the fact table.Bitmap join indexes are much more efficient in storage than materialized join views, an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowids of the fact tables.
See Also:
Oracle Database Data Warehousing Guide for examples and restrictions of bitmap join indexes14.7 Using Domain Indexes for Performance
Domain indexes are built using the indexing logic supplied by a user-defined indextype. An indextype provides an efficient mechanism to access data that satisfy certain operator predicates. Typically, the user-defined indextype is part of an Oracle option, like the Spatial option. For example, theSpatialIndextype
allows efficient search and retrieval of spatial data that overlap a given bounding box.The cartridge determines the parameters you can specify in creating and maintaining the domain index. Similarly, the performance and storage characteristics of the domain index are presented in the specific cartridge documentation.
Refer to the appropriate cartridge documentation for information such as the following:
-
What datatypes can be indexed?
-
What indextypes are provided?
-
What operators does the indextype support?
-
How can the domain index be created and maintained?
-
How do we efficiently use the operator in queries?
-
What are the performance characteristics?
Note:You can also create index types with theCREATE
INDEXTYPE
statement.
14.8 Using Clusters for Performance
Clusters are groups of one or more tables that are physically stored together because they share common columns and usually are used together. Because related rows are physically stored together, disk access time improves.To create a cluster, use the
CREATE
CLUSTER
statement.
See Also:
Oracle Database Concepts for more information on clusters-
Cluster tables that are accessed frequently by the application in join statements.
-
Do not cluster tables if the application joins them only occasionally
or modifies their common column values frequently. Modifying a row's
cluster key value takes longer than modifying the value in an
unclustered table, because Oracle might need to migrate the modified row
to another block to maintain the cluster.
-
Do not cluster tables if the application often performs full table
scans of only one of the tables. A full table scan of a clustered table
can take longer than a full table scan of an unclustered table. Oracle
is likely to read more blocks, because the tables are stored together.
-
Cluster master-detail tables if you often select a master record and
then the corresponding detail records. Detail records are stored in the
same data block(s) as the master record, so they are likely still to be
in memory when you select them, requiring Oracle to perform less I/O.
-
Store a detail table alone in a cluster if you often select many
detail records of the same master. This measure improves the performance
of queries that select detail records of the same master, but does not
decrease the performance of a full table scan on the master table. An
alternative is to use an index organized table.
-
Do not cluster tables if the data from all tables with the same
cluster key value exceeds more than one or two Oracle blocks. To access a
row in a clustered table, Oracle reads all blocks containing rows with
that value. If these rows take up multiple blocks, then accessing a
single row could require more reads than accessing the same row in an
unclustered table.
-
Do not cluster tables when the number of rows for each cluster key
value varies significantly. This causes waste of space for the low
cardinality key value; it causes collisions for the high cardinality key
values. Collisions degrade performance.
See Also:
Oracle Database Administrator's Guide for more information on creating clusters14.9 Using Hash Clusters for Performance
Hash clusters group table data by applying a hash function to each row's cluster key value. All rows with the same cluster key value are stored together on disk. Consider the benefits and drawbacks of hash clusters with respect to the needs of the application. You might want to experiment and compare processing times with a particular table as it is stored in a hash cluster, and as it is stored alone with an index.Follow these guidelines for choosing when to use hash clusters:
-
Use hash clusters to store tables accessed frequently by SQL statements with
WHERE
clauses, if theWHERE
clauses contain equality conditions that use the same column or combination of columns. Designate this column or combination of columns as the cluster key.
-
Store a table in a hash cluster if you can determine how much space
is required to hold all rows with a given cluster key value, including
rows to be inserted immediately as well as rows to be inserted in the
future.
-
Use sorted hash clusters,
where rows corresponding to each value of the hash function are sorted
on a specific columns in ascending order, when response time can be
improved on operations with this sorted clustered data.
-
Do not store a table in a hash cluster if the application often
performs full table scans and if you must allocate a great deal of space
to the hash cluster in anticipation of the table growing. Such full
table scans must read all blocks allocated to the hash cluster, even
though some blocks might contain few rows. Storing the table alone
reduces the number of blocks read by full table scans.
-
Do not store a table in a hash cluster if the application frequently
modifies the cluster key values. Modifying a row's cluster key value can
take longer than modifying the value in an unclustered table, because
Oracle might need to migrate the modified row to another block to
maintain the cluster.
No comments:
Post a Comment