【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

  3  where rownum =1;

Elapsed: 00:00:00.00

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)

          4  consistent gets

-------------------------

SQL> select rowid ,object_id from test where rowid = 'AAARVKAAEAAAAD8AAA';

-----------------------------------------------------------------------------------

| 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 |

----------------------------------------------------------------------------------

        147  recursive calls

         14  consistent gets

          0  redo size

        。。。

          3  sorts (memory)

-----------------------------------------------

----建立索引

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

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 |

--------------------------------------------------

   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;

         6

         7

         8

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 |

   1 - access("OBJECT_ID">5 AND "OBJECT_ID"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

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 |

   1 - filter("OBJECT_ID">3)

       3670  consistent gets

           。。。。。。。

      53362  rows processed

SQL> select object_id from un_test where object_id>500;

52887 rows selected.

|   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 |

   1 - filter("OBJECT_ID">500

       3634  consistent gets

          。。。。。。。。。

      52887  rows processed

有時如果 的值過小，強制使用索引掃描時，會發生INDEX FULL SCAN .INDEX FULL SCAN 和 INDEX FAST FULL SCAN 不同。它是一種索引掃描，從根節點開始掃描，周遊整棵索引樹，并且一次讀取一個索引塊，IFS 會引起 db file sequential  read  事件。