6. SQL 调整实例
SQL 调整目标:执行时间尽量短、Consistent gets(一致性读取) 尽量小,
在这两个小的情况下,Physical reads(物理读取)也尽量小。
调整工具:Oracle SQL*PLUS
命令:SET autotrace traceonly;
执行计划的查看:按第二列数字,数字大的先执行,数字相等的上面的先
执行。
分析表及索引(让 Oracle 收集各列上值的统计信息及分布等):
analyze table table_name compute statistics
for table
for all indexes
for all indexed columns;
原语句:
SELECT * FROM LZCardTemp
WHERE QueryOper = '320041' AND OperateFlag = '0'
AND CertifyCode = '1101' AND SendOutCom = 'A863209'
AND ReceiveCom = 'A86320900'
AND StartNo <= '86110100121744' AND EndNo >= '86110100121744'
AND ( ModifyDate < '2004-08-25' OR ( ModifyDate = '2004-08-25' AND
ModifyTime <= '19:28:06'))
ORDER BY ModifyDate DESC, ModifyTime DESC
已用时间: 00: 00: 05.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=1 Bytes=208)
1 0 SORT (ORDER BY) (Cost=79 Card=1 Bytes=208)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'LZCARDTEMP' (Cost=77 C
ard=1 Bytes=208)
3 2 INDEX (RANGE SCAN) OF 'PK_LZCARDTEMP' (UNIQUE) (Cost=7
6 Card=1)
Statistics
----------------------------------------------------------
1004 recursive calls
0 db block gets
1466 consistent gets
1229 physical reads