函数索引与统计更新

来源：互联网发布：2017微信加粉丝软件编辑：程序博客网时间：2024/05/24 03:22

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL> set timing on;
SQL> create table test as select * from dba_objects ;

表已创建。

已用时间: 00: 00: 01.77
SQL> set autotrace on;

-- 查询中用到了substr函数，由于没有索引。理所当然走全表扫描TABLE ACCESS FULL
SQL> select count(1) from test
2 where substr(object_name, 1, 3)='abc';

COUNT(1)
----------
         0

已用时间: 00: 00: 00.10

执行计划
----------------------------------------------------------

----------------------------------------------------------------
| Id | Operation                  | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    66 |   236   (2)|
|   1 | SORT AGGREGATE    |           |     1 |    66 |            |
|   2 |   TABLE ACCESS FULL| TEST |    12 |   792 |   236   (2)|
----------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
         48 recursive calls
          0 db block gets
       1114 consistent gets
       1030 physical reads
          0 redo size
        223 bytes sent via SQL*Net to client
        243 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--创建函数索引test_idx_obj_name

SQL> create index test_idx_obj_name on TEST (substr(object_name,1,3));

索引已创建。

已用时间: 00: 00: 00.70

--创建函数索引后，执行计划走了索引，执行时间由 00: 00: 00.10 缩短到 00: 00: 00.03
SQL> select count(1) from test
2 where substr(object_name, 1, 3)='abc';

COUNT(1)
----------
         0

已用时间: 00: 00: 00.03

执行计划
----------------------------------------------------------

----------------------------------------------------------------------------
| Id | Operation         | Name              | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |    66 |     1   (0)|
|   1 | SORT AGGREGATE   |                   |     1 |    66 |            |
|   2 |   INDEX RANGE SCAN| TEST_IDX_OBJ_NAME |    12 |   792 |     1   (0)|
----------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
         28 recursive calls
          0 db block gets
         79 consistent gets
          1 physical reads
          0 redo size
        223 bytes sent via SQL*Net to client
        243 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

-- 能不能在快点呢？我们试着对该表做个统计分析，再来看结果

SQL> analyze table test compute statistics;

表已分析。

已用时间: 00: 00: 03.23
SQL> select count(1) from test
2 where substr(object_name, 1, 3)='abc';

COUNT(1)
----------
         0

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------

----------------------------------------------------------------------------
| Id | Operation         | Name              | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |    21 |     1   (0)|
|   1 | SORT AGGREGATE   |                   |     1 |    21 |            |
|   2 |   INDEX RANGE SCAN| TEST_IDX_OBJ_NAME |    62 | 1302 |     1   (0)|
----------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          2 consistent gets
          0 physical reads
          0 redo size
        223 bytes sent via SQL*Net to client
        243 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--统计更新后执行时间缩短到 00: 00: 00.01 ，而且consistent gets 和recursive calls 也有所下降。

说明统计更新对提高函数索引的性能是有一定的帮助的。