Oracle's Bitmap Index

Common indexes for Oracle are the Btree index and the bitmap index.

Btree Index Features:

Default Index

Suitable for a large number of additions and deletions

Cannot use the OR operator

Suitable for high cardinality columns (that is, multiple unique values)

Create Sql:create index lie_idx1 on table (liename);

Bitmap Index Features:

The cost of doing the update is very high

Good for OR operator

Columns with fewer cardinality (that is, duplicate values are many)

Create Sql:create bitmap index lie_bit_idx1 on table (liename);

Bitmap index using a note:

To make a bitmap index (BITMAP) on a column, the column must be infrequently modified. Because Oracle locks a duplicate value of a lookup when querying a bitmap index, SQL that has that duplicate value will be blocked until the first SQL is committed before one SQL is committed.

Some rules for creating indexes:

1. Weigh the relationship between the index and DML. Executing DML statements modifies indexes

2. Put the index and table data in different table spaces. You can read the index in parallel when reading the table data "moving index: ALTER index INDEXNAME rebuild Tablespace"

Common Operations for indexes:

Re-create INDEX: ALTER index INDEXNAME rebuild tablespace indexname2; The index record is not cleared immediately after the database operation Delete command

Online rebuild index: ALTER index indexname rebuild online;

Consolidated index: ALTER index INDEXNAME COALESCE; For organizing Index fragmentation

Delete indexes: Drop index scott.indexname;

Analysis Index:

1. Query the table that holds the analysis data: SELECT COUNT (*) from Index_status;

2, perform Analysis index command: Analyze index indexname validate structrue; --The index information is stored in the Index_status table after analysis.

Oracle's Bitmap Index