下列的SQL语句是一个报表统计的SQL，听开发说with里面返回的记录其实很少，只有十几条而已。
SQL> WITH STAT AS (
2 SELECT PTI.FROM_BUREAU_NO,PTI.TASK_ID, PPE.PATROL_EQUIPMENT_ID,PTI.IS_COMPLETE,PPE.DEVICE_ID3 FROM GG_pTASK_PATROL_DETAIL PTPD, GG_PTASK_ITEM PTI,GG_PATROL_EQUIPMENT PPE
4 WHERE PTPD.PATROL_LINE_ID=PPE.PATROL_LINE_ID AND PTPD.PATROL_LINE_ID IS NOT NULL
5 AND PTPD.PATROL_SUB_TYPE=1
6 AND PTPD.PATROL_TYPE=1
7 AND PTI.TASK_ID=PTPD.TASK_ID
8 AND PTI.TASK_KIND=1
9 )
10 SELECT FROM_BUREAU_NO,VOLTAGE_LEVEL,COUNT(*) allrows, SUM(REC) FROM(
11 SELECT T.FROM_BUREAU_NO,PD.DEVICE_ID,PD.VOLTAGE_LEVEL,DECODE(T.IS_COMPLETE,3,1,0) REC FROM GG_DEVICE PD,STAT T WHERE PD.DEVICE_ID=T.DEVICE_ID)
12 GROUP BY FROM_BUREAU_NO,VOLTAGE_LEVEL;
已用时间: 00: 01: 13.24 --非常慢
执行计划
----------------------------------------------------------
Plan hash value: 1646606100
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 253 | 15939 | | 73290 (1)| 00:14:40 |
| 1 | HASH GROUP BY | | 253 | 15939 | | 73290 (1)| 00:14:40 |
|* 2 | HASH JOIN | | 107K| 6614K| 6592K| 73283 (1)| 00:14:40 |
|* 3 | HASH JOIN | | 105K| 5354K| | 1625 (1)| 00:00:20 |
| 4 | NESTED LOOPS | | 375 | 13125 | | 806 (1)| 00:00:10 |
|* 5 | TABLE ACCESS FULL | GG_PTASK_PATROL_DETAIL | 375 | 7500 | | 55 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| GG_PTASK_ITEM | 1 | 15 | | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_GG_PTASK_ITEM | 1 | | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | GG_PATROL_EQUIPMENT | 192K| 3191K| | 818 (1)| 00:00:10 |
| 9 | TABLE ACCESS FULL | GG_DEVICE | 5226K| 54M| | 65586 (1)| 00:13:08 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PD"."DEVICE_ID"=TO_NUMBER("PPE"."DEVICE_ID"))
3 - access("PTPD"."PATROL_LINE_ID"="PPE"."PATROL_LINE_ID")
6 - filter("PTI"."TASK_KIND"=1)
7 - access("PTI"."TASK_ID"="PTPD"."TASK_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
302451 consistent gets
176347 physical reads
0 redo size
543 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--开始使用利器
SQL> alter session set statistics_level=all;
SQL> set pagesize 100
SQL> SELECT FROM_BUREAU_NO,VOLTAGE_LEVEL,COUNT(*) allrows, SUM(REC) FROM(
2 SELECT PTI.FROM_BUREAU_NO,PTI.TASK_ID, PPE.PATROL_EQUIPMENT_ID,
3 DECODE(PTI.IS_COMPLETE,3,1,0) REC,PPE.DEVICE_ID,PD.VOLTAGE_LEVEL
4 FROM GG_pTASK_PATROL_DETAIL PTPD, GG_PTASK_ITEM PTI,GG_PATROL_EQUIPMENT PPE,GG_DEVICE PD
5 WHERE PTPD.PATROL_LINE_ID=PPE.PATROL_LINE_ID AND PTPD.PATROL_LINE_ID IS NOT NULL --关联路线
6 AND PTPD.PATROL_SUB_TYPE=1
7 AND PTPD.PATROL_TYPE=1
8 AND PTI.TASK_ID=PTPD.TASK_ID
9 AND PTI.TASK_KIND=1
10 AND PD.DEVICE_ID= PPE.DEVICE_ID)
11 GROUP BY FROM_BUREAU_NO,VOLTAGE_LEVEL;
FROM_BURE