下面是表结构的部分字段:
Name Type Nullable Default Comments
---------------- -------------- -------- ------- --------
EVTID VARCHAR2(36)
EVTTIME DATE
DEVIP VARCHAR2(32)
PN VARCHAR2(255)
MSG VARCHAR2(4000) Y 相关索引如下:
create index IDX_SIM_ET_EVENT_DAY_1 on T_SIM_ET_EVENT_DAY
(EVTTIME, DEVIP, PN, EVTID)
tablespace TBS_USER_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 64K
minextents 1
maxextents unlimited
pctincrease 0
);
表和索引位于不同表空间。执行如下查询,对3月15日当天指定Devip、PN的记录数进行统计:
SELECT COUNT(*) AS COUNT FROM T_SIM_ET_EVENT_DAY
WHERE (EVTTIME >= TO_DATE('2007-03-15','YYYY-MM-DD')
AND EVTTIME < TO_DATE('2007-03-16','YYYY-MM-DD'))
AND DEVIP = '202.99.155.218' AND PN = 'AAA' ;用时184秒,符合条件的记录计数共206万,该表共有750万条记录,执行计划如下:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34172 Card=1 Bytes
=36) 1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_SIM_ET_EVENT_DAY_1' (INDE
X) (Cost=34172 Card=1442676 Bytes=51936336)Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
134478 consistent gets
124267 physical reads
0 redo size
303 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed请教各位高手,这个计数查询还可以做什么优化吗?
Name Type Nullable Default Comments
---------------- -------------- -------- ------- --------
EVTID VARCHAR2(36)
EVTTIME DATE
DEVIP VARCHAR2(32)
PN VARCHAR2(255)
MSG VARCHAR2(4000) Y 相关索引如下:
create index IDX_SIM_ET_EVENT_DAY_1 on T_SIM_ET_EVENT_DAY
(EVTTIME, DEVIP, PN, EVTID)
tablespace TBS_USER_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 64K
minextents 1
maxextents unlimited
pctincrease 0
);
表和索引位于不同表空间。执行如下查询,对3月15日当天指定Devip、PN的记录数进行统计:
SELECT COUNT(*) AS COUNT FROM T_SIM_ET_EVENT_DAY
WHERE (EVTTIME >= TO_DATE('2007-03-15','YYYY-MM-DD')
AND EVTTIME < TO_DATE('2007-03-16','YYYY-MM-DD'))
AND DEVIP = '202.99.155.218' AND PN = 'AAA' ;用时184秒,符合条件的记录计数共206万,该表共有750万条记录,执行计划如下:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34172 Card=1 Bytes
=36) 1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_SIM_ET_EVENT_DAY_1' (INDE
X) (Cost=34172 Card=1442676 Bytes=51936336)Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
134478 consistent gets
124267 physical reads
0 redo size
303 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed请教各位高手,这个计数查询还可以做什么优化吗?
或者拆分EVTTime,把日期和时间分成两个字段
WHERE (EVTTIME >= TO_DATE('2007-03-15','YYYY-MM-DD')
AND EVTTIME < TO_DATE('2007-03-16','YYYY-MM-DD'))
AND DEVIP = '202.99.155.218' AND PN = 'AAA' ;
看一下
确实这个复合索引太大了,有四个字段:EVTTIME, DEVIP, PN, EVTID
但查询条件比较复杂,牵涉了3个字段,
不设计这个复合索引在查询时会引起全表扫描啊,
最多在索引中去掉EVTID这个字段,但效果不是太好。希望有高手来指点指点:)
先把日期型 EVTTIME 转化成"YYYY-MM-DD"格式的字符串直接与串"2007-03-15"比较可能会不同