Welcome to oracle_awen's archive blog
根据应用程序中的需求往往需要创建函数索引,如 to_char(salesdatetime,'yyyy
mmdd')


SQL> create index map_red on fwd.salestrack(salestel,to_char(salesdatetime,'yyyy
mmdd'));

索引已创建。

SQL> select * from salestrack where salestel='13768875509' and to_char(salesdate
time,'yyyymmdd')>'20120701';

已选择1263行。


执行计划
----------------------------------------------------------
Plan hash value: 1625202595

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

----------

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

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

----------

|   0 | SELECT STATEMENT            |            |    67 |  5226 |     8   (0)|
00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SALESTRACK |    67 |  5226 |     8   (0)|
00:00:01 |

|*  2 |   INDEX RANGE SCAN          | MAP_RED    |    12 |       |     3   (0)|
00:00:01 |

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

----------


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

   2 - access("SALESTEL"='13768875509' AND
              TO_CHAR(INTERNAL_FUNCTION("SALESDATETIME"wink.gif,'yyyymmdd')>'20120701'
AND

              TO_CHAR(INTERNAL_FUNCTION("SALESDATETIME"wink.gif,'yyyymmdd') IS NOT NULL

)



统计信息
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        679  consistent gets
          6  physical reads
          0  redo size
      68599  bytes sent via SQL*Net to client
       1308  bytes received via SQL*Net from client
         86  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1263  rows processed

SQL> select * from salestrack where salestel='13768875509' and to_char(salesdate
time,'yyyymmdd')='20120719';

已选择80行。


执行计划
----------------------------------------------------------
Plan hash value: 1625202595

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

----------

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

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

----------

|   0 | SELECT STATEMENT            |            |    13 |  1014 |     5   (0)|
00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SALESTRACK |    13 |  1014 |     5   (0)|
00:00:01 |

|*  2 |   INDEX RANGE SCAN          | MAP_RED    |     5 |       |     3   (0)|
00:00:01 |

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

----------


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

   2 - access("SALESTEL"='13768875509' AND
              TO_CHAR(INTERNAL_FUNCTION("SALESDATETIME"wink.gif,'yyyymmdd')='20120719')



统计信息
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         52  consistent gets
          0  physical reads
          0  redo size
       5058  bytes sent via SQL*Net to client
        439  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         80  rows processed
添加评论

昵称 *

E-mail (防止垃圾信息,此栏留空)