Oracle性能：数据的访问路径

数据的访问路径

 

Oracle执行计划详解 http://www.cnblogs.com/jianggc/articles/2029854.html

 

 

概念:

rowid:伪列

recursive sql:triger

predicate（谓词）: where 限制条件

row source （行源）: 返回的结果集

driving table（驱动表|外部表）:row source 数据量较少，作为 row source 1。

probe table（被探查表|内部表）: row source 数据量较多，作为 row source 2。

concatenated index （组合索引）: where 限制条件 使用了包含 先导列 的过滤条件 才会使用 该索引，否则不使用。

selectivity（选择性）:  列域值/表行数 接近于 1  的列 适合 选择来创建索引。

 

Oracle数据访问方式:

1. Full Table Scan,FTS

2. Table Access by rowid (rowid lookup)

3. Index Scan (index lookup)

  3.1 index unique scan

  3.2 index range scan

  3.3 index full scan

  3.4 index fast full scan

 

--建表

create table td_brand(

  brand_code varchar2(32),

  brand_name varchar2(100)

) tablespace tbs_test;

alter table td_brand

  add constraints pk_cons_brand_code primary key(brand_code);

 

数据访问方式的测试场景:

cmd

echo %NLS_LANG%

set NLS_LANG = AMSEICAN_AMERICA.AL32UTF8

 

sqlplus /nolog

conn zzhtest/zzhtest@ssa_234

set pagesize 50;

set linesize 1000;

set timing on;

set autotrace traceonly explain;

 

1. Full Table Scan,FTS

1.1 多行多列（查询数据量 超过全表的 5% - 10%）

select * from td_brand; --pk约束

select brand_code from td_brand; --pk约束（查询数据 完全在索引中就能找到）

1.2 指定使用/不使用 索引

/*+ index ( table [index [index]…])*/ :指定使用哪些索引

/*+ no_index ( table [index [index]…])*/ :指定不使用哪些索引

 

select /*+ index(t pk_cons_brand_code) */ * from td_brand t;  --pk约束

select /*+ no_index(td_brand pk_cons_brand_code) */ brand_code from td_brand;  --pk约束

 

1.3 并行查询

select /*+ parallel(t,2) */ * from td_brand t;  --走的 全表扫描，开启了 并行查询你 

select /*+ parallel(t,2) */ brand_code from td_brand t;  --走的 索引扫描，不会开启 并行查询

select /*+ no_index(t pk_cons_brand_code) parallel(t,2) */ brand_code from td_brand t;  --走的 全表扫描，开启了 并行查询

小结:

1) Table Access Full 的数据访问方式 才能开启  并行查询

2) 若 from td_brand t 则 hint 里必须使用 表别名 t，否则hint无效。

 

2.  Table Access by Rowid (rowid lookup)

2.1 使用了 where rowid = '' 的过滤条件

select t.*,t.rowid from td_brand t;

select * from td_brand where rowid ='';

 

3. Index Scan

--添加 约束

--alter table td_brand drop constraints pk_cons_brand_code;

alter table td_brand add  constraints pk_cons_brand_code primary key(brand_code);

alter table td_brand add  constraints uq_cons_brand_code unique(brand_code);

 

--创建 索引

--drop index uq_idx_brand_code;

create index uq_idx_brand_code on td_brand(brand_code);  --UNIQUE索引

create index nuq_idx_brand_code on td_brand(brand_code);  --NONUNIQUE索引

 

create index uq_idx_conc on td_brand(brand_code,brand_name);  --UNIQUE索引(组合索引)

create index nuq_idx_conc on td_brand(brand_code);  --NONUNIQUE索引(组合索引)

--查看 约束

user_constraints

user_cons_columns

--查看 索引

user_indexes

user_ind_columns

--重新收集 表的 统计信息(表结构改变过)

execute dbms_stats.gather_table_stats(ownname => upper('zzhtest'),tabname => upper('td_brand'),method_opt => 'for all columns size auto');

 

 

