談一下oracle索引

標籤：談一下oracle索引

索引的應用：
索引的分類：
邏輯分類：
單列索引 & 複合列索引
create index i_emp_ename on emp (ename);
create index i_emp_3 on emp (empno,ename,sal);
非唯一鍵索引 & 唯一鍵索引
create unique index i_emp_ename on emp (ename);
基於函數的索引
create index i_emp_ename on emp (lower(ename));
應用程式定義域索引:

oracle查看錶是否有索引：
select index_name from user_indexes where table_name=‘EMP‘;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
物理分類:
非分區索引 & 分區索引
B-Tree索引：
  常規B-Tree  ：
  反向鍵B-Tree：create index i_emp_ename on emp (ename) reverse;

Bitmap索引:適合使用在低基數列（重複值多的列：性別）
create bitmap index i_ob1_id on ob1 (object_id);

SQL> create index i_e01_empno on e01 (empno);
SQL> create bitmap index i_e01_empno on e01 (empno);
SQL> select blocks/128 from user_segments where segment_name=‘I_E01_EMPNO‘;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
影響索引使用的因素：
1.空值影響B-Tree索引的使用：B-Tree索引不記錄空值
create index i_emp_ename on emp (ename);
SQL> analyze index i_emp_ename validate structure;
SQL> select NAME,HEIGHT,BLOCKS,BR_BLKS,BR_ROWS,LF_BLKS,LF_ROWS from index_stats;
alter table e01 modify (empno number not null);

合并索引葉集塊片段：不會降低索引的二元高度
SQL> alter index I_EMP_ENAME coalesce;

如果需要降低索引的二元高度使用重建索引
SQL> alter index I_EMP_ENAME rebuild; --> 使用現有索引的關鍵字重建新索引
SQL> alter index I_EMP_ENAME rebuild online;

報如下錯誤，需要online解決。比如：rebuild online
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> create index I_EMP_ENAME on emp (ename) online;

2.隱式資料類型
create table t01 (code_id varchar2(20));
insert into t01 values (1);
create index i_t01_code on t01 (code_id);
select * from t01 where code_id=1;
select * from t01 where code_id=‘1‘;

3.初始化參數影響索引使用:索引權重
optimizer_index_cost_adj=[1~10000]%

alter session set optimizer_index_cost_adj=50;
全表的成本 PK 索引訪問的成本*optimizer_index_cost_adj

4.索引的聚簇因子：順序訪問索引索引值而在表的資料區塊上面跳轉的次數；反映的是通過索引順序取值對錶的資料區塊訪問的次數

select * from e02 where empno<46572; --> .203037807

select * from e01 where empno<376;   --> .00163923

empno<2000

select dbms_rowid.rowid_block_number(rowid),count(*) from e01 where empno<2000 group by dbms_rowid.rowid_block_number(rowid);

select blocks from user_segments where segment_name=‘E01‘;
select blocks from user_tables where table_name=‘E01‘;

select dbms_rowid.rowid_block_number(rowid),count(*) from e02 where empno<2000 group by dbms_rowid.rowid_block_number(rowid);

select blocks from user_segments where segment_name=‘E02‘;
select blocks from user_tables where table_name=‘E02‘;

SQL> select index_name,clustering_factor from user_indexes where index_name in (‘I_E01_EMPNO‘,‘I_E02_EMPNO‘);

INDEX_NAME               CLUSTERING_FACTOR
------------------------------ -----------------
I_E01_EMPNO                  229204
I_E02_EMPNO                    1384
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter table ob1 modify (object_id number not null)
索引的掃描演算法和效率：
索引的唯一鍵掃描:INDEX UNIQUE SCAN
select * from emp where empno=7839;

索引的快速全掃描:INDEX FAST FULL SCAN,不讀取根與分枝塊，唯讀取葉子，而且使用批量讀取，結果無序
select object_id from ob1;

索引的全掃描：INDEX FULL SCAN,結果有序
select object_id from ob1 order by 1;

索引的範圍掃描:INDEX RANGE SCAN
select * from ob1 where object_id<100;

索引的跳躍掃描:複合列索引，前置列在where子句中不能出現，索引列要擁有列層級的統計資訊
analyze table ob1 compute statistics for all indexed columns;
create index i_ob1 on ob1 (owner,object_type,object_name);
select owner,object_type,object_name from ob1 where object_type=‘WINDOWS‘;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
開啟sql自動跟蹤
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SET AUTOT ON
SET AUTOT TRACE
SET AUTOT TRACE EXP
SET AUTOT TRACE STAT
關閉sql自動跟蹤
SET AUTOT OFF

select /*+index(dept pk_dept)*/ * from scott.dept;


select distinct FILE#,BLOCK# from v$bh where objd=dba_objects.data_object_id;

select object_name,data_object_id from dba_objects where object_name in (‘DEPT‘,‘PK_DEPT‘);
select count(*) from v$bh where objd=87106;
select FILE#,BLOCK# from v$bh where objd=87106;
select distinct FILE#,BLOCK# from v$bh where objd=87106;

本文出自 “梁小明的部落格” 部落格，請務必保留此出處http://7038006.blog.51cto.com/7028006/1870282

談一下oracle索引