oracle SQL表执行路径（表扫描方式）

oracle SQL表执行路径（表扫描方式）

 

       优化器在形成执行计划时需要做的一个重要选择是如何从数据库查询出需要的数据。对于sql语句存取的任何表中的任何行，可能存在许多存取路径，通过它们可以定位和查询出需要的数据。优化器会选择其中自认为最优化的路径。在物理层，oracle读取数据，一次读取的最小单位为数据库块，一次读取的最大值由操作系统一次I/O的最大值与mulitblock参数共同决定，所以即使只需要一行数据，也是将该行所在的数据库块读入内存。逻辑上，oralce用如下存取方法访问数据：1）全表扫描（Full Table Scans,FTS）;2）通过ROWID的表存取（Table Access by RowID或rowid lookup）；3）索引扫描（Index Scan或Index Lookup）。

 

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

为实现全表扫描，oracle读取表中所有的行，并检查每一行是否满足语句的where限制条件。Oracle顺序地读取了分配给表的每个数据块，直到读到表的最高水线处(high water mak,HWM)。一个多块读操作可以使一次I/O能读取多块数据块（由db_block_multiblock_read_count参数设定），而不是只读取一个数据块，这可以极大减少I/O的总次数，提高系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只能被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有所改善，所以一般要使用truncate命令使得HWM值归为0。

适用情况：在较大的表上不建议适用全表扫描，除非取出的数据比较多，超过总量的5%-10%，或者想适用并行查询功能时。

Eg:
SQL> explain plan for select * from lpx_share; Explained. Elapsed: 00:00:00.05 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 3739818362 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9173 | 1415K| 40 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| LPX_SHARE | 9173 | 1415K| 40 (0)| 00:00:01 | ------------------------------------------------------------------------------- Note ----- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- - dynamic sampling used for this statement 12 rows selected.

2）         通过ROWID的表存取（Table Access by RowID或rowid lookup）

数据行的rowid指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过rowid来存取数据可以快速定位到目标数据上，是oracle存取单行数据的最快方法。

为了通过rowid存取表，oracle首先要获取被选择行的rowid，或者从语句的where子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的rowid为依据定位每个被选择的行。这种存取方法一次I/O只能读取一个数据块。

Eg:

SQL> explain plan for select * from lpx_share where rowid = 'AAAaRQAATAAGgQTAAA'; Explained. Elapsed: 00:00:00.02 SQL> SELECT * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 710339927 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 170 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| LPX_SHARE | 1 | 170 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- 8 rows selected. Elapsed: 00:00:00.01

3）         索引扫描（Index Scan或Index Lookup）

首先通过index查找到数据对应的rowid值，然后根据rowid直接从表中得到具体的数据，称为索引扫描或索引查找。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次I/O得到的，故该次I/O只读取一个数据库块。在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的rowid值。索引扫描可以由2步组成：a）扫描索引得到对应的rowid值；b）通过找到的rowid从表中读取具体的数据。每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数据都已经cache到内存中，所以第一步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是，对于第二步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，是极其耗时的。所以如果对大表进行索引扫描，取出的数据如果大于总量的5%-10%，使用索引扫描效率会下降很多。

根据索引的类型与where限制条件的不同，索引扫描可以分为以下几种：3.1）索引唯一扫描（index unique scan）；3.2）索引范围扫描（index rang scan）；3.3）索引全扫描（index full scan）；3.4）索引快速扫描（index fast full scan）。

        3.1）索引唯一扫描（index unique scan）

通过唯一索引查找数据经常返回单个rowid。如果该唯一索引有多个列组成（即组合索引），则至少要有组合索引的引导列参与到该查询中。如果结果只返回一行，则为索引唯一扫描。

Eg:

SQL> create unique index object_id_ind on lpx_share(object_id); Index created. Elapsed: 00:00:00.25 SQL> explain plan for select * from lpx_share where object_id = 115; Explained. Elapsed: 00:00:00.04 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1490130773 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| LPX_SHARE | 1 | 158 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | OBJECT_ID_IND | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------- 2 - access("OBJECT_ID"=115) 14 rows selected. Elapsed: 00:00:00.00

3.2）索引范围扫描（index rang scan）

使用一个索引存取多行数据，或者使用组合索引结果返回多行记录，则该存取方法为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况是在where子句中使用了范围操作符<，>，<=，>=，between and 等。使用索引范围扫描的情况：a)在唯一索引列上使用了范围操作符<，>，<=，>=，between and 等。b)在组合索引上，只使用部分列进行查询，导致查询出多行。c)对于非唯一索引列进行的任务查询。Eg:

SQL> explain plan for select * from lpx_share where object_id between 99 and 200; Explained. Elapsed: 00:00:00.14 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 761856627 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 474 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| LPX_SHARE | 3 | 474 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | OBJECT_ID_IND | 3 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT --------------------------------------------------- 2 - access("OBJECT_ID">=99 AND "OBJECT_ID"<=200) Note ----- - dynamic sampling used for this statement 18 rows selected. Elapsed: 00:00:00.00

3.3）索引全扫描（index full scan）

    在某些情况下，可能需要进行索引全扫描而不是范围扫描，全索引扫描只有在CBO模式下才能有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。Eg:

SQL> create index obj_name_type on lpx_share(object_name,object_type); Index created. Elapsed: 00:00:00.12 SQL> explain plan for select object_type,object_name from lpx_share order by object_name,object_type; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1029267338 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 693K| 18M| 4175 (1)| 00:00:51 | 1 | INDEX FULL SCAN | OBJ_NAME_TYPE | 693K| 18M| 4175 (1)| 00:00:51 -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 12 rows selected

3.4）索引快速扫描（index fast full scan）

    扫描索引中的所有数据块，与索引全扫描类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回的。在这种存取方法中，可以使用多快读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。Eg:

SQL> explain plan for select object_type,object_name from lpx_share; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3080491366 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 693K| 18M| 1135 (1)| 00:0 | 1 | INDEX FAST FULL SCAN| OBJ_NAME_TYPE | 693K| 18M| 1135 (1)| 00:0 -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 12 rows selected