b树index和bitmap索引

 

 

---创建表

create table t05111_gender(a char(1));

----循环插入数据

begin

     for i in 1..50
       loop
         insert into t05111_gender values('A');
         end loop;
         commit:
         end;

         /

-----在表上建立索引

create index i05111_gender on t05111_gender(a);

---查询索引的blevel

 select i.INDEX_NAME,i.blevel
    from user_indexes i
    where i.table_owner='HR'
    and i.TABLE_NAME='T05111_GENDER';

---查询索引块大小

select s.segment_name,s.BYTES/(1024*1024)
from user_segments s
where s.segment_name='I05111_GENDER';

----删除索引，建立bitmap索引，发现bitmap索引块更小，效率更好

drop index i05111_gender;

create bitmap index i05111_gender on t05111_gender(a);

select s.segment_name,s.BYTES/(1024*1024)
from user_segments s
where s.segment_name='I05111_GENDER';