在select 中的where 中使用index-优快云博客

本文通过对比测试，在Oracle数据库中展示了创建索引前后的查询性能差异。实验表明，合理使用索引能够显著提高特定条件下的查询速度，从原来的4.46秒减少到0.01秒。

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-368744/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-368744/