【SQL】查詢資料的方式 (一)
SQL> set autot on
SQL> select rowid, object_id
2 from test
3 where rowid ='AAARVKAAEAAAAD8AAA';
ROWID OBJECT_ID
------------------ ----------
AAARVKAAEAAAAD8AAA 28
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2153624467
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY USER ROWID| TEST |
-------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意到統計資訊,只有一個consistent gets,只需要一次讀取即可以獲得資料。這種方式是訪問資料最快的方式。
----當查詢條件無法命中任何索引或者掃描索引的代價大於全表掃描代價的某一比率(optimizer_index_cost_adj)的時候,oracle會選擇使用全表掃描。此時oracle 會讀取一定數量的資料塊,直到hwm。
SQL> select rowid ,object_id
2 from test
3 where rownum =1;
ROWID OBJECT_ID
------------------ ----------
AAARVKAAEAAAAD8AAA 28
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2347100821
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS FULL| TEST |--全表掃描。
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------------------------
SQL> select rowid ,object_id from test where rowid = 'AAARVKAAEAAAAD8AAA';
ROWID OBJECT_ID
------------------ ----------
AAARVKAAEAAAAD8AAA 28
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2153624467
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| TEST | 1 | 17 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
147 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
。。。
3 sorts (memory)
0 sorts (disk)
1 rows processed
-----------------------------------------------
----建立索引
SQL> create table un_test tablespace users as select * from dba_objects;
Table created.
Elapsed: 00:00:02.18
SQL> create unique index i_test_uni on un_test (object_id);
Index created.
Elapsed: 00:00:00.24
---INDEX UNIQUE SCAN 當索引為unique時,每個子葉節點只會指向一條資料.
如果oracle預測到只有0或1條資料時,就會選擇INDEX UNIQUE SCAN .
SQL> select object_id from un_test where object_id =28;
OBJECT_ID
----------
28
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 333614268
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| I_TEST_UNI | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
1 - access("OBJECT_ID"=28)
---INDEX RANGE SCAN
當透過索引查詢資料時,oracle 認為會返回資料可能大於1 ,會進行INDEX RANGE SCAN.
例如進行範圍查詢,< , > ,LIKE,BETWEEN VAL1 AND VAL2 ,IN 等操作。
INDEX RANGE SCAN, INDEX UNIQUE SCAN 會引起db file sequential read 等待
SQL> select object_id from un_test where object_id >5 and object_id <9;
OBJECT_ID
----------
6
7
8
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1694266620
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_TEST_UNI | 1 | 5 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID">5 AND "OBJECT_ID"<9)
Statistics
----------------------------------------------------------
--INDEX FAST FULL SCAN
索引快速掃描和全表掃描類似,一次讀取db_file_multiblock_count 個資料塊。INDEX FAST FULL SCAN
與其他索引掃描不同,它不會從根節點開始,也不讀取節點,而是直接掃描所有子葉節點;也不會一次讀取一個資料塊。
SQL> select object_id from un_test where object_id >3;
53362 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 972078537
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53364 | 260K| 27 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| I_TEST_UNI | 53364 | 260K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3670 consistent gets
0 physical reads
0 redo size
。。。。。。。
0 sorts (memory)
0 sorts (disk)
53362 rows processed
SQL> select object_id from un_test where object_id>500;
52887 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 972078537
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52998 | 258K| 27 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| I_TEST_UNI | 52998 | 258K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">500
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3634 consistent gets
0 physical reads
0 redo size
。。。。。。。。。
0 sorts (memory)
0 sorts (disk)
52887 rows processed
有時如果 的值過小,強制使用索引掃描時,會發生INDEX FULL SCAN .INDEX FULL SCAN 和 INDEX FAST FULL SCAN 不同。它是一種索引掃描,從根節點開始掃描,遍歷整棵索引樹,並且一次讀取一個索引塊,IFS 會引起 db file sequential read 事件。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-673842/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【SQL】查詢資料的方式 (二)SQL
- sql查詢一張表的重複資料SQL
- 查詢所有資料字典的SQLSQL
- SQL Server 跨資料庫查詢SQLServer資料庫
- 【資料庫】sql連表查詢資料庫SQL
- SQL Server實戰四:查詢資料庫的資料SQLServer資料庫
- sql查詢案例:刪除2條完全一樣的資料SQL
- 資料庫資料的查詢----連線查詢資料庫
- 查詢 JSON 資料結構的 8 種方式JSON資料結構
- 查詢json資料結構的8種方式JSON資料結構
- SQL Server:基於WEB的資料庫查詢SQLServerWeb資料庫
- 一條SQL完成跨資料庫例項Join查詢SQL資料庫
- SQL Server 多表聯合查詢取最新一條資料SQLServer
- SQL查詢的:子查詢和多表查詢SQL
- Android SQL資料庫查詢方法 query( )AndroidSQL資料庫
- 優化SQL Server資料庫查詢方法優化SQLServer資料庫
- SQL的資料庫操作:新增、更新、刪除、查詢SQL資料庫
- SQL Server中基於WEB的資料庫查詢SQLServerWeb資料庫
- SQL 唯一查詢SQL
- SQL查詢的轉義方法(一)SQL
- SQL Server中Table字典資料的查詢SQL示例程式碼SQLServer
- SQL Server 查詢資料庫中所有表資料條數SQLServer資料庫
- oracle資料庫sql查詢檢視第二次查詢很慢Oracle資料庫SQL
- MySQL資料庫詳解(一)SQL查詢語句是如何執行的?MySql資料庫
- 教你用一條SQL搞定跨資料庫查詢難題SQL資料庫
- 樹狀資料結構儲存方式——查詢篇資料結構
- SSH_hibernate---六種查詢資料庫方式資料庫
- 透過shell指令碼生成查詢表資料的sql指令碼SQL
- 通過shell指令碼生成查詢表資料的sql指令碼SQL
- SQL Server資料庫查詢優化的方法總結SQLServer資料庫優化
- 查詢資料庫隱含引數的sql語句資料庫SQL
- 關於SQL Server資料查詢基本方法的總結SQLServer
- MySQL資料庫:7、SQL常用查詢語句MySql資料庫
- 改進資料庫效能-SQL查詢優化資料庫SQL優化
- 回閃查詢查詢刪除的資料
- Flask——資料的查詢Flask
- 一條查詢sql的執行之路SQL
- Logtail:像查詢資料庫一樣查詢日誌AI資料庫