Indexes in oracle

Oracle/PLSQL: Indexes

---

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Create an Index

The syntax for creating a index is:

CREATE [UNIQUE] INDEX index_name

  ON table_name (column1, column2, . column_n)   [ COMPUTE STATISTICS ];
   ON supplier (supplier_name);
   ON supplier (supplier_name, city);


We could also choose to collect statistics upon creation of the index as follows:
  ON supplier (supplier_name, city)
  COMPUTE STATISTICS;
  ON table_name (function1, function2, . function_n)
  [ COMPUTE STATISTICS ];
   ON supplier (UPPER(supplier_name));
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);
  RENAME TO new_index_name;
  RENAME TO supplier_index_name;
  REBUILD COMPUTE STATISTICS;
  REBUILD COMPUTE STATISTICS;

UNIQUE indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

For Example:

CREATE INDEX supplier_idx

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx

CREATE INDEX supplier_idx

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

The syntax for creating a function-based index is:

CREATE [UNIQUE] INDEX index_name

For Example:

CREATE INDEX supplier_idx

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)

Rename an Index

The syntax for renaming an index is:

ALTER INDEX index_name

For Example:

ALTER INDEX supplier_idx

In this example, we're renaming the index called supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

The syntax for collecting statistics on an index is:

ALTER INDEX index_name

For Example:

ALTER INDEX supplier_idx

In this example, we're collecting statistics for the index called supplier_idx.

Drop an Index

The syntax for dropping an index is:

DROP INDEX index_name;

For Example:

DROP INDEX supplier_idx;

In this example, we're dropping an index called supplier_idx.

Bit Max Indexes:

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

Characteristic of Bitmap Indexes

o    For columns with very few unique values (low cardinality)

Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 %  that the column is ideal candidate, consider also 0.2% – 1%)

o    Tables that have no or little insert/update are good candidates (static data in warehouse)
 

o    Stream of bits: each bit relates to a column value in a single row of table

create bitmap index person_region on person (region);

        Row     Region   North   East   West   South
        1       North        1      0      0       0
        2       East         0      1      0       0
        3       West         0      0      1       0
        4       West         0      0      1       0
        5       South        0      0      0       1
        6       North        1      0      0       0

. Advantage of Bitmap Indexes

The advantages of them are that they have a highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.

Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.

One belief concerning bitmap indexes is that they are only suitable for indexing low-cardinality data. This is not necessarily true, and bitmap indexes can be used very successfully for indexing columns with many thousands of different values.

Disadvantage of Bitmap Indexes

The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful

Bitmap Indexes and Deadlocks

Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

Session 1	Session 2
create table bitmap_index_demo (   value varchar2(20) );
insert into bitmap_index_demo select decode(mod(rownum,2),0,'M','F')   from all_objects;
create bitmap index   bitmap_index_demo_idx   on bitmap_index_demo(value);
insert into bitmap_index_demo   values ('M'); 1 row created.
	insert into bitmap_index_demo   values ('F'); 1 row created.
insert into bitmap_index_demo   values ('F'); ...... waiting ......
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource	insert into bitmap_index_demo   values ('M'); ...... waiting ......

The "-Tree" in b-Tree

A b-Tree index is a data structure in the form of a tree - no surprises there - but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book.

• Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells you the physical location of the telephone (row in the table).
• The names on each page are sorted, and the pages - when sorted correctly - contain a complete sorted list of every name and address

A sorted list in a phone book is fine for humans, beacuse we have mastered "the flick" - the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.

If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.

To find the name Gallileo in this b-Tree phone book, we:

• Read page 1. This tells us that page 6 starts with Fermat and that page 7 starts with Hawking.
• Read page 6. This tells us that page 350 starts with Fyshe and that page 351 starts with Garibaldi.
• Read page 350, which is a leaf block; we find Gallileo's address and phone number.

That's it; 3 blocks to find a specific row in a million row table. In reality, index blocks often fit 100 or more rows, so b-Trees are typically quite shallow. I have never seen an index with more than 5 levels. Curious? Try this:

SELECT index_name, blevel+1 FROM user_indexes ORDER BY 2;

user_indexes.blevel is the number of branch levels. Always add 1 to include the leaf level; this tells you the number of blocks a unique index scan must read to reach the leaf-block. If you're really, really, insatiably curious; try this in SQL*Plus:

ACCEPT index_name PROMPT "Index Name: "

ALTER SESSION SET TRACEFILE_IDENTIFIER = '&index_name';

COLUMN object_id NEW_VALUE object_id

SELECT object_id

FROM   user_objects

WHERE  object_type = 'INDEX'

AND    object_name = upper('&index_name');

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id';

ALTER SESSION SET TRACEFILE_IDENTIFIER = "";

SHOW PARAMETER user_dump_dest

Give the name of an index on a smallish table (because this will create a BIG file). Now, on the Oracle server, go to the directory shown by the final SHOW PARAMETER user_dump_dest command and find your trace file - the file name will contain your index name. Here is a sample:

*** 2007-01-31 11:51:26.822

----- begin tree dump

branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)

   leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)

   leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)

   leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)

   leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)

   leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)

   ...

   ...

   leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)

   leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)

   leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)

   leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)

----- end tree dump

This index has only a root branch with 323 leaf nodes. Each leaf node contains a variable number of index entries up to 807! A deeper index would be more interesting, but it would take a while to dump.

Structural difference between bitmap and btree index in oracle? 

Structural difference between bitmap and btree index

BtreeIt is made of branch nodes and leaf nodes. Branch nodes holds prefix key value along with the link to the leaf node. The leaf node in turn contains the indexed value and rowed.

BitmapIt simply consists of bits for every single distinct value. It uses a string of bits to quickly locate rows in a table. Used to index low cardinality columns. 

Difference Between B-Tree Index & Bitmap Index.

1. B-tree Index has low cardinality values, where as Bitmap Index has High Cardinality values.
2. B-tree Index is userful for OLTP, where as Bitmap Index is useful for Dataware Housing.
3. B-tree index updates on key values has relatively inexpensive, where as Bitmap index has more expensive.

A B-Tree index stores the index value and the physical rowid of the row. The index values are arranged in the form of leaves. A B-Tree index is used most when the cardinality is high.

A Bitmap index on the other hand is used when the cardinality is low and there are a lot of duplicate data in the indexed column (for example Gender). The Bitmap index consists of 4 columns (first beign the index value, the second and third column consisting of the start and last rowid of the table, and the fourth column consisting of the bitmap. A row having the index value will have its corresponding bit set.

A bit map index generally consumes less space