如何优化以下查询语句:
select to_char(record_time,'yyyy-mm-dd hh24:mi:ss'),SUM(TCP4_BYTE_IN),SUM(TCP4_BYTE_OUT) ,SUM(TCP4_PKTNUM_IN),SUM(TCP4_PKTNUM_OUT)
from applicationdata A join protocol B on A.APPLICATION_PROTOCOL = B.PROTOCOL_ID
where link_id = 342 and group_id = 1 group by record_time order by record_time
其中applicationdata记录个数为500万,protocol的记录很少,列link_id只具有6种取值,列record_time具有74229种取值。调整驱动表的次序,发现对查询性能影响不大,我对列link_id加bitmap索引,发现对性能几乎没有影响,为了提高该查询语句的性能,如果对link_id和record_time加索引,谢谢!
select to_char(record_time,'yyyy-mm-dd hh24:mi:ss'),SUM(TCP4_BYTE_IN),SUM(TCP4_BYTE_OUT) ,SUM(TCP4_PKTNUM_IN),SUM(TCP4_PKTNUM_OUT)
from applicationdata A join protocol B on A.APPLICATION_PROTOCOL = B.PROTOCOL_ID
where link_id = 342 and group_id = 1 group by record_time order by record_time
其中applicationdata记录个数为500万,protocol的记录很少,列link_id只具有6种取值,列record_time具有74229种取值。调整驱动表的次序,发现对查询性能影响不大,我对列link_id加bitmap索引,发现对性能几乎没有影响,为了提高该查询语句的性能,如果对link_id和record_time加索引,谢谢!
关键还是要看哪个条件能最大限度的排除不需要的数据
然后收集统计信息,
再执行下下面这个sql,然后贴出执行计划:select /*+ ordered use_nl(B,A) */
to_char(A.record_time,'yyyy-mm-dd hh24:mi:ss'),
SUM(A.TCP4_BYTE_IN),
SUM(A.TCP4_BYTE_OUT) ,
SUM(A.TCP4_PKTNUM_IN),
SUM(A.TCP4_PKTNUM_OUT)
from protocol B,applicationdata A
where A.APPLICATION_PROTOCOL = B.PROTOCOL_ID
and A.link_id = 342
and A.group_id = 1
group by A.record_time
order by A.record_time
表APPLICATION_DATA_BAKk的字段APPLICATION_PROTOCOL有75种取值,表APPLICATION_DATA_BAKk的字段link_id的有6种取值,字段record_time有2880中取值,针对每一种取值,数据分布相对均匀。
现有查询语句:
select to_char(record_time,'yyyy-mm-dd hh24:mi:ss'),SUM(TCP4_BYTE_IN),SUM(TCP4_BYTE_OUT) ,SUM(TCP4_PKTNUM_IN),SUM(TCP4_PKTNUM_OUT)
from APPLICATION_DATA_BAK A join PROTOCOL_GROUP_INFO B on A.APPLICATION_PROTOCOL = B.PROTOCOL_ID
where LINK_ID= 342 and GROUP_ID = 1 group by RECORD_TIME order by RECORD_TIME不加任何索引的执行计划为:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13985 Card=34247 B
ytes=1267139) 1 0 SORT (GROUP BY) (Cost=13985 Card=34247 Bytes=1267139)
2 1 HASH JOIN (Cost=13649 Card=34247 Bytes=1267139)
3 2 TABLE ACCESS (FULL) OF 'PROTOCOL_GROUP_INFO' (TABLE) (
Cost=3 Card=11 Bytes=88) 4 2 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE)
(Cost=13643 Card=239728 Bytes=6952112)
Statistics
----------------------------------------------------------
237 recursive calls
0 db block gets
61426 consistent gets
61353 physical reads
0 redo size
116706 bytes sent via SQL*Net to client
2536 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
2773 rows processed
对字段APPLICATION_PROTOCOL加bitmap索引后的执行计划为:
2773 rows selected.
Elapsed: 00:00:04.84
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13985 Card=34247 B
ytes=1267139) 1 0 SORT (GROUP BY) (Cost=13985 Card=34247 Bytes=1267139)
2 1 HASH JOIN (Cost=13649 Card=34247 Bytes=1267139)
3 2 TABLE ACCESS (FULL) OF 'PROTOCOL_GROUP_INFO' (TABLE) (
Cost=3 Card=11 Bytes=88) 4 2 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE)
(Cost=13643 Card=239728 Bytes=6952112)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
61372 consistent gets
61317 physical reads
0 redo size
116706 bytes sent via SQL*Net to client
2536 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2773 rows processed继续在LINK_ID上加bitmap索引后的执行计划为:
Elapsed: 00:01:08.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8098 Card=34247 By
tes=1267139) 1 0 SORT (GROUP BY) (Cost=8098 Card=34247 Bytes=1267139)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_DATA_BAK'
(TABLE) (Cost=7762 Card=3196 Bytes=92684) 3 2 NESTED LOOPS (Cost=7762 Card=34247 Bytes=1267139)
4 3 TABLE ACCESS (FULL) OF 'PROTOCOL_GROUP_INFO' (TABLE)
(Cost=3 Card=11 Bytes=88) 5 3 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP INDEX (SINGLE VALUE) OF 'LINKID_INDEX' (I
NDEX (BITMAP)) 8 6 BITMAP INDEX (SINGLE VALUE) OF 'APPLICATION_PROT
OCOL_INDEX' (INDEX (BITMAP))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
115182 consistent gets
115050 physical reads
0 redo size
116706 bytes sent via SQL*Net to client
2536 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2773 rows processed
从上可知,对字段APPLICATION_PROTOCOL加索引后,性能改善不明显,但对LINK_ID字段加bitmap索引后,性能严重恶化,这是为什么呢?为了提高该查询语句的性能,应该如何优化,谢谢!