oracle讀取數(shù)據(jù)的最小單位是塊.oracle讀取數(shù)據(jù)的最大限制取決于OS和oracle對(duì)多塊讀I/O的限制(db_file_multiblock_read_count).
物理上來(lái)說(shuō),一個(gè)sql讀取某個(gè)記錄,得將記錄讀取到DB Cache中,然后才能從中或者,這個(gè)稱為物理讀.如果這個(gè)數(shù)據(jù)已經(jīng)存在于DB Cache中,那么前臺(tái)進(jìn)程可以直接沖DB Cache中讀取數(shù)據(jù),這個(gè)稱謂邏輯讀.
邏輯上來(lái)說(shuō),有3種途徑讀取數(shù)據(jù):全表掃描(full table scan),索引掃描,通過(guò)rowid直接訪問(wèn).在查看執(zhí)行計(jì)劃時(shí),可以通過(guò)table access來(lái)查看oracle訪問(wèn)某個(gè)表的方法.
1.全表掃描:整個(gè)表被掃描,直到HWM標(biāo)示的位置.進(jìn)行全表掃描時(shí),是采用多塊讀的方式,多塊讀由db_file_multiblock_read_count參數(shù)控制.
2.索引掃描:從索引中可以獲取數(shù)據(jù)的rowid,通過(guò)rowid直接定位到數(shù)據(jù).(rowid可以唯一的定位到某一條記錄的物理位置)
常見(jiàn)的索引訪問(wèn)模式:
index unique scan:一般是PK或者唯一性索引訪問(wèn)
index range scan:一般在查詢條件中存在范圍條件
index full scan:按照索引的順序進(jìn)行全掃描,掃描出來(lái)的數(shù)據(jù)是有順序的
index fast full scan:掃描索引的所有塊,反回的數(shù)據(jù)不是按照索引順序的
index skip scan:索引跳躍掃描.where條件的字段不是索引的鍵值的第一個(gè)鍵(9i之后開(kāi)始支持)
3.rowid訪問(wèn):這是數(shù)據(jù)訪問(wèn)的最快方式
以下為索引訪問(wèn)的試驗(yàn):
1.創(chuàng)建一個(gè)表,并分別創(chuàng)建3個(gè)索引:唯一索引,一般索引,組合索引
SQL> create table test as select * from dba_objects;
表已創(chuàng)建。
SQL> create unique index idx_1_unique on test(object_id);
索引已創(chuàng)建。
SQL> create index idx_2_normal on test(owner);
索引已創(chuàng)建。
SQL> create index idx_3_compose on test(owner,object_name,object_type);
索引已創(chuàng)建。
SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST');
PL/SQL 過(guò)程已成功完成。
SQL> set autot trace2.對(duì)于唯一索引,發(fā)生index range scan的時(shí)候就是返回多行記錄,where 后面有 >,<,between ..and..,如果為=就返回一行
SQL> select owner from test where object_id=10;
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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行。
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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.對(duì)于非唯一索引,即使where后面的限制條件是=,但是有可能返回多行，所以進(jìn)行index range scan
SQL> select owner from test where owner='SCOTT';
已選擇7行。
執(zhí)行計(jì)劃
----------------------------------------------------------
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')
統(tǒng)計(jì)信息
----------------------------------------------------------
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.查詢所需要的信息可以通過(guò)索引 IDX_3_COMPOSE獲得,并且where后面沒(méi)有引導(dǎo)列owner,而且返回的行數(shù)很少(這里只有一行),所以CBO選擇index skip scan
SQL> select owner, object_name,object_type from test where object_name='EMP' ;
執(zhí)行計(jì)劃
----------------------------------------------------------
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')
統(tǒng)計(jì)信息
----------------------------------------------------------
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.查詢所需的信息可以通過(guò)索引IDX_3_COMPOSE獲得,并且where后面沒(méi)有引導(dǎo)列owner,而且返回的行數(shù)較多(1701行),所以CBO選擇index fast full scan,這樣避免了全表掃描
SQL> select owner, object_name,object_type from test where object_type='INDEX';
已選擇1779行。
執(zhí)行計(jì)劃
----------------------------------------------------------
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')
統(tǒng)計(jì)信息
----------------------------------------------------------
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 閱讀(187)
評(píng)論(0) 編輯 收藏