SQL性能优化策略之联合索引优化方法
复制SQL> set autot trace
SQL> SELECT REQUISITION_ID PARAM1, 1 PARAM2, /*电子标签*/ 1 PARAM3
2 FROM dbo.LIS_REQUISITION_INFO
3 WHERE PRINT_TIME >=
4 TO_DATE(2019-01-01 00:00:00, YYYY-MM-DD HH24:MI:SS)
5 AND PRINT_TIME < SYSDATE
6 and length(requisition_id) = 12
7 AND (TAT1_STATE = OR TAT1_STATE ISNULL)
8 AND ROWNUM < 800;
Execution Plan
----------------------------------------------------------
Plan hash value: 1151136383
------------------------------------------------------------------------------------------
| Id | Operation |Name |Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 799 | 18377 | 160K (1)| 00:32:03 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY
INDEX ROWID |LIS_REQUISITION_INFO| 800 | 18400 | 160K (1)| 00:32:03 |
|* 4 | INDEX RANGE SCAN |I_PRINT_TIME | | | 3799 (1)| 00:00:46 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<800)
2 - filter(SYSDATE@!>TO_DATE( 2019-01-01 00:00:00, syyyy-mm-dd hh24:mi:ss))
3 - filter("TAT1_STATE"ISNULLAND LENGTH("REQUISITION_ID")=12)
4 - access("PRINT_TIME">=TO_DATE( 2019-01-01 00:00:00, syyyy-mm-dd hh24:mi:ss) AND
"PRINT_TIME"<SYSDATE@!)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1204017 consistent gets
161836 physical reads
19984 redo size
761 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.
THE END
