INDEXING
All data in Oracle - tables, indexes, clusters - is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So you never read "just one row"; you will always read the entire block and ignore the rows you don't need. Minimizing this wastage is one of the fundamentals of Oracle Performance Tuning.
If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.
"B" Tree Stands for Balanced Tree
Contrary to popular belief, b is not for binary; it's balanced.
As you insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length.
BITMAP INDEXES
With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
B-tree indexes are well suited for OLTP applications in which users' queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during non peak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.
Index information
The dba_indexes view is populated with index statistics when indexes are analyzed. The dba_indexes view contains a great deal of important information for the SQL optimizer, but there is still more to see. Oracle provides an analyze index xxx validate structure command that provides additional statistics into a temporary table called index_stats. But, the information needs to be saved, as each analyze validate structure command overlays the information.
To get the full picture, you need both pieces. Also, there are certainly some columns that are more important than others:
•CLUSTERING_FACTOR. This is one of the most important index statistics because it indicates how well sequenced the index columns are to the table rows. If clustering_factor is low (about the same as the number of dba_segments.blocks in the table segment) than the index key is in the same order as the table rows and index range scan will be very efficient, with minimal disk I/O. As clustering_factor increases (up to dba_tables.num_rows), the index key is increasingly out of sequence with the table rows. Oracle's cost-based SQL optimizer relies heavily upon clustering_factor to decide whether or not to use the index to access the table.
•HEIGHT. As an index accepts new rows, the index blocks split. Once the index nodes have split to a predetermined maximum level the index will "spawn" into a new level.
•BLOCKS This is the number of blocks consumed by the index. This is dependent on the db_block_size. In Oracle9i and beyond, many DBAs create b-tree indexes in very large block sizes (db_32k_block_size) because the index will spawn less. Robin Schumacher has noted in his book Oracle Performance Troubleshooting notes:
"As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database."
All data in Oracle - tables, indexes, clusters - is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So you never read "just one row"; you will always read the entire block and ignore the rows you don't need. Minimizing this wastage is one of the fundamentals of Oracle Performance Tuning.
If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.
"B" Tree Stands for Balanced Tree
Contrary to popular belief, b is not for binary; it's balanced.
As you insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length.
BITMAP INDEXES
With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
B-tree indexes are well suited for OLTP applications in which users' queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during non peak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.
Index information
The dba_indexes view is populated with index statistics when indexes are analyzed. The dba_indexes view contains a great deal of important information for the SQL optimizer, but there is still more to see. Oracle provides an analyze index xxx validate structure command that provides additional statistics into a temporary table called index_stats. But, the information needs to be saved, as each analyze validate structure command overlays the information.
To get the full picture, you need both pieces. Also, there are certainly some columns that are more important than others:
•CLUSTERING_FACTOR. This is one of the most important index statistics because it indicates how well sequenced the index columns are to the table rows. If clustering_factor is low (about the same as the number of dba_segments.blocks in the table segment) than the index key is in the same order as the table rows and index range scan will be very efficient, with minimal disk I/O. As clustering_factor increases (up to dba_tables.num_rows), the index key is increasingly out of sequence with the table rows. Oracle's cost-based SQL optimizer relies heavily upon clustering_factor to decide whether or not to use the index to access the table.
•HEIGHT. As an index accepts new rows, the index blocks split. Once the index nodes have split to a predetermined maximum level the index will "spawn" into a new level.
•BLOCKS This is the number of blocks consumed by the index. This is dependent on the db_block_size. In Oracle9i and beyond, many DBAs create b-tree indexes in very large block sizes (db_32k_block_size) because the index will spawn less. Robin Schumacher has noted in his book Oracle Performance Troubleshooting notes:
"As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database."
•PCT_USED. This metric is very misleading because it looks identical to the dba_indexes pct_used column, but has a different meaning. Normally, the pct_used threshold is the freelist unlink threshold, while in index_stats pct_used is the percentage of space allocated in the b-tree that is being used.
Concatenated Index
When frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes.
In the case of a Concatenated Index, The ability of an Oracle Optimizer to use a Concatenated index without reference to leading edge columns is known as the skip-scan feature.A concatenated index that is used for skip-scanning is more efficient than a full table scan. Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated.
Function Based Indexes
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index
Indexing a Virtual Column
It is said that creating a Virtual Column and Indexing the Virtual Column performs much better than a Functional Index on a Table Column however it all depends on the data which is stored in the table. Therefore it is always necessary to test it before replacing any Functional Index with a Virtual Column. Also Virtual column concept can applied only for Oracle Releases after 11G.A virtual column is not free. If you have an existing table, you have to create and maintain the DDL required to create the virtual column, whereas a function-based index can be added, modified, and dropped independently from the table.
Several caveats are associated with virtual columns:
Concatenated Index
When frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes.
In the case of a Concatenated Index, The ability of an Oracle Optimizer to use a Concatenated index without reference to leading edge columns is known as the skip-scan feature.A concatenated index that is used for skip-scanning is more efficient than a full table scan. Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated.
Function Based Indexes
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index
Indexing a Virtual Column
It is said that creating a Virtual Column and Indexing the Virtual Column performs much better than a Functional Index on a Table Column however it all depends on the data which is stored in the table. Therefore it is always necessary to test it before replacing any Functional Index with a Virtual Column. Also Virtual column concept can applied only for Oracle Releases after 11G.A virtual column is not free. If you have an existing table, you have to create and maintain the DDL required to create the virtual column, whereas a function-based index can be added, modified, and dropped independently from the table.
Several caveats are associated with virtual columns:
- You can define a virtual column only on a regular heap-organized table. You can't define a virtual column on an index-organized table, an external table, a temporary table, object tables, or cluster tables.
- Virtual columns can't reference other virtual columns.
- Virtual columns can reference columns only from the table in which the virtual column is defined.
- The output of a virtual column must be a scalar value (a single value, not a set of values).
Reverse Key Indexes
When sequential data is loaded, the index may encounter I/O-related bottlenecks. During the data loads, one part of the index, and one part of the disk, may be used much more heavily than any other part. To alleviate this problem, you should store your index tablespaces on disk architectures that permit the files to be physically striped across multiple disks.Reverse-key indexes are similar to B-tree indexes except that the bytes of the index key are reversed when an index entry is created.
You cannot use reverse key indexes with bitmap indexes or index-organized tables.
PARTITIONED INDEXES
Indexes, like tables, may be partitioned. There are two possible methods to partition indexes.They are Local and Global Partitioned Indexes.
You may either:
Equipartition the index with the table, Also known as a local index. For every table partition, there will be an index partition that indexes just that table
partition. All of the entries in a given index partition point to a single table partition and all of the rows in a single table partition are represented in a single index partition.
Partition the index by range , Also known as a global index. Here the index is partitioned by range, and a single index partition may point to any (and all) table partitions.
In the locally partitioned index, the index entries in a given partition, point into exactly one table partition. The globally partitioned index diagram however, shows that the index entries in a global index may point into any or all of the table partitions. Also, note that the number of index partitions may in fact be different than the number of table partitions.
Since global indexes may be partitioned by range only, you must use local indexes if you wish to have a hash or composite partitioned index. The local index will be partitioned using the same scheme as the underlying table.
Prefixed Indexes
Prefixed indexes are indexes that contain keys from the partitioning key as the leading edge of the index.
Non-Prefixed Indexes
Non-prefixed indexes are indexes that do not have the leading column of the partitioning key as the leading column of the index.
Compressed indexes
This is really an option to a normal b-tree index. It results in fewer leaf nodes, so less overall I/O and less to cache. All of this means Oracle's optimizer will be just a little more likely to use these than a normal uncompressed index. There is a cost to all of this, in CPU to uncompress when you access these. Also, in reading about how the optimizer uses these, and choosing the proper compression level, it begins to look daunting. Your mileage may vary.
Compressed indexes have the following advantages:
- Reduced storage
- More rows stored in leaf blocks, which can result in less I/O when accessing a compressed index
Invisible indexes
These are new in 11g. They are created as a normal index, but invisible to the cost based optimizer. This can allow you to test out performance of large queries, without impacting the live running application.
Bitmap Join Indexes
The use of bitmap join indexes is restricted; you can only index the columns in the dimension tables.The columns used for the join must be primary key or unique constraints in the dimension tables, and if it is a composite primary key, you must use each of the columns in your join.
You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.
These are new in 11g. They are created as a normal index, but invisible to the cost based optimizer. This can allow you to test out performance of large queries, without impacting the live running application.
Bitmap Join Indexes
The use of bitmap join indexes is restricted; you can only index the columns in the dimension tables.The columns used for the join must be primary key or unique constraints in the dimension tables, and if it is a composite primary key, you must use each of the columns in your join.
You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.
No comments:
Post a Comment