What is the difference between an Oracle B*tree index and an Oracle bitmap index

(1) Establish B*tree index

3:11:08 sql>create index Emp1_job_ind on EMP1 (Job);

(2) Analysis of index structure

3:11:08 sql> ANALYZE INDEX emp1_job_ind VALIDATE STRUCTURE;

Index analyzed.

(3) View Index storage information

03:11:41 sql> SELECT blevel,leaf_blocks,num_rows from user_indexes

03:12:12 2 WHERE index_name= ' Emp1_job_ind ';

Blevel Leaf_blocks Num_rows

---------- ----------- ----------

0 1 14

03:12:26 sql> INSERT into the EMP1 SELECT * from EMP1;

---------self replication to create multiple lines of records.

03:12:26 sql> ANALYZE TABLE EMP1 COMPUTE STATISTICS;

Table analyzed.

03:13:01 sql> SELECT blevel,leaf_blocks,num_rows from user_indexes

03:13:07 2 WHERE index_name= ' Emp1_job_ind ';

Blevel Leaf_blocks Num_rows

---------- ----------- ----------

1 118 28672

(4) Establish bitmap index

03:34:52 sql> DROP Index emp1_job_ind;

03:34:52 sql> Create bitmap index emp1_job on emp1 (Job);

(5) Analysis of index structure

03:34:52 sql> ANALYZE TABLE EMP1 COMPUTE STATISTICS;

Table analyzed.

03:13:01 sql> SELECT blevel,leaf_blocks,num_rows from user_indexes

03:13:07 2 WHERE index_name= ' Emp1_job_ind ';

Blevel Leaf_blocks Num_rows

---------- ----------- ----------

1 16 10

Establishing a bitmap index on a column with a lot of duplicate values is more advantageous than the B*tree index.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/