/* 2008/07/01 星期一
*蒙昭良
*環境：windowsXP + Oracle10gR2
*在select 中的where 中使用index
*/
1 在where中使用索引
SQL> set timing on
SQL> set autotrace on
SQL> drop index test_owner_index;

索引已刪除。

已用時間: 00: 00: 02.01

沒有使用索引之前：全表掃描花4.46秒
SQL> select count(*) from test where wner='RISENET';

COUNT(*)
----------
      1350

已用時間: 00: 00: 04.46

執行計劃
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 | 4110   (1)| 00:00:50 |
|   1 | SORT AGGREGATE    |      |     1 |     6 |            |          |
|* 2 |   TABLE ACCESS FULL| TEST | 1052 | 6312 | 4110   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='RISENET')

統計資訊
----------------------------------------------------------
        124 recursive calls
          0 db block gets
      15108 consistent gets
      15087 physical reads
          0 redo size
        409 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> create index test_owner_index
2 on test(owner);

索引已建立。

已用時間: 00: 00: 04.57

使用索引之後：0.01秒

SQL> select count(*) from test where wner='RISENET';

COUNT(*)
----------
      1350

已用時間: 00: 00: 00.01

執行計劃
----------------------------------------------------------
Plan hash value: 1335201336

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

| Id | Operation         | Name             | Rows | Bytes | Cost (%CPU)| Time
     |

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

|   0 | SELECT STATEMENT |                  |     1 |     6 |     3   (0)| 00:0
0:01 |

|   1 | SORT AGGREGATE   |                  |     1 |     6 |            |
     |

|* 2 |   INDEX RANGE SCAN| TEST_OWNER_INDEX | 1052 | 6312 |     3   (0)| 00:0
0:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='RISENET')

統計資訊
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          6 consistent gets
          5 physical reads
          0 redo size
        409 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> spool off

在select 中的where 中使用index

相關文章