SQL 改写优化

explain plan for Select *
From (Select 系统序号,
模板名称,
病历类别,
建立科室,
建立人,
建立时间,
应该完成时间,
完成时间,
审阅申请时间,
审阅时间,
打印次数,
建立科室I,
状态颜色C,
分级编码
From EMR_VD住院病历列表_医生站 A
Where A.住院序号i = 30240
And A.完成时间 is null
Order By 分级编码, 显示顺序, 系统序号) T;


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------


Plan hash value: 1107059487

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 23180 | 21019 (1)| 00:04:13 |
| 1 | SORT ORDER BY | | 61 | 23180 | 21019 (1)| 00:04:13 |
|* 2 | VIEW | EMR_VD住院病历列 | 61 | 23180 | 21018 (1)| 00:04:13 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | 1 | 111 | 17 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 111 | 17 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 1 | 100 | 16 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 87 | 15 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 87 | 15 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMR_D住院病历列表| 1 | 66 | 14 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_77598_住院序 | 14 | | 3 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_T病历类别 | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | EMR_T病历类别 | 1 | 21 | 1 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION TO ROWIDS | | 97 | 1261 | 1 (0)| 00:00:01 |
| 14 | BITMAP INDEX FAST FULL SCAN | DOC_T科室档案_系 | | | | |
|* 15 | INDEX UNIQUE SCAN | PK_T员工档案 | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案 | 1 | 11 | 1 (0)| 00:00:01 |
| 17 | COUNT | | | | | |
|* 18 | HASH JOIN | | 60 | 3600 | 21001 (1)| 00:04:13 |
|* 19 | FILTER | | | | | |
|* 20 | HASH JOIN OUTER | | 60 | 2340 | 20998 (1)| 00:04:12 |
|* 21 | FILTER | | | | | |
|* 22 | HASH JOIN OUTER | | 119 | 2975 | 20995 (1)| 00:04:12 |
|* 23 | TABLE ACCESS FULL | CIS_D病历质控_超 | 11927 | 186K| 52 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | EMR_D住院病历列表| 416K| 3662K| 20942 (1)| 00:04:12 |
|* 25 | TABLE ACCESS FULL | EMR_T病历类别 | 1 | 14 | 3 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | EMR_T病历类别 | 40 | 840 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("A"."完成时间" IS NULL AND "A"."住院序号I"=30240)
6 - access("D"."系统序号"(+)="A"."建立科室I")
9 - filter("A"."完成时间" IS NULL)
10 - access("A"."住院序号I"=30240)
11 - access("C"."系统序号"="A"."病历类别I")
15 - access("A"."建立人R"="B"."系统序号")
18 - access("B"."系统序号"="A"."病历类别I")
19 - filter("D"."系统序号" IS NULL)
20 - access("A"."病历类别I"="D"."系统序号"(+))
21 - filter("C"."住院序号I" IS NULL)
22 - access("A"."病历类别I"="C"."病历类别I"(+) AND "A"."住院序号I"="C"."住院序号I"(+))
23 - filter("A"."病历类别I" IS NOT NULL)
25 - filter("D"."名称"(+)=U'\65E5\5E38\75C5\7A0B\8BB0\5F55')

已选择50行。

其中EMR_VD住院病历列表_医生站  是视图，过滤列在视图中查找很快，在视图外很慢


解决方案：
Where A.住院序号i = 30240 带入视图中提前过滤得以优化