Oracle单表访问路径
ORACLE 采用三种访问表中记录的方式 :
1 全表扫描（Full Table Scans, FTS）
2 通过ROWID访问表（table access by ROWID）
3 索引扫描（Index scan）
1)索引范围扫描(INDEX RANGE SCAN)
2)索引唯一扫描(INDEX UNIQUE SCAN)
3)索引全扫描(index full scan)
4)索引快速扫描(index fast full scan)
5)索引跳跃式扫描(index skip scan)
一 全表扫描（Full Table Scans, FTS）
1 全表扫描优势：
ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量。所以利用多块读的方法可以十分高效地实现全表扫描。
需要注意的是只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。
2 使用FTS的前提条件：
在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。
3 查询数据库中哪些执行过哪些全表扫描的SQL
1)查询某一用户下执行过的全表扫描的地址
select * from v$sql_plan v
where v.operation = 'TABLE ACCESS'
and v.OPTIONS = 'FULL'
and v.OBJECT_OWNER = 'SCOTT'; --指定用户下
2)通过hash_value找出对应的SQL
select * from v$sql where hash_value='2475760909';
或者
select * from v$sqlarea where hash_value='2475760909';
4 全表扫描实例（TABLE ACCESS FULL）
SQL> conn scott/tiger
SQL> create table t1 as select level as id,sysdate as hiredate,level*10000 as sal from dual connect by level<=100000;
表已创建。
SQL> set autotrace traceonly
SQL> set linesize 200
SQL> select * from t1 where id=23;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 140 | 77 (4)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 4 | 140 | 77 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=23)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
393 consistent gets
0 physical reads
0 redo size
524 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从查询计划我们可以看到所采用的查询方式是“TABLE ACCESS FULL”，这就是全表扫描。也正是因为采用全表扫描，所以consistent gets会很大。
二 通过ROWID访问表（table access by ROWID）
原理：
使用ROWID进行查询的前提是我们明确知道了一个正确的ROWID，然后通过这个ROWID进行查询。所以这里所提到的所有ROWID必须是真实存在的，否则会报错。
ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。
为了通过ROWID存取表，Oracle首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。
下面给出使用rowid访问表的实例。
1)单个rowid的情形
SQL> select a.*,rowid from t1 a where id=23;
ID HIREDATE SAL ROWID
---------- -------------- ---------- ------------------
23 12-8月 -15 230000 AAAnqVAAEAAEH9sAAW
SQL> select * from t1 where rowid='AAAnqVAAEAAEH9sAAW';
执行计划
----------------------------------------------------------
Plan hash value: 487051824
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
524 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
查询计划中说明该查询是的表访问方式是”TABLE ACCESS BY USER ROWID“，也就是直接通过USER ROWID来访问，这也是为什么只需要1次consistent gets的原因。
2)多个rowid的倾向
SQL> select a.*,rowid from t1 a where id in(23,999);
ID HIREDATE SAL ROWID
---------- -------------- ---------- ------------------
23 12-8月 -15 230000 AAAnqVAAEAAEH9sAAW
999 12-8月 -15 9990000 AAAnqVAAEAAEH9uAEs
SQL> select * from t1 where rowid in('AAAnqVAAEAAEH9sAAW','AAAnqVAAEAAEH9uAEs');
执行计划
----------------------------------------------------------
Plan hash value: 379112920
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
568 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
查询计划分析：
上面的执行计划中出现了INLIST ITERATOR，即INLIST迭代，该操作说明其子操作多次重复时，会出现该操作。
由于我们使用了in运算，且传递了2个rowid，故出现INLIST迭代操作，迭代操作意味着条件中的对象列表一个接一个的迭代传递给子操作。
Inlist iterator：
Inlist iterator操作是oracle为inlist查询提供的一种解决方案：即查询谓词为：
where indexed_column in (:1, :2, :3)
对于这种查询，oracle一般有两种解决方法：inlist iterator 和 concatenation.
1)concatenation(级联)
对于concatenation(级联),就是为inlist中的每个值执行一次索引查询，然后将结果集采用union all的方式合并。
2)inlist iterator(inlist 迭代)
而inlist iterator,oracle的解释是：
“The INLIST ITERATOR operation iterates over the next operation in the plan for each value in the IN-list predicate.”。
按oracle的说法是这种算法更高效。实际测试中也会发现inlist iterator操作的cpu cost和buffer reads更低。
其他：
a> 若希望禁用inlist iterator,可设置10157等待事件：
alter session set events ‘10157 trace name context forever, level 1’;
b> use_concat也可用来禁用inlist iterator操作，而强制其使用concatenation操作。但实际中会发现该hint常常不能如预期地生效，所以使用后还要注意验证结果是否如预期发生了变化。
三 索引扫描（Index scan）
我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，
然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。
一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。
在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。
索引扫描可以由2步组成：
(1) 扫描索引得到对应的rowid值。
(2) 通过找到的rowid从表中读出具体的数据。
根据索引的类型与where限制条件的不同，有4种类型的索引扫描。
1 索引范围扫描(INDEX RANGE SCAN)
使用index rang scan的3种情况：
1)在唯一索引列上使用了range操作符(> < <> >= <= between)
2)在组合索引上，只使用部分列进行查询，导致查询出多行
3)对非唯一索引列上进行的任何查询。
INDEX RANGE SCAN是范围扫描，举个例子，有1到100，分5个范围，要查询45就要到第3的范围里查，这样会很快
Index Unique Scan和Index Range Scan在B Tree上的搜索路径是一样的，只是Index Unique Scan在找到应该含有要找的Index Key的block后便停止了搜索，因为该键是唯一的而Index Range Scan还要循着指针继续找下去直到条件不满足时。并且，Index Range Scan只是索引上的查询，与是否扫描表没有关系 。如果所选择的列都在index上就不用去scan table(因为直接通过索引就能得到想要的数据)，如果扫描到表, 必然还有一个table access by rowed(回表)，正如上例所展示的，通过index range scan访问的表可以通过按照索引顺序重新建立表 来提高效率；
原因有二：
1) 如果你只读一部分数据，假设20% ，如果表数据顺序混乱，实际上可能把整个表都读进来了，如果表顺序和索引一致，则只需要读进 20%的表的block就够了。这是简单情况。
2)复杂情况下，顺序混乱的时候block可能在整个查询的不同时间点多次反复访问,当再次要访问这个块的时候说不定已经被换出去了，或者被修改过了，那代价更大,而如果顺序一样，对同一个block的访问集中在一段连续的很短的时间内，变数少，不会对同一个block产生多次IO。
--为t1表中的id字段创建索引 IX_T1_ID
SQL> show user
USER 为 "SCOTT"
SQL> create index ix_t1_id on t1(id);
索引已创建。
--查询ID在99到999之间的记录
SQL> select * from t1 where id between 99 and 999;
已选择901行。
执行计划
----------------------------------------------------------
Plan hash value: 897798478
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 901 | 31535 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 901 | 31535 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_T1_ID | 901 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=99 AND "ID"<=999)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
127 consistent gets
0 physical reads
0 redo size
27210 bytes sent via SQL*Net to client
1045 bytes received via SQL*Net from client
62 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
901 rows processed
SQL> select * from t1 where id=9999;
执行计划
----------------------------------------------------------
Plan hash value: 897798478
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 35 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_T1_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=9999)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
79 consistent gets
1 physical reads
0 redo size
530 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2 索引唯一扫描(INDEX UNIQUE SCAN)
通过唯一索引查找一个数值经常返回单个ROWID。如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。
SQL> show user
USER 为 "SCOTT"
SQL> drop index ix_t1_id;
索引已删除。
SQL> alter table t1 add constraint pk_t1_id primary key(id);
表已更改。
SQL> select * from t1 where id=9999;
执行计划
----------------------------------------------------------
Plan hash value: 3736029472
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 35 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T1_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=9999)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
1 physical reads
0 redo size
434 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3 索引全扫描(index full scan)(ifs)
Full Index Scan
In a full index scan, the database reads the entire index in order.
A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified.
A full scan can eliminate sorting because the data is ordered by index key.
原理：
1)ORACLE定位到索引的ROOT BLOCK
2)然后到BRANCH BLOCK（如果有的话）
3)再定位到第一个LEAF BLOCK, 然后根据LEAF BLOCK的双向链表顺序读取。
它所读取的块都是有顺序的，也是经过排序的，不能并行访问索引。
关于INDEX FULL SCAN和INDEX FAST FULL SCAN的区别在于：
前者在对索引进行扫描的时候会考虑大索引的结构，而且会按照索引的排序，
而后者则不会，INDEX FAST FULL SCAN不会去扫描根块和分支块，对索引像访问堆表一样访问，所以这两个扫描方式用在不同的场合。
如果存在ORDER BY这样的排序，INDEX FULL SCAN是合适的，如果不需要排序，那INDEXFAST FULL SCAN效率是更高的。
SQL> SELECT id from t1 where id is not null order by id;
已选择100000行。
执行计划
----------------------------------------------------------
Plan hash value: 333785393
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 111K| 1413K| 239 (2)| 00:00:03 |
|* 1 | INDEX FULL SCAN | IX_T1_ID | 111K| 1413K| 239 (2)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
6948 consistent gets
0 physical reads
0 redo size
1455961 bytes sent via SQL*Net to client
73711 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
Hint
SELECT /*+ index_ifs(t1 ix_t1_id) */ id from t1 where id is not null order by id;
4 索引快速扫描(index fast full scan)(ffs)
Fast Full Index Scan
A fast full index scan is a full index scan in which the database reads the index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint.
A fast full scan is faster than a normal full index scan because it can use multiblock I/O and can run in parallel just like a table scan.
The database cannot perform fast full index scans of bitmap indexes.
原理：
从段头开始，读取包含位图块，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK，
读取的顺序完全有物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。
查询某个表记录总数的时候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的。
2、强制使用INDEX FAST FULL SCAN
SELECT /*+ index_ffs(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL order by n1;
select /*+ index_ffs(t1 ix_t1_id) */ id,hiredate from t1 where id=5000;
SQL> select id from t1 where id>=5000;
已选择95001行。
执行计划
----------------------------------------------------------
Plan hash value: 923087333
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105K| 1344K| 56 (6)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IX_T1_ID | 105K| 1344K| 56 (6)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">=5000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
6628 consistent gets
0 physical reads
0 redo size
1387824 bytes sent via SQL*Net to client
70048 bytes received via SQL*Net from client
6335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
95001 rows processed
5 索引跳跃式扫描(index skip scan)
Index Skip Scans
Index skip scans improve index scans by no nprefix columns. Often, scanning index blocks is faster than sc anning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageo us if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
当查询可以通过组合索引得到结果，而且返回结果很少，并且where条件中没有包含索引引导列的时候，可能执行index skip scan。
索引跳跃式扫描发生的条件:
1)必须是组合索引。
2)引导列没有出现在where条件中。
1.索引跳跃式扫描适合于组合索引，比如（gender，age）
2.当根据组合索引的第一个列gender（leading 列）做查询时，通常是可以用的到索引的
3.当你想使用第二个列age作为筛选条件时，oracle可以使用索引跳跃式扫描
4.跳跃式扫描适合于第一个列值重复比较多，第二个列唯一值比较多的情况
hint
SQL> select /*+ index_ss(t1,ix_type_id) */ object_id from t1 where object_id=10;
执行计划
----------------------------------------------------------
Plan hash value: 2199569833
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 104 | 232 (0)| 00:00:03 |
|* 1 | INDEX SKIP SCAN | IX_TYPE_ID | 8 | 104 | 232 (0)| 00:00:03 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=10)
filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
全表扫描
SQL> select /*+ full (t1)*/ object_id from t1 where object_id=10;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 104 | 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 8 | 104 | 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
739 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ORACLE官方说，在前导列唯一值较少的情况下，才会用到index skip can。这个其实好理解，就是入口要少。
ORACLE也承认skip scan没有直接索引查询快，但可以这样说，相比于整个表扫描(table scan)，索引跳跃式扫描的速度要快得多。
HINT总结：
1.全表扫描hint full(table_name)
2.索引hint index(table_name index_name)
3.索引快速扫描hint index_ffs(table_name index_name)
FAST FULL SCN。
4.索引跳跃扫描hint index_ss(table_name index_name)
INDEX SKIP SCAN
5.表关联hint user_nl(table_name1 table_name2)
6.表关联hint use_hash(table_name1 table_name2)
7.表关联hint user_merge(table_name1 table_name2)
8.表顺序hint leading(table_name1 table_name2)
9.数据加载hint append()
10.数据返回模式hint first_rows
使用hint时切记查看表名是否使用了别名，如果使用了别名，记得要在hint中也要使用别名，否则hint是没有作用的（切记）
参考：http://www.cnblogs.com/xwdreamer/archive/2012/06/13/2547825.html
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1769946/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-1769946/
1922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
