Oracle_查询当天记录 三种方法效率比较

oracle 查询当天记录 三种方法效率比较

 

via: http://my.oschina.net/baishi/blog/293742

 

-- 查询一表中当天生成的数据

-- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mobilefrends_cdate_idx on mobilefrends(cdate);

  

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

-- 方法一：用to_char()函数

  

hll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');

  

  COUNT(*)

----------

     82119

  

已用时间:  00: 00: 17.18

  

执行计划

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

Plan hash value: 3731074549

  

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

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

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

|   0 | SELECT STATEMENT      |                        |     1 |     8 | 11773  (16)| 00:02:22 |

|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |

|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 11773  (16)| 00:02:22 |

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

  

Predicate Information (identified by operation id):

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

  

   2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy

              -mm-dd'))

  

  

统计信息

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

          1  recursive calls

          0  db block gets

      36896  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

  

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

-- 方法二：用trunc()函数

  

hll@SZTYORA> select count(*) from mobilefrends where trunc(cdate)=trunc(sysdate);

  

  COUNT(*)

----------

     82119

  

已用时间:  00: 00: 16.32

  

执行计划

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

Plan hash value: 3731074549

  

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

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

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

|   0 | SELECT STATEMENT      |                        |     1 |     8 | 12580  (22)| 00:02:31 |

|   1 |  SORT AGGREGATE       |                        |     1 |     8 |            |          |

|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 12580  (22)| 00:02:31 |

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

  

Predicate Information (identified by operation id):

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

  

   2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))

  

  

统计信息

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

          0  recursive calls

          0  db block gets

      36896  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

  

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

-- 方法三：不用函数

hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate) and cdate<trunc(sysdate)+1;

  

  COUNT(*)

----------

     82119

  

已用时间:  00: 00: 00.43

  

执行计划

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

Plan hash value: 2668176725

  

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

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

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

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

|   1 |  SORT AGGREGATE    |                        |     1 |     8 |            |          |

|*  2 |   FILTER           |                        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX |    48 |   384 |     3   (0)| 00:00:01 |

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

  

Predicate Information (identified by operation id):

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

  

   2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))

   3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))

  

  

统计信息

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

          0  recursive calls

          0  db block gets

        221  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

  

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

  

  

-- 由上三种方法可见：

-- 方法一：用to_char()函数，耗时17.18秒，逻辑读为36896次，执行的是全索引扫描；

-- 方法二：用trunc()函数，耗时16.32秒，逻辑读也是36896次，执行的也是全索引扫描；

-- 方法三：不用函数，耗时0.43秒，逻辑读为221次，执行的是索引范围扫描；