Oracle 单表访问路径分析

博主： Tehran
发布时间：2022 年 02 月 21 日
256 次浏览
暂无评论
18341字数
分类： Oracle

一全表扫描（ 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 条件中。

索引跳跃式扫描适合于组合索引，比如（ gender ， age ）
当根据组合索引的第一个列 gender （ leading 列）做查询时，通常是可以用的到索引的
当你想使用第二个列 age 作为筛选条件时， oracle 可以使用索引跳跃式扫描
跳跃式扫描适合于第一个列值重复比较多，第二个列唯一值比较多的情况
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 是没有作用的（切记）

作者：chenoracle

最后修改：2022 年 02 月 27 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

Oracle 单表访问路径分析

Tehran • 2022 年 02 月 21 日

<div class="tip share">请注意，本文编写于 794 天前，最后修改于 788 天前，其中某些信息可能已经过时。</div>

<h3>一 全表扫描（ Full Table Scans, FTS ）</h3>1 全表扫描优势： ORACLE 采用 一次读入多个数据块 (database block) 的方式优化全表扫描，而不是只读取一个数据块，这极大的减少了 I/O 总次数，提高了系统的吞吐量。所以利用多块读的方法可以十分高效地实现全表扫描。 需要注意的是只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。 2 使用 FTS 的前提条件： 在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。 3 查询数据库中哪些执行过哪些全表扫描的 SQL <code>1) 查询某一用户下执行过的全表扫描的地址</code><pre><code>select * from v$sql_plan v
where v.operation = 'TABLE ACCESS'
and v.OPTIONS = 'FULL'
and v.OBJECT_OWNER = 'SCOTT'; -- 指定用户下</code></pre><code>2) 通过 hash_value 找出对应的 SQL</code><pre><code>select * from v$sql where hash_value='2475760909';
或者
select * from v$sqlarea where hash_value='2475760909';</code></pre><code>4全表扫描实例（ TABLE ACCESS FULL ）</code><pre><code>SQL&gt; conn scott/tiger
SQL&gt; create table t1 as select level as id,sysdate as hiredate,level*10000 as sal from dual connect by level&lt;=100000;
表已创建。
SQL&gt; set autotrace traceonly
SQL&gt; set linesize 200
SQL&gt; 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(&quot;ID&quot;=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</code></pre>从查询计划我们可以看到所采用的查询方式是“ TABLE ACCESS FULL ”，这就是全表扫描。也正是因为采用全表扫描，所以 consistent gets 会很大。<h3>二 通过 ROWID 访问表（ table access by ROWID ）</h3>原理： 使用 ROWID 进行查询的前提是我们明确知道了一个正确的 ROWID ，然后通过这个 ROWID 进行查询。所以这里所提到的所有 ROWID 必须是真实存在的，否则会报错。 ROWID 指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过 ROWID 来存取数据可以快速定位到目标数据上，是 Oracle 存取单行数据的最快方法。 为了通过 ROWID 存取表， Oracle 首先要获取被选择行的 ROWID ，或者从语句的 WHERE 子句中得到，或者通过表的一个或多个索引的索引扫描得到。 Oracle 然后以得到的 ROWID 为依据定位每个被选择的行。 下面给出使用 rowid 访问表的实例。<code>1) 单个 rowid 的情形</code><pre><code>SQL&gt; select a.*,rowid from t1 a where id=23;
ID HIREDATE SAL ROWID
---------- -------------- ---------- ------------------
23 12-8 月 -15 230000 AAAnqVAAEAAEH9sAAW
SQL&gt; 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</code></pre>查询计划中说明该查询是的表访问方式是” TABLE ACCESS BY USER ROWID “，也就是直接通过 USER ROWID 来访问，这也是为什么只需要 1 次 consistent gets 的原因。 <code>2) 多个 rowid 的倾向 </code><pre><code>SQL&gt; 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&gt; 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</code></pre>查询计划分析： 上面的执行计划中出现了 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&gt; 若希望禁用 inlist iterator, 可设置 10157 等待事件： alter session set events ‘10157 trace name context forever, level 1’; b&gt; use_concat 也可用来禁用 inlist iterator 操作，而强制其使用 concatenation 操作。但实际中会发现该 hint 常常不能如预期地生效，所以使用后还要注意验证结果是否如预期发生了变化。<h3>三 索引扫描（ Index scan ）</h3>我们先通过 index 查找到数据对应的 rowid 值 ( 对于非唯一索引可能返回多个 rowid 值 ) ， 然后根据 rowid 直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找 (index lookup) 。 一个 rowid 唯一的表示一行数据，该行对应的数据块是通过一次 i/o 得到的，在此情况下该次 i/o 只会读取一个数据库块。 在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的 ROWID 值。 索引扫描可以由 2 步组成： (1) 扫描索引得到对应的 rowid 值。 (2) 通过找到的 rowid 从表中读出具体的数据。 根据索引的类型与 where 限制条件的不同，有 4 种类型的索引扫描。<h4>1 索引范围扫描 (INDEX RANGE SCAN)</h4>使用 index rang scan 的 3 种情况： 1)在唯一索引列上使用了range操作符(&gt; &lt; &lt;&gt; &gt;= &lt;= 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&gt; show user USER 为 "SCOTT" SQL&gt; create index ix_t1_id on t1(id); 索引已创建。 -- 查询 ID 在 99 到 999 之间的记录 SQL&gt; select * from t1 where id between 99 and 999; 已选择 901 行。<code>执行计划</code><pre><code>----------------------------------------------------------
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(&quot;ID&quot;&gt;=99 AND &quot;ID&quot;&lt;=999)
Note
-----
- dynamic sampling used for this statement</code></pre><code>统计信息</code><pre><code>----------------------------------------------------------
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&gt; select * from t1 where id=9999;</code></pre><code>执行计划</code><pre><code>----------------------------------------------------------
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(&quot;ID&quot;=9999)
Note
-----
- dynamic sampling used for this statement</code></pre><code>统计信息</code><pre><code>----------------------------------------------------------
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</code></pre><h4>2 索引唯一扫描 (INDEX UNIQUE SCAN)</h4>通过 唯一索引 查找一个数值经常返回单个 ROWID 。如果存在 UNIQUE 或 PRIMARY KEY 约束 ( 它保证了语句只存取单行 ) 的话， Oracle 经常实现唯一性扫描。<pre><code>SQL&gt; show user
USER 为 &quot;SCOTT&quot;
SQL&gt; drop index ix_t1_id;
索引已删除。
SQL&gt; alter table t1 add constraint pk_t1_id primary key(id);
表已更改。
SQL&gt; 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(&quot;ID&quot;=9999)</code></pre><code>统计信息</code><pre><code>----------------------------------------------------------
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</code></pre><h4>3 索引全扫描 (index full scan)(ifs)</h4>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&gt; SELECT id from t1 where id is not null order by id; 已选择 100000 行。<code>执行计划</code><pre><code>----------------------------------------------------------
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(&quot;ID&quot; IS NOT NULL)
Note
-----
- dynamic sampling used for this statement</code></pre><code>统计信息</code><pre><code>----------------------------------------------------------
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;</code></pre><h4>4 索引快速扫描 (index fast full scan)(ffs)</h4>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&gt; select id from t1 where id&gt;=5000; 已选择 95001 行。 <code>执行计划</code><pre><code>----------------------------------------------------------
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(&quot;ID&quot;&gt;=5000)
Note
-----
- dynamic sampling used for this statement</code></pre><code>统计信息</code><pre><code>----------------------------------------------------------
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</code></pre><h4>5 索引跳跃式扫描 (index skip scan)</h4>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 条件中。<ol><li>索引跳跃式扫描适合于组合索引，比如（ gender ， age ）</li><li>当根据组合索引的第一个列 gender （ leading 列）做查询时，通常是可以用的到索引的</li><li>当你想使用第二个列 age 作为筛选条件时， oracle 可以使用索引跳跃式扫描</li><li>跳跃式扫描适合于第一个列值重复比较多，第二个列唯一值比较多的情况 hint SQL&gt; select /+ index_ss(t1,ix_type_id) / object_id from t1 where object_id=10;</li></ol><code>执行计划</code><pre><code>----------------------------------------------------------
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(&quot;OBJECT_ID&quot;=10)
filter(&quot;OBJECT_ID&quot;=10)
Note
-----
- dynamic sampling used for this statement</code></pre><code>统计信息</code><pre><code>----------------------------------------------------------
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</code></pre><code>全表扫描</code> SQL&gt; select /+ full (t1)/ object_id from t1 where object_id=10;<code>执行计划</code><pre><code>----------------------------------------------------------
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(&quot;OBJECT_ID&quot;=10)
Note
-----
- dynamic sampling used for this statement</code></pre><code>统计信息</code><pre><code>----------------------------------------------------------
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</code></pre>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 是没有作用的（切记）作者：<a href="https://uuks.cn/go/aHR0cDovL2Jsb2cuaXRwdWIubmV0LzI5Nzg1ODA3L3ZpZXdzcGFjZS0xNzY5OTQ2Lw" target="_blank" >chenoracle</a>

Oracle 单表访问路径分析

一全表扫描（ Full Table Scans, FTS ）

二通过 ROWID 访问表（ table access by ROWID ）

三索引扫描（ Index scan ）

1 索引范围扫描 (INDEX RANGE SCAN)

2 索引唯一扫描 (INDEX UNIQUE SCAN)

3 索引全扫描 (index full scan)(ifs)

4 索引快速扫描 (index fast full scan)(ffs)

5 索引跳跃式扫描 (index skip scan)

相关文章

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

handsome 换字体

Oracle 数据库常用安装包合集（包含基础版和最新补丁集202107）

超全 Linux 安装包合集(Centos Redhat Oracle Linux)

本站typecho 主题版本修改的东西(8.40版本和typecho开发版1.2)

影视资源站接口

Oracle ASM表空间管理

谷歌广告（Google AdSense）申请指南

DOS下判断前一天日期

MySQL中跨库查询

crontab 定时任务

Oracle 单表访问路径分析

一 全表扫描（ Full Table Scans, FTS ）

二 通过 ROWID 访问表（ table access by ROWID ）

三 索引扫描（ Index scan ）

1 索引范围扫描 (INDEX RANGE SCAN)

2 索引唯一扫描 (INDEX UNIQUE SCAN)

3 索引全扫描 (index full scan)(ifs)

4 索引快速扫描 (index fast full scan)(ffs)

5 索引跳跃式扫描 (index skip scan)

相关文章

发表评论 取消回复 # 请输入真实的邮箱地址便于接收回复

Oracle 单表访问路径分析

一全表扫描（ Full Table Scans, FTS ）

二通过 ROWID 访问表（ table access by ROWID ）

三索引扫描（ Index scan ）

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复