oracle读取数据的最小单位是块.oracle读取数据的最大限制取决于OS和oracle对多块读I/O的限制(db_file_multiblock_read_count).
物理上来说,一个sql读取某个记录,得将记录读取到DB Cache中,然后才能从中或者,这个称为物理读.如果这个数据已经存在于DB Cache中,那么前台进程可以直接冲DB Cache中读取数据,这个称谓逻辑读.
逻辑上来说,有3种途径读取数据:全表扫描(full table scan),索引扫描,通过rowid直接访问.在查看执行计划时,可以通过table access来查看oracle访问某个表的方法.
1.全表扫描:整个表被扫描,直到HWM标示的位置.进行全表扫描时,是采用多块读的方式,多块读由db_file_multiblock_read_count参数控制.
2.索引扫描:从索引中可以获取数据的rowid,通过rowid直接定位到数据.(rowid可以唯一的定位到某一条记录的物理位置)
常见的索引访问模式:
index unique scan:一般是PK或者唯一性索引访问
index range scan:一般在查询条件中存在范围条件
index full scan:按照索引的顺序进行全扫描,扫描出来的数据是有顺序的
index fast full scan:扫描索引的所有块,反回的数据不是按照索引顺序的
index skip scan:索引跳跃扫描.where条件的字段不是索引的键值的第一个键(9i之后开始支持)
3.rowid访问:这是数据访问的最快方式
以下为索引访问的试验:
1.创建一个表,并分别创建3个索引:唯一索引,一般索引,组合索引
SQL> create table test as select * from dba_objects;
表已创建。
SQL> create unique index idx_1_unique on test(object_id);
索引已创建。
SQL> create index idx_2_normal on test(owner);
索引已创建。
SQL> create index idx_3_compose on test(owner,object_name,object_type);
索引已创建。
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST');
PL/SQL 过程已成功完成。
SQL> set autot trace2.对于唯一索引,发生index range scan的时候就是返回多行记录,where 后面有 >,<,between ..and..,如果为=就返回一行
SQL> select owner from test where object_id=10;
执行计划
----------------------------------------------------------
Plan hash value: 4024065456
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_1_UNIQUE | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
406 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 owner from test where object_id<10;
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 3064099465
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 88 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 88 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_1_UNIQUE | 8 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
472 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from clien t
0 sorts (memory)
0 sorts (disk)
8 rows processed
SQL> 3.对于非唯一索引,即使where后面的限制条件是=,但是有可能返回多行，所以进行index range scan
SQL> select owner from test where owner='SCOTT';
已选择7行。
执行计划
----------------------------------------------------------
Plan hash value: 3589364510
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2398 | 14388 | 6 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_2_NORMAL | 2398 | 14388 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER"='SCOTT')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
469 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)
7 rows processed
SQL> 4.查询所需要的信息可以通过索引 IDX_3_COMPOSE获得,并且where后面没有引导列owner,而且返回的行数很少(这里只有一行),所以CBO选择index skip scan
SQL> select owner, object_name,object_type from test where object_name='EMP' ;
执行计划
----------------------------------------------------------
Plan hash value: 3043072055
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 23 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_3_COMPOSE | 2 | 80 | 23 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='EMP')
filter("OBJECT_NAME"='EMP')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
540 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> 5.查询所需的信息可以通过索引IDX_3_COMPOSE获得,并且where后面没有引导列owner,而且返回的行数较多(1701行),所以CBO选择index fast full scan,这样避免了全表扫描
SQL> select owner, object_name,object_type from test where object_type='INDEX';
已选择1779行。
执行计划
----------------------------------------------------------
Plan hash value: 1925096375
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1736 | 69440 | 80 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_3_COMPOSE | 1736 | 69440 | 80 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='INDEX')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
476 consistent gets
0 physical reads
0 redo size
57920 bytes sent via SQL*Net to client
1683 bytes received via SQL*Net from client
120 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1779 rows processed
SQL>
posted on 2011-06-12 01:23
xrzp 阅读(174)
评论(0) 编辑 收藏