17,377
社区成员
发帖
与我相关
我的任务
分享
-- 给你看 1700多万数据的大表上的两个SQL语句:
hll@SZTYORA> select count(*) from mobilefrends;
COUNT(*)
----------
17200235
已用时间: 00: 00: 01.40
执行计划
----------------------------------------------------------
Plan hash value: 3731074549
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12411 (3)| 00:02:29 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX | 16M| 12411 (3)| 00:02:29 |
----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hll@SZTYORA> select count(*) from mobilefrends
2 where cdate>=trunc(sysdate-1) and cdate<trunc(sysdate);
COUNT(*)
----------
7481
已用时间: 00: 00: 00.34
执行计划
----------------------------------------------------------
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 | 51 | 408 | 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
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hll@SZTYORA> select count(*) from mobilefrends
2 where to_char(cdate,'yyyy-mm-dd') = to_char(sysdate-1,'yyyy-mm-dd');
COUNT(*)
----------
7481
已用时间: 00: 01: 25.87
执行计划
----------------------------------------------------------
Plan hash value: 3731074549
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 14371 (16)| 00:02:53 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX | 169K| 1324K| 14371 (16)| 00:02:53 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy
-mm-dd'))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
不要建 函数索引 就一般 索引 试试
SELECT COUNT(*)
FROM POUND_BILL
WHERE INPORT_TIM BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 0.99999