谈一下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 expiredSQL> 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_adj4.索引的聚簇因子：顺序访问索引键值而在表的数据块上面跳转的次数；反映的是通过索引顺序取值对表的数据块访问的次数select * from e02 where empno<46572; --> .203037807select * from e01 where empno<376; --> .00163923empno<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 229204I_E02_EMPNO 1384~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~alter table ob1 modify (object_id number not null)索引的扫描算法和效率：索引的唯一键扫描:INDEX UNIQUE SCANselect * 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 SCANselect * 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_name from ob1 where object_type='WINDOWS';~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~打开sql自动跟踪SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]SET AUTOT ONSET AUTOT TRACESET AUTOT TRACE EXPSET AUTOT TRACE STAT关闭sql自动跟踪SET AUTOT OFFselect /*+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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!