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 閱讀(186)
評論(0) 編輯 收藏