【SQL】查詢資料的方式 (一)

楊奇龍發表於2010-09-14

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章