shoug oracle,oracle 浅谈索引

今天第一次参加shoug，听了一些关于sql性能调优的东西，结合自己的认识和理解。整理一下

首先oracle三大范式：

表中每一条记录的每个一个字段值，都是不可再分的最小数据单位(原子不可分解)。

每一个非主键字段必须完全依赖于主键。

所有非主键字段对任何主键字段都不存在传递依赖。

注：第二范式基于第一范式，第三范式基于第二范式

1.B-Tree树索引

什么是b-tree索引：用官方的话来讲：An index organized like an upside-down tree. A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. The “B” stands for “balanced” because all leaf blocks automatically stay at the same depth。

b-tree索引一般来说都是独自行动的。 如果你的sql有几个字段都用了索引，b-tree索引会选择一个选择度最高的去用，而不用别的。并不是配合一起使用，如果你的执行计划真的两个btree索引都走了那么将不会去扫描基表，而是把两个索引做的hashjoin然后再

SELECT tablespace_name FROM test2  WHERE tablespace_name=’USERS’  AND table_name=’TEST1′

B-tree的索引被使用不被使用取决于选择度。 如果选择度很低，oracle也不会走索引的

CREATE TABLE test2 AS SELECT * FROM User_Tables;

INSERT INTO test2 SELECT * FROM User_Tables;

COMMIT;

CREATE INDEX test2_index2 ON test2(tablespace_name);

SELECT /*+index(test2_index2)*/ * FROM test2  WHERE tablespace_name=’USERS’

—————-

oracle觉得使用它的人都是聪明的人。当你使用！=一个值的时候它会认识要被检索的将会很多。所以会去做全表扫描、

SELECT table_name FROM test2 WHERE table_name !=:test1

它是一个Index full scan select 与where子句中出现的所有列必须存在索引才会存在的 如果换成

SELECT * FROM test2 WHERE table_name !=test1 这将是一个table access scan

index full scan也只能说明是我们只查的一个列在select 上用的index 而不是在where上index被使用了。同理<>也是不行的

———

极限选择度  当一个查询 如果

———————

bitmap索引

如果说btree索引是一只虎。那么bitmap就是一群儿狼。虎只有一个王，狼却是一群DROP INDEX test2_index1;

DROP INDEX test2_index2;

CREATE BITMAP INDEX test2_index1 ON test2(table_name);

CREATE BITMAP INDEX test2_index2 ON test2(tablespace_name);

SELECT table_name,tablespace_name FROM test2  WHERE   table_name=’TEST1′ AND tablespace_name=’USERS’

SELECT STATEMENT, GOAL = ALL_ROWS   5 170 2

BITMAP CONVERSION TO ROWIDS   5 170 2

BITMAP AND

BITMAP INDEX SINGLE VALUE UCJMH TEST2_INDEX1

BITMAP INDEX SINGLE VALUE UCJMH TEST2_INDEX2

但是还是要注意选择度 如果选择度低一样不会被走索引

—————————-

fbi function base index

基于函数的索引

DROP INDEX test2_index1;

CREATE  INDEX test2_index1 ON test2(SUBSTR(max_extents,-3,3));

SELECT  * FROM test2 WHERE SUBSTR(max_extents,-3,3)=645;—table access scan

DROP INDEX test2_index1;

CREATE   INDEX test2_index1 ON test2(to_number(SUBSTR(max_extents,-3,3)));

SELECT  * FROM test2 WHERE SUBSTR(max_extents,-3,3)=645;—index range scan

SELECT to_number(SUBSTR(to_char(12345678),-3,3)) FROM dual;

DROP INDEX test2_index1;

CREATE   INDEX test2_index1 ON test2(to_number(SUBSTR(to_char(max_extents),-3,3)));

SELECT  * FROM test2 WHERE SUBSTR(max_extents,-3,3)=645;—index range scan

类型不匹配就不会走 需要把oracle隐式的显式的在建立索引的时候指出来。

如果必须要full scan 要尽量的写只scan一遍。 类似于分析函数 只扫一遍全表