Indexes

a) Btree index

b) Btree index clusters

c) Hash cluster index

d) Bitmap index.

a) Btree index  

A btree index consists of Root, Branch and Leaf blocks. A leaf block contains KEY, which is the index column values sorted in ascending order and ADDRESS, which is the rowid sorted in ascending order. If the index column (first column/leading edge in case of composite index) value is null for any row of the table, then the corresponding index entry is skipped. For index to be used while executing the SQL statement, indexed column (first column in case of composite index) should have been referred in the WHERE clause with no function applied to it (in case of LIKE predicate first position should not be %).

• The distribution of data on a btree index column is very important. If the data is badly skewed (one distinct value over 5 rows, another one over 50000 rows of the table) on the indexed column, oracle might not use an index when it should. If the table has been analyzed without using histogram, oracle makes an incorrect assumption. It will use full table scan while retrieving less number of rows also.

Following example shows how to create & read a Histogram-

SELECT deptno,
count(*)
FROM emp
GROUP BY deptno;

DEPTNO	COUNT(*)
------	--------
1	125000
2	125000
3	1
4	125000
5	495000
6	4000
7	1000
8	124999

To find distribution of data on indexed column using VALIDATE clause-

ANALYZE INDEX btree_deptno VALIDATE STRUCTURE;

SELECT * FROM index_histogram;

REPEAT_COUNT	KEYS_WITH_REPEAT_COUNT
------------	----------------------
0	3
32768	0
65536	0
98304	4
131072	0

First row shows that 3 keys are repeated in the range 0 to 32768. Fourth row shows that 4 keys are repeated in the range of 98304 and 131072.

Oracle uses height-balanced histogram. Each bucket of height-balanced histogram contains same number of elements. Histogram produces clearer picture of distribution of data on the indexed column.

To create a Histogram-

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS deptno SIZE 8;          -- i.e. number of buckets=8

SELECT endpoint_number,
endpoint_value
FROM user_histograms
WHERE column_name = 'DEPTNO';

ENDPOINT_NUMBER	ENDPOINT_VALUE
---------------	--------------
1	1
2	2
3	4
6	5
7	7
8	8

Bucket 1 starts with 1, contains 125000 ones and has an endpoint of 1. Similarly Bucket 2 ends with 2. Bucket 3 starts with 1 three, 124999 fours and has an endpoint of 4. Bucket 4 starts with 1 four, 124999 fives and endpoint of 5. Bucket 5 and 6 contains 125000 fives each and has endpoint of 5. Bucket 7 starts with 120001 fives, 4000 sixes and 999 sevens and endpoint of 7. Oracle skips rows for buckets 4 & 5, as buckets 4, 5, 6 have same endpoint number of 5.

• Btree index should not be created in the same tablespace as the data for the table.
  Spreading the input-output across several disk drives maximizes throughput and produces much faster queries.

• To speed up the process of creating an index on a larger table, you can use NOSORT, UNRECOVERABLE options. The largest piece of overhead in index creation is sorting. If the data is already in ascending order, NOSORT option can be used. When you use UNRECOVERABLE option, no information is written to the redo logs.

• Disable the PK constraint before performing a large table load. It will be faster. Then enable the constraint. But if table contains duplicate keys PK will fail. To resolve this, Exceptions table can be used. Use $ORACLE_HOME/rdbms/admin/utlexcpt.sql to create Exceptions table.

ALTER TABLE emp ENABLE CONSTRAINT pk_emp_empno EXCEPTIONS INTO exceptions;
SELECT ename, empno FROM emp E, exceptions X WHERE E.rowid = X.row_id;
Update the duplicate rows and then enable the constraint.

• If you want to use full table scan always, you can deliberately suppress the use of the index by changing the WHERE clause (or you can use Hints also) as follows-     where deptno + 0 > 40;
  where deptname||'' = 'ACCOUNTS';
  where initcap(deptname) = 'Accounts';

• NOT IN, IS NULL/NOT NULL conditions on indexed column suppress the use of the index, irrespective of the optimizer used.

• Index become fragmented over time if many index entries have been deleted when rows from a table are deleted. It might result in poorer performance. In that case, rebuild it rather than dropping and recreating it. It is very fast as it bypasses the sorting operation.

ALTER INDEX btree_deptno REBUILD;

b) Btree index cluster

A btree index cluster prejoins tables on-disk. Sometimes a table can be clustered to itself. To create a btree index cluster, you must pick the cluster key column and the average block size for each cluster block. If the size is too small, you will exeperience chained cluster blocks. If the size is too large, you will have wasted space and slower running queries.

c) Hash cluster index

Hash cluster index is the result of applying a hash function to a column (or columns) of a table. It can be slightly more efficient than a btree index. With a btree index, the root, branch and leaf blocks must be retrieved to return rows using index, whereas a table that is clustered using a hash function usually requires only one i/o to locate the data. You can define your own hash function or use oracle's default hash function.

d) Bitmap index

                                    Row1 Row2 Row3 Row4 and so on
Bitmap Index entry: F           1         1        0         1
Bitmap Index entry: M          0         0        1         0

Using AND's OR's in the WHERE clause on bitmap indexed columns is very fast, because the bitmaps are actually merged in memory before the table is accessed. The storage requirements are very low compared to other indexes.

NOT IN, <> conditions on bitmap indexed column suppresses the use of index.