2014-3-18 对filter下有两个节点的优化

来源：互联网发布：淘宝宝贝上架流程编辑：程序博客网时间：2024/06/01 09:06

非常简单的一条SQL，数据量也不大，但逻辑读非常高。

select count(1) from PMS_PROJECT;--18964
select count(1) from PUB_OBJECT_AUTHORITY;--260871
select count(1) from PMS_PROJECT_AUTHORITY;--19288

SELECT *

FROM (SELECT *
FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (SELECT MAIN_TABLE.*
FROM (SELECT *
FROM PMS_PROJECT
WHERE 1 = 1
AND PROJECT_TYPE_CODE = 4
AND PROJECT_TYPE_CODE IS NOT null) MAIN_TABLE
WHERE EXISTS (SELECT ''
FROM PUB_OBJECT_AUTHORITY
WHERE PUB_OBJECT_AUTHORITY.CLASS_NAME =
'PS_EXECUTE'
AND PUB_OBJECT_AUTHORITY.USER_ID =
'00001311.fs'
AND main_table.EXE_DEPT_CODE LIKE
PUB_OBJECT_AUTHORITY.DEPARTMENT_CODE || '%'
UNION ALL
SELECT ''
FROM PMS_PROJECT_AUTHORITY
WHERE PMS_PROJECT_AUTHORITY.OBJECT_TYPE =
'OBJECT_PROJECT'
AND PMS_PROJECT_AUTHORITY.USER_ID =
'00001311.fs'
AND PMS_PROJECT_AUTHORITY.OBJECT_ID =
MAIN_TABLE.PROJECT_ID)) INNER_TABLE)
WHERE OUTER_TABLE_ROWNUM <= 10) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM > 0;
已用时间: 00: 00: 00.34
执行计划
----------------------------------------------------------
Plan hash value: 515022443
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2474 | 44123 (1)| 00:08:50 |
|* 1 | VIEW | | 1 | 2474 | 44123 (1)| 00:08:50 |
| 2 | COUNT | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | PMS_PROJECT | 11485 | 5798K| 321 (1)| 00:00:04 |
| 5 | UNION-ALL | | | | | |
|* 6 | INDEX RANGE SCAN | IDX_AUTHORITY | 1 | 34 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PMS_PROJECT_AUTHORITY | 1 | 41 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_ROLE | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10)
3 - filter( EXISTS ( (SELECT '' FROM "PUB_OBJECT_AUTHORITY" "PUB_OBJECT_AUTHORITY" WHERE
"PUB_OBJECT_AUTHORITY"."USER_ID"='00001311.fs' AND
"PUB_OBJECT_AUTHORITY"."CLASS_NAME"='PS_EXECUTE' AND :B1 LIKE
"PUB_OBJECT_AUTHORITY"."DEPARTMENT_CODE"||'%') UNION ALL (SELECT '' FROM
"PMS_PROJECT_AUTHORITY" "PMS_PROJECT_AUTHORITY" WHERE "PMS_PROJECT_AUTHORITY"."OBJECT_ID"=:B
AND "PMS_PROJECT_AUTHORITY"."USER_ID"='00001311.fs' AND
"PMS_PROJECT_AUTHORITY"."OBJECT_TYPE"='OBJECT_PROJECT')))
4 - filter(TO_NUMBER("PROJECT_TYPE_CODE")=4 AND "PROJECT_TYPE_CODE" IS NOT NULL)
6 - access("PUB_OBJECT_AUTHORITY"."CLASS_NAME"='PS_EXECUTE' AND
"PUB_OBJECT_AUTHORITY"."USER_ID"='00001311.fs')
filter(:B1 LIKE "PUB_OBJECT_AUTHORITY"."DEPARTMENT_CODE"||'%')
7 - filter("PMS_PROJECT_AUTHORITY"."USER_ID"='00001311.fs' AND
"PMS_PROJECT_AUTHORITY"."OBJECT_TYPE"='OBJECT_PROJECT')
8 - access("PMS_PROJECT_AUTHORITY"."OBJECT_ID"=:B1)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
64787 consistent gets
0 physical reads
0 redo size
7186 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

