Index:
An index stores the values in the indexed column(s). And for each value the locations of the rows that have it. Just like the index at the back of a book.
Types of index:
B-tree vs. Bitmap:
B-tree:
By default indexes are B-tree.These are balanced. This means that all the leaf nodes are at the same depth in the tree.
Bitmap:
A bitmap index is a two-dimensional array with zero and one (bit) values. it stores the indexing information in bit arrays. The query and search result is done by performing logical bitwise 0 or 1 operation. This make it very fast.
Comparison in both:
Bitmaps is it's easy to compress all those ones and zeros. So a bitmap index is typically smaller than the same B-tree index.
Rows where all the indexed values are null are NOT included in a B-tree. But they are in a bitmap! So the optimizer can use a bitmap to answer queries like:
where indexed_column is null;
You can get around this with B-trees by adding a constant to the end of an index. This makes the following composite index:
create index enable_for_is_null_i on tab ( indexed_column, 1 );
Bitmap indexes may lead to concurrency issues however, possibly blocking other DML on the same table. Therefore these should be avoided in an OLTP applications.
Why are B-trees the default instead of bitmaps?
Killing write concurrency.
They're one of the few situations in Oracle Database where an insert in one session can block an insert in another. This makes them questionable for most OLTP applications.
Well, whenever you insert, update or delete table rows, the database has to keep the index in sync. This happens in a B-tree by walking down the tree, changing the leaf entry as needed. You can see how this works with this visualization tool.
But bitmap locks the entire start/end rowid range! So say you add a row with the value RED. Any other inserts which try to add another row with the value RED to the same range are blocked until the first one commits!
This is an even bigger problem with updates. An update from a B-tree is really a delete of the old value and insert of the new one. But with a bitmap Oracle Database has to lock the affected rowid ranges for both the old and new values!
Function-based Indexes:
Unique Indexes:
Descending Indexes:
Concatenate or Composite index:
It is an index on multiple columns in a table.
Global Partitioned Index:
Local Partitioned Index:
An index stores the values in the indexed column(s). And for each value the locations of the rows that have it. Just like the index at the back of a book.
Types of index:
B-tree vs. Bitmap:
B-tree:
By default indexes are B-tree.These are balanced. This means that all the leaf nodes are at the same depth in the tree.
Bitmap:
A bitmap index is a two-dimensional array with zero and one (bit) values. it stores the indexing information in bit arrays. The query and search result is done by performing logical bitwise 0 or 1 operation. This make it very fast.
Comparison in both:
Bitmaps is it's easy to compress all those ones and zeros. So a bitmap index is typically smaller than the same B-tree index.
Rows where all the indexed values are null are NOT included in a B-tree. But they are in a bitmap! So the optimizer can use a bitmap to answer queries like:
where indexed_column is null;
You can get around this with B-trees by adding a constant to the end of an index. This makes the following composite index:
create index enable_for_is_null_i on tab ( indexed_column, 1 );
Bitmap indexes may lead to concurrency issues however, possibly blocking other DML on the same table. Therefore these should be avoided in an OLTP applications.
Why are B-trees the default instead of bitmaps?
Killing write concurrency.
They're one of the few situations in Oracle Database where an insert in one session can block an insert in another. This makes them questionable for most OLTP applications.
Well, whenever you insert, update or delete table rows, the database has to keep the index in sync. This happens in a B-tree by walking down the tree, changing the leaf entry as needed. You can see how this works with this visualization tool.
But bitmap locks the entire start/end rowid range! So say you add a row with the value RED. Any other inserts which try to add another row with the value RED to the same range are blocked until the first one commits!
This is an even bigger problem with updates. An update from a B-tree is really a delete of the old value and insert of the new one. But with a bitmap Oracle Database has to lock the affected rowid ranges for both the old and new values!
Function-based Indexes:
Unique Indexes:
Descending Indexes:
Concatenate or Composite index:
It is an index on multiple columns in a table.
Global Partitioned Index:
Local Partitioned Index:
https://www.tutorialsteacher.com/sqlserver/indexes
No comments:
Post a Comment