Understanding BITMAP is so easy

1. Syntax

CREATE BITMAP INDEX index_name ON table

2. An simple example


TABLE test(id, name address){
(1,Rob,New York)
(2,Mary,Orlando)
(3,Jason,Chicago)
(4,Lisa,New York)
(5,Kim,New York)
(6,Philip,Washington)
(7,Frank,New York)
(8,Elizabeth,New York)
(9,Klaus,New York)
...
}

if you want to find the result under the condition of 【Where address = 'New York'】,however, many rows meet the condition and then you won't get the benefits from the general index for efficient query. Here, you can create BITMAP INDEX:

New York	Orlando	Chicago	Washington
1	0	0	0
0	1	0	0
0	0	1	0
1	0	0	0
1	0	0	0
0	0	0	1
1	0	0	0
1	0	0	0
1	0	0	0

In this case,  select * from TABLE where address = "New York"  will be quickly execute to find the resultset according to "1" and "0".

Difference to between COMPUTE STATISTICS and VALIDATE STRUCTURE for ANALYZE INDEX

TWO WAYS to analyze index:
ANALYZE INDEX index_name COMPUTE STATISTICS;
ANALYZE INDEX index_name VALIDATE STRUCTURE;

COMPUTE STATISTICS:

populates statistics for cost base optimizer mode

using all the target data to compute index

create histogram based statistics

It is for making better explain plan for optimizer

VALIDATE STRUCTURE:

just a diagnostic tool -- not for statistics gathering!

detect index physical/logical corruption of segment

populates some statistics in index_stats for session level

check index integrity/fragmentation

PRACTICE:
index_stats only stores a single line of data (in the same session, new value replaces the old one)

SQL> analyze index pk_emp validate structure;

Index analyzed.

SQL> select name from index_stats;

NAME
------------------------------
PK_EMP

SQL> analyze index pk_dept validate structure;

Index analyzed.

SQL> select name from index_stats;

NAME
------------------------------
PK_DEPT

SQL> disconnect;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn scott/tiger
Connected.
SQL> analyze index pk_emp validate structure;

Index analyzed.

SQL> analyze index pk_dept validate structure;

Index analyzed.

SQL> select name from index_stats;

NAME
------------------------------
PK_DEPT

online/offline option Test

SQL> conn scott/tiger
Connected.
SQL> analyze index pk_emp validate structure online;

Index analyzed.

SQL> select count(1) from index_stats;

  COUNT(1)
----------
         0

SQL> analyze index pk_emp validate structure offline;

Index analyzed.

SQL> select count(1) from index_stats;

  COUNT(1)
----------
         1