很明显是filter的问题，这里的filter相当于nestedloop，第一个节点产生多少数据，那第二个节点就循环多少次。可以用dbms_xplan.display_cursor，下面执行计划的starts就是执行的次数。

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.39 | 64787 |
|* 1 | VIEW | | 1 | 1 | 10 |00:00:00.39 | 64787 |
| 2 | COUNT | | 1 | | 1904 |00:00:00.39 | 64787 |
|* 3 | FILTER | | 1 | | 1904 |00:00:00.39 | 64787 |
|* 4 | TABLE ACCESS FULL | PMS_PROJECT | 1 | 11485 | 11498 |00:00:00.05 | 1450 |
| 5 | UNION-ALL | | 11498 | | 1904 |00:00:00.31 | 63337 |
|* 6 | INDEX RANGE SCAN | IDX_AUTHORITY | 11498 | 1 | 1904 |00:00:00.13 | 34494 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PMS_PROJECT_AUTHORITY | 9594 | 1 | 0 |00:00:00.12 | 28843 |
|* 8 | INDEX RANGE SCAN | IDX_ROLE | 9594 | 1 | 9630 |00:00:00.08 | 19229 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10))
3 - filter( IS NOT NULL)
4 - filter((TO_NUMBER("PROJECT_TYPE_CODE")=4 AND "PROJECT_TYPE_CODE" IS NOT NULL))
6 - access("PUB_OBJECT_AUTHORITY"."CLASS_NAME"='PS_EXECUTE' AND
"PUB_OBJECT_AUTHORITY"."USER_ID"='00001311.fs')
filter(:B1 LIKE "PUB_OBJECT_AUTHORITY"."DEPARTMENT_CODE"||'%')
7 - filter(("PMS_PROJECT_AUTHORITY"."USER_ID"='00001311.fs' AND
"PMS_PROJECT_AUTHORITY"."OBJECT_TYPE"='OBJECT_PROJECT'))
8 - access("PMS_PROJECT_AUTHORITY"."OBJECT_ID"=:B1)

改进方法1：
SELECT *
FROM (SELECT *
FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (SELECT *
FROM PMS_PROJECT
WHERE 1 = 1
AND PROJECT_TYPE_CODE = 4
AND PROJECT_TYPE_CODE IS NOT null
and EXISTS
(SELECT ''
FROM PMS_PROJECT_AUTHORITY
WHERE PMS_PROJECT_AUTHORITY.OBJECT_TYPE =
'OBJECT_PROJECT'
AND PMS_PROJECT_AUTHORITY.USER_ID =
'00001311.fs'
AND PMS_PROJECT_AUTHORITY.OBJECT_ID =
PMS_PROJECT.PROJECT_ID)
union
SELECT *
FROM PMS_PROJECT
WHERE 1 = 1
AND PROJECT_TYPE_CODE = 4
AND PROJECT_TYPE_CODE IS NOT null
and EXISTS
(SELECT ''
FROM PUB_OBJECT_AUTHORITY
WHERE PUB_OBJECT_AUTHORITY.CLASS_NAME =
'PS_EXECUTE'
AND PUB_OBJECT_AUTHORITY.USER_ID =
'00001311.fs'
AND PMS_PROJECT.EXE_DEPT_CODE LIKE
PUB_OBJECT_AUTHORITY.DEPARTMENT_CODE || '%')) INNER_TABLE)
WHERE OUTER_TABLE_ROWNUM <= 10) OUTER_TABLE

WHERE OUTER_TABLE_ROWNUM > 0;

