一条简单的SQL语句优化-新年新气象(国外英文资料).docVIP

一条简单的SQL语句优化-新年新气象(国外英文资料) A simple SQL sentence optimization - New Years new weather Personal classification: Oracle Database CPU has been consumed, the front desk operation is a bit slow, check the conversation situation, the database pressure is basically on a SQL. SQL select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i, Enterprise, Edition, Release, 9.2.0.8.0 - 64bit, Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS, for, IBM/AIX, RISC, System/6000:, Version, 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production SQL EXPLAIN PLAN FOR 2, SELECT, count (*) FROM, T_SR_SERVICEREQUEST, T 3 WHERE: SYS_B_0 =: SYS_B_1 4 AND T.SUBMITTIME: 1/: SYSDATE - = SYS_B_2 5, AND, T.SRTYPEID =: 2 6, AND, T.SUBSNUMBER =: 3 7 and (t.SERVICEID like to_char (sysdate: SYS_B_3) ||: SYS_B_4 8 or t.SERVICEID like to_char (sysdate-: SYS_B_5, SYS_B_6) ||: SYS_B_7); Explained. SQL select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- Id Operation Name | | | | Rows Bytes Cost Pstart| Pstop | | | | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 100 | 13 | | | | 1 | SORT AGGREGATE | | 1 | 100 | | | | | 2 | CONCATENATION | | | | | | | |* 3 | FILTER | | | | | | | 4 PARTITION RANGE ITERATOR | | | | | | | KEY | KEY | 5 TABLE ACCESS BY |* | LOCAL INDEX ROWID| T_SR_SERVICEREQUEST | 1 | 100 | 3 | KEY | KEY | 6 INDEX RANGE SCAN |* | | PK_SR_SERVICEREQUEST | 3214 | | 2 | KEY | KEY | |* 7 | FILTER | | | | | | | | 8 |分区范围的迭代器| | | | |关键|关键| |×9 |表访问的本地索引rowid | t_sr_servicerequest | 1 | 100 | 3 |关键|关键| |×10 |索