[sql语句优化.docxVIP

编号: 1SQLID: 29zcgnwdhvqam存储过程及模块：无问题描述：没有创建合理的索引，导致H_CSIP_INTF_PROCESSOR全表扫描原SQL语句： select rowid, WRITE_TIME from USBSZ.H_CSIP_INTF_PROCESSOR where WRITE_TIME trunc(sysdate - 30) and rownum 5001执行计划：PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2171870722--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5000 | 97K| 18 (0)| 00:00:01 ||* 1 | COUNT STOPKEY | | | | | ||* 2 | TABLE ACCESS FULL| H_CSIP_INTF_PROCESSOR | 5001 | 97K| 18 (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM5001) 2 - filter(WRITE_TIMETRUNC(SYSDATE@!-30))15 rows selected.建议方案：建议在表H_CSIP_INTF_PROCESSOR上创建单列索引，索引字段为WRITE_TIMESQL调整：无优化后测试效果： 优化前每次526719逻辑读，每小时执行52次。 编号: 2SQLID: 2rhbw4gy7vrhv存储过程及模块：CRM_CSIP_MANAGER.TIMING_VERIFY_RULE_FOR_OTHER问题描述：语句中带有rule HINT,导致执行计划无法选择合理的索引原SQL语句： sELECT /*+rule*/ B.CO_ID FROM CSIP_INTF_BUFFER A, CSIP_INST_CONTROL B WHERE A.INTF_CD = :B4 AND A.BUS_ID = B.ID AND B.AREA_ID = :B3 AND (B.PROD_ID = :B2 OR B.ACC_NBR = :B1) AND ROWNUM 2 执行计划：Execution Plan----------------------------------------------------------Plan hash value: 3738408526-----------------------------------------------------------| Id | Operation | Name |-----------------------------------------------------------| 0 | SELECT STATEMENT | ||* 1 | COUNT STOPKEY