Using a Different Index Type:
=============================
1.B-Tree Indexes
These indexes are the standard index type, and they are excellent for primary key and highly-selective indexes.
Used as concatenated indexes, the database can use B-tree indexes to retrieve data sorted by the index columns.
2.Bitmap Indexes
These indexes are suitable for low cardinality data. Through compression techniques, they can generate a large number of rowids with minimal I/O.
Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O.
Bitmap indexes are particularly efficient in queries with COUNT(), because the query can be satisfied within the index.
3.Function-based Indexes
These indexes allow access through a B-tree on a value derived from a function on the base data.
Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the query optimizer enabled.
Function-based indexes are particularly useful when querying on composite columns to produce a derived result or to overcome limitations
in the way data is stored in the database. An example is querying for line items in an order exceeding a certain value derived from
(sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data
to allow case-insensitive searches.
4.Partitioned Indexes
Partitioning a global index allows partition pruning to take place within an index access, which results in reduced I/Os.
By definition of good range or list partitioning, fast index scans of the correct index partitions can result in very fast query times.
5.Reverse Key Indexes
These indexes are designed to eliminate index hot spots on insert applications. These indexes are excellent for insert performance,
but they are limited because the database cannot use them for index range scans.
=============================
1.B-Tree Indexes
These indexes are the standard index type, and they are excellent for primary key and highly-selective indexes.
Used as concatenated indexes, the database can use B-tree indexes to retrieve data sorted by the index columns.
2.Bitmap Indexes
These indexes are suitable for low cardinality data. Through compression techniques, they can generate a large number of rowids with minimal I/O.
Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O.
Bitmap indexes are particularly efficient in queries with COUNT(), because the query can be satisfied within the index.
3.Function-based Indexes
These indexes allow access through a B-tree on a value derived from a function on the base data.
Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the query optimizer enabled.
Function-based indexes are particularly useful when querying on composite columns to produce a derived result or to overcome limitations
in the way data is stored in the database. An example is querying for line items in an order exceeding a certain value derived from
(sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data
to allow case-insensitive searches.
4.Partitioned Indexes
Partitioning a global index allows partition pruning to take place within an index access, which results in reduced I/Os.
By definition of good range or list partitioning, fast index scans of the correct index partitions can result in very fast query times.
5.Reverse Key Indexes
These indexes are designed to eliminate index hot spots on insert applications. These indexes are excellent for insert performance,
but they are limited because the database cannot use them for index range scans.
No comments:
Post a Comment