3.1 使用了“ where 索引列”  的过滤条件，且提取的数据量 少于 全表的 5%。

select * from td_brand;  --pk约束,unique约束,unique索引,nonunique索引

 

select brand_code from td_brand;  --1.pk约束

select brand_code from td_brand;  --2.unique约束,unique索引,nonunique索引

说明:对于全数据量的投影操作 Index Full Scan 优于 Table Access Full。

 

select brand_code from td_brand where brand_name = 'Apple';  --pk约束,unique约束,unique索引,nonunique索引

select brand_code from td_brand where brand_code <> 'B01';  --pk约束,unique约束,unique索引,nonunique索引

 

3.2 索引 存储了什么？

答:索引 存储了索引列的数据，还存储了rowid。如果只查询索引列的数据，则可以直接从索引中查找，如果还需要查询非索引列，则需要通过rowid查找。

select brand_code from td_brand where brand_code = 'B3';  --1. pk约束,unique约束,unique索引

select brand_code from td_brand where brand_code = 'B3';  --2. nonunique索引 （走不了 唯一索引）

 

select brand_code,brand_name from td_brand where brand_code = 'B3';  --1. pk约束,unique约束,unique索引

select brand_code,brand_name from td_brand where brand_code = 'B3';  --2. nonunique索引 （走不了 唯一索引）

 

 

3.3 索引 本身是已经排序的。所以，使用 index scan 的含有“ order by 索引列 ” 的语句 不用做 SORT ORDER BY 操作。

select brand_code from td_brand where brand_code > 'B1' order by brand_code desc;  --pk约束,unique约束,unique索引,nonunique索引

 

select brand_code from td_brand order by brand_code;  --1. pk约束

select brand_code from td_brand order by brand_code;  --2. unique约束,unique索引,nonunique索引

 

select brand_code,brand_name from td_brand where brand_name > 'Apple' order by 1;  --1. pk约束

select brand_code,brand_name from td_brand where brand_name > 'Apple' order by 1;  --2. unique约束,unique索引,nonunique索引

 

select brand_code,brand_name from td_brand where brand_name > 'Apple' order by 2;  -- pk约束,unique约束,unique索引,nonunique索引

 

【3.2节的实验】

1. UNIQUE索引

  1.1 创建pk约束

  1.2 创建unique约束

  1.3 创建unique索引

2. NOUNIQUE索引

  2.1 创建nounique索引

 

3.4 具体介绍几种种索引扫描

索引唯一扫描（index unique scan）:返回 单行，where限制条件 使用了 "索引列 = "

索引范围扫描（index range scan）:返回 多行， where限制条件 使用了 "索引列 <范围运算符> " ，例如，  <,>,<=,>=,<>,between and

索引全扫描（index full scan）:查询列可以在索引列直接取得

索引快速全扫描（index fast full scan）:与 index full scan 不同的是  index fast full scan 不进行排序，可以多块读，也可以并行读。

索引跳跃式扫描（index skip scan）:使用 concatenated index(组合索引)的 非先导列 做 where 限制条件。

 

3.5 约束与索引的关系

1. pk,unique约束会自动创建同名索引

2. 已有索引的列不可以重复创建索引

 

3.6 索引的分类

Oracle 索引详解 索引分类   http://www.linuxidc.com/Linux/2011-07/38017p2.htm

 

1. 唯一性

unique索引

nonunique索引

2. 索引列

非组合索引

组合索引(concatenated index)

create index conc_idx on td_brand(brand_code,brand_name);

3. 索引的存储结构

4. 是否分区

Oracle-如何构建高效的Oracle Partitionhttp://wenku.baidu.com/view/a697673283c4bb4cf7ecd13e.html?from=search

 

 

global 分区索引

local 分区索引

 

【小结】索引

1. 索引存储了什么

2. 索引对数据访问方式的影响

3. 索引的分类

 

【TIP】3.6 索引的分类 参考笔记"【ORACLE_性能_索引】index"