oracle 查詢當天記錄 三種方法效率比較

標籤：os   資料   io   for   cti   re   

-- 查詢一表中當天產生的資料-- 原表mobilefrends中的cdate欄位上有索引，建立索引語句是：create index mobilefrends_cdate_idx on mobilefrends(cdate); ----------------------------------------------------------------------------------------------------------------------- 方法一：用to_char()函數 [email protected]> 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([email protected]!-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()函數 [email protected]> 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([email protected]!-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 ----------------------------------------------------------------------------------------------------------------------- 方法三：不用函數[email protected]> 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([email protected]!-1)<TRUNC([email protected]!))   3 - access("CDATE">=TRUNC([email protected]!-1) AND "CDATE"<TRUNC([email protected]!))  統計資訊----------------------------------------------------------          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次，執行的是索引範圍掃描；