已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 63579047
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 834 | 2014K| 99 (4)| 00:00:02 |
|* 1 | VIEW | | 834 | 2014K| 99 (4)| 00:00:02 |
| 2 | COUNT | | | | | |
| 3 | VIEW | | 834 | 2004K| 99 (4)| 00:00:02 |
| 4 | SORT UNIQUE | | 834 | 448K| 99 (89)| 00:00:02 |
| 5 | UNION-ALL | | | | | |
| 6 | NESTED LOOPS | | 11 | 6138 | 12 (9)| 00:00:01 |
| 7 | SORT UNIQUE | | 11 | 451 | 5 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| PMS_PROJECT_AUTHORITY | 11 | 451 | 5 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_USER_ID_ROLE | 11 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | PMS_PROJECT | 1 | 517 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | IDX_PMS_PROJECT_PK | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PMS_PROJECT | 574 | 289K| 82 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 823 | 442K| 85 (0)| 00:00:02 |
|* 14 | INDEX RANGE SCAN | IDX_AUTHORITY | 1 | 34 | 3 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | EXE_DEPT_CODE_TEMP1 | 171 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10)
8 - filter("PMS_PROJECT_AUTHORITY"."OBJECT_TYPE"='OBJECT_PROJECT')
9 - access("PMS_PROJECT_AUTHORITY"."USER_ID"='00001311.fs')
10 - filter(TO_NUMBER("PROJECT_TYPE_CODE")=4 AND "PROJECT_TYPE_CODE" IS NOT NULL)
11 - access("PMS_PROJECT_AUTHORITY"."OBJECT_ID"="PMS_PROJECT"."PROJECT_ID")
12 - filter(TO_NUMBER("PROJECT_TYPE_CODE")=4 AND "PROJECT_TYPE_CODE" IS NOT NULL)
14 - access("PUB_OBJECT_AUTHORITY"."CLASS_NAME"='PS_EXECUTE' AND
"PUB_OBJECT_AUTHORITY"."USER_ID"='00001311.fs')
15 - access("PMS_PROJECT"."EXE_DEPT_CODE" LIKE "PUB_OBJECT_AUTHORITY"."DEPARTMENT_CODE"||'%')
filter("PMS_PROJECT"."EXE_DEPT_CODE" LIKE "PUB_OBJECT_AUTHORITY"."DEPARTMENT_CODE"||'%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
969 consistent gets
0 physical reads
0 redo size
7691 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed

改进方法2：
with temp1 as (
SELECT DEPARTMENT_ID
FROM PUB_OBJECT_AUTHORITY pa
WHERE pa.CLASS_NAME = 'PS_EXECUTE'
AND pa.USER_ID = '00001311.fs'
),
temp2 as(
select DEPARTMENT_ID from PUB_DEPARTMENT dept start with dept.DEPARTMENT_ID in(select DEPARTMENT_ID from temp1)
connect by prior dept.DEPARTMENT_ID = dept.SUPER_DEPARTMENT_ID
)
SELECT *
FROM PMS_PROJECT
WHERE 1 = 1
AND PROJECT_TYPE_CODE = '4'
AND PROJECT_TYPE_CODE IS NOT null
and PMS_PROJECT.EXE_DEPT_ID in (select DEPARTMENT_ID from temp2)
union
SELECT *
FROM PMS_PROJECT
where PMS_PROJECT.PROJECT_ID in
(select OBJECT_ID
from PMS_PROJECT_AUTHORITY
WHERE PMS_PROJECT_AUTHORITY.OBJECT_TYPE = 'OBJECT_PROJECT'
AND PMS_PROJECT_AUTHORITY.USER_ID = '00001311.fs');

已用时间: 00: 00: 00.09
执行计划
----------------------------------------------------------
Plan hash value: 2003914880
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 42058 | 349 (2)| 00:00:05 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | PUB_OBJECT_AUTHORITY | 1 | 34 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_AUTHORITY | 1 | | 3 (0)| 00:00:01 |
|* 5 | VIEW | | 17 | 42058 | 345 (2)| 00:00:05 |
| 6 | COUNT | | | | | |
| 7 | VIEW | | 17 | 41837 | 345 (2)| 00:00:05 |
| 8 | SORT UNIQUE | | 17 | 9276 | 345 (7)| 00:00:05 |
| 9 | UNION-ALL | | | | | |
|* 10 | HASH JOIN | | 6 | 3138 | 327 (2)| 00:00:04 |
| 11 | VIEW | | 8 | 48 | 5 (0)| 00:00:01 |
|* 12 | CONNECT BY WITH FILTERING | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | PUB_DEPARTMENT | | | | |
| 14 | NESTED LOOPS | | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | VIEW | | 1 | 7 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68DF_F9516B51 | 1 | 7 | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_PUB_DEPARTMENT | 1 | 6 | 0 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | | | | |
| 19 | CONNECT BY PUMP | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID| PUB_DEPARTMENT | 8 | 104 | 5 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IND_PUB_DEPT_SUPER_DEPT_ID | 8 | | 1 (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | PMS_PROJECT | 11485 | 5798K| 321 (1)| 00:00:04 |
| 23 | NESTED LOOPS | | 11 | 6138 | 16 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PMS_PROJECT_AUTHORITY | 11 | 451 | 5 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | IDX_USER_ID_ROLE | 11 | | 1 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | PMS_PROJECT | 1 | 517 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | IDX_PMS_PROJECT_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PA"."CLASS_NAME"='PS_EXECUTE' AND "PA"."USER_ID"='00001311.fs')
5 - filter("OUTER_TABLE_ROWNUM"<=10 AND "from$_subquery$_007"."OUTER_TABLE_ROWNUM">0)
10 - access("PMS_PROJECT"."EXE_DEPT_ID"="DEPARTMENT_ID")
12 - access("DEPT"."SUPER_DEPARTMENT_ID"=PRIOR "DEPT"."DEPARTMENT_ID")
17 - access("DEPT"."DEPARTMENT_ID"="DEPARTMENT_ID")
21 - access("DEPT"."SUPER_DEPARTMENT_ID"=PRIOR "DEPT"."DEPARTMENT_ID")
22 - filter("PROJECT_TYPE_CODE"='4' AND "PROJECT_TYPE_CODE" IS NOT NULL)
24 - filter("PMS_PROJECT_AUTHORITY"."OBJECT_TYPE"='OBJECT_PROJECT')
25 - access("PMS_PROJECT_AUTHORITY"."USER_ID"='00001311.fs')
27 - access("PMS_PROJECT"."PROJECT_ID"="OBJECT_ID")
统计信息
----------------------------------------------------------
2 recursive calls
8 db block gets
2920 consistent gets
1 physical reads
604 redo size
7691 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
10 rows processed

0 0