qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

使用SQL Profile进行SQL优化案例

　一个社保系统的自助查询系统查询个人医疗费用明细的查询语句要用一分多钟还没查询出来，语句如下：
　　select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'
　　从上面的语句可知是从视图 v_zzzd_ylbx_ylfymxcx中查询数据。v_zzzd_ylbx_ylfymxcx视图的创建语句如下：
create or replace view v_zzzd_ylbx_ylfymxcx as
select a.indi_id aac001,a.idcard aac002,'' aof008,a.center_id aab301,
a.name aac003,a.hospital_id akf008,d.hospital_name akf009,a.serial_no akf010,
f.biz_name akf011,
nvl(round(sum(b.real_pay),2),0) akf012,
nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end),2),0) akf013, 0 akf014,0 akf015,0 akf016,
nvl(round(sum(case when b.fund_id = '001' then b.real_pay else 0 end),2),0) ak093,
nvl(round(sum(b.real_pay),2),0) - nvl(round(sum(case when b.fund_id in ('999','003') and b.policy_item_code in ( 'S00','S01','C001','C004''C006') then b.real_pay else 0 end),2),0) ak092,
nvl(round(sum(case when b.fund_id in ('999','003') and b.policy_item_code in ( 'S00','S01','C001','C004''C006') then b.real_pay else 0 end),2),0) ak094,
nvl(round(sum(case when b.fund_id in('003', '999') then b.real_pay else 0 end),2),0) ak095,
a.fin_date akf017,to_char(nvl(a.in_days,0)) akf018,
nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end),2),0) akf019,
nvl(round(sum(case when b.fund_id in( '001','201','301' ) then b.real_pay else 0 end),2),0) akf020
from  bs_insured h,mt_biz_fin a ,mt_pay_record_fin b,bs_disease c,bs_hospital d ,bs_hosp_level e ,bs_biztype f,bs_corp g
where h.indi_id=a.indi_id
and a.hospital_id = b.hospital_id
and a.serial_no = b.serial_no
and a.biz_type = f.biz_type
and a.center_id = f.center_id
and a.center_id=c.center_id
and a.fin_disease=c.icd
and a.hospital_id = d.hospital_id
and d.hosp_level=e.hosp_level
and a.biz_type in ('10','11','12','13','16','17')
and a.valid_flag = 1
and b.valid_flag = 1
and a.pers_type in ('1','2')
and a.corp_id = g.corp_id
group by a.indi_id ,a.idcard ,a.center_id,a.name ,a.hospital_id ,d.hospital_name,a.serial_no , f.biz_name,a.fin_date,a.in_days;
　　生成SQL Profile有两种方式：自动和手动方式，这里使用自动方式来生成SQL Profile.
　　下面创建一个SQL自动调整优化任务：
SQL> declare
2   my_task_name varchar2(30);
3   my_sqltext clob;
4  begin
5   my_sqltext :='select * from  v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
6   my_task_name :=dbms_sqltune.create_tuning_task(
7           sql_text => my_sqltext,
8           user_name => 'INSUR_CHANGDE',
9           scope=>'COMPREHENSIVE',
10          time_limit=>60,
11          task_name => 'my_sql_tuning_task_2014080803',
12          description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
13  end;
14  /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2  dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080803');
3  end;
4  /
PL/SQL procedure successfully completed.
通过下面的语句查询优化建议
SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080803') from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_2014080803
Tuning Task Owner                 : INSUR_CHANGDE
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 08/08/2014 19:42:47
Completed at                      : 08/08/2014 19:43:49
Number of Index Findings          : 1
Number of SQL Restructure Findings: 1
Number of Errors                  : 1
-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID     : 0rpt6bzp60cjm
SQL Text   : select * from  v_zzzd_ylbx_ylfymxcx where
aac002='430703198202280017'
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
　　通过创建一个或多个索引可以改进此语句的执行计划。
　　Recommendation (estimated benefit: 99.98%)
　　------------------------------------------
　　- 考虑运行可以改进物理方案设计的 Access Advisor 或者创建推荐的索引。
　　create index INSUR_CHANGDE.IDX$$_429C0001 on
　　INSUR_CHANGDE.MT_BIZ_FIN("IDCARD",TO_NUMBER("VALID_FLAG"),"PERS_TYPE","BIZ_
　　TYPE");
　　这里在创建IDX$$_429C0001索引时,TO_NUMBER("VALID_FLAG")这是因为表MT_BIZ_FIN中的valid_flag是varchar2而视图定义中写成了valid_flag=1的原因
　　- 考虑运行可以改进物理方案设计的 Access Advisor 或者创建推荐的索引。
　　create index INSUR_CHANGDE.IDX$$_429C0002 on
　　INSUR_CHANGDE.MT_PAY_RECORD_FIN("HOSPITAL_ID","SERIAL_NO");
　　Rationale
　　---------
　　创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "Access Advisor"
　　可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
　　2- Restructure SQL finding (see plan 1 in explain plans section)
　　----------------------------------------------------------------
　　谓词 TO_NUMBER("A"."VALID_FLAG")=1 (在执行计划的行 ID 9 处使用) 包含索引列 "VALID_FLAG"
　　的隐式数据类型转换。此隐式数据类型转换使优化程序无法有效地使用表 "INSUR_CHANGDE"."MT_BIZ_FIN" 的索引。
　　这是因为表MT_BIZ_FIN中的valid_flag是varchar2而视图定义中写成了valid_flag=1的原因
　　Recommendation
　　--------------
　　- 将谓词重写为等价型以便利用索引。
　　Rationale
　　---------
　　如果谓词是不等式条件或者如果存在关于索引列的表达式或隐式数据类型转换, 则优化程序无法使用索引。
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- 当前操作因超时而中断。这是因为优化任务设置的超时时间为60秒的原因
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3562745886
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   1 |  HASH GROUP BY                      |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   2 |   NESTED LOOPS                      |                         |     7 |  1505 |   127K  (2)| 00:25:25 |
|   3 |    NESTED LOOPS                     |                         |     7 |  1491 |   127K  (2)| 00:25:25 |
|   4 |     NESTED LOOPS                    |                         |     7 |  1253 |   127K  (2)| 00:25:25 |
|   5 |      NESTED LOOPS                   |                         |     7 |  1127 |   127K  (2)| 00:25:25 |
|   6 |       NESTED LOOPS                  |                         |     7 |  1085 |   127K  (2)| 00:25:25 |
|   7 |        NESTED LOOPS                 |                         |    14 |  1554 |   127K  (2)| 00:25:25 |
|   8 |         NESTED LOOPS                |                         |    14 |  1484 |   127K  (2)| 00:25:25 |
|*  9 |          TABLE ACCESS FULL          | MT_BIZ_FIN              |    14 |  1232 |   127K  (2)| 00:25:25 |
|  10 |          TABLE ACCESS BY INDEX ROWID| BS_BIZTYPE              |     1 |    18 |     1   (0)| 00:00:01 |
|* 11 |           INDEX UNIQUE SCAN         | PK_BS_BIZTYPE           |     1 |       |     1   (0)| 00:00:01 |
|* 12 |         INDEX UNIQUE SCAN           | PK_BS_CORP              |     1 |     5 |     1   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN       |     1 |    44 |     1   (0)| 00:00:01 |
|* 14 |         INDEX RANGE SCAN            | IDX_MT_PAY_RECORD_FIN_1 |     1 |       |     1   (0)| 00:00:01 |
|* 15 |       INDEX UNIQUE SCAN             | PK_BS_INSURED           |     1 |     6 |     1   (0)| 00:00:01 |
|* 16 |      INDEX RANGE SCAN               | INX_BS_DISEASE_01       |     1 |    18 |     1   (0)| 00:00:01 |
|  17 |     TABLE ACCESS BY INDEX ROWID     | BS_HOSPITAL             |     1 |    34 |     1   (0)| 00:00:01 |
|* 18 |      INDEX UNIQUE SCAN              | PK_BS_HOSPITAL          |     1 |       |     1   (0)| 00:00:01 |
|* 19 |    INDEX UNIQUE SCAN                | PK_BS_HOSP_LEVEL        |     1 |     2 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("A"."IDCARD"='430703198202280017' AND TO_NUMBER("A"."VALID_FLAG")=1 AND
("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2') AND ("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR
"A"."BIZ_TYPE"='12' OR "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17'))
11 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
12 - access("A"."CORP_ID"="G"."CORP_ID")
13 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
14 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
15 - access("H"."INDI_ID"="A"."INDI_ID")
16 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
18 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
19 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
　　这是按优化建议创建两个索引后的执行计划
2- Using New Indices
--------------------
Plan hash value: 2373509962
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |     7 |  1505 |    14   (8)| 00:00:01 |
|   1 |  HASH GROUP BY                       |                   |     7 |  1505 |    14   (8)| 00:00:01 |
|   2 |   NESTED LOOPS                       |                   |     7 |  1505 |    13   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                      |                   |     7 |  1470 |    12   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                   |     7 |  1428 |    11   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                    |                   |     7 |  1302 |    10   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                   |                   |     7 |  1288 |     9   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                  |                   |     7 |  1050 |     7   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                 |                   |    14 |  1484 |     4   (0)| 00:00:01 |
|   9 |          INLIST ITERATOR             |                   |       |       |            |          |
|  10 |           TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    14 |  1232 |     2   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | IDX$$_429C0001    |    14 |       |     1   (0)| 00:00:01 |
|  12 |          TABLE ACCESS BY INDEX ROWID | BS_BIZTYPE        |     1 |    18 |     1   (0)| 00:00:01 |
|* 13 |           INDEX UNIQUE SCAN          | PK_BS_BIZTYPE     |     1 |       |     1   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|* 15 |          INDEX RANGE SCAN            | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX UNIQUE SCAN                | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 21 |    INDEX UNIQUE SCAN                 | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("A"."IDCARD"='430703198202280017' AND "MT_BIZ_FIN".???)
filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
"A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
13 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
14 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
15 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
17 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
18 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
20 - access("H"."INDI_ID"="A"."INDI_ID")
21 - access("A"."CORP_ID"="G"."CORP_ID")
-------------------------------------------------------------------------------
　　因为前一次优化任务因为超时中断了所以再次进行SQL自动优化任务,并将超时时间设置为600秒
SQL> declare
2   my_task_name varchar2(30);
3   my_sqltext clob;
4  begin
5   my_sqltext :='select * from  v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
6   my_task_name :=dbms_sqltune.create_tuning_task(
7           sql_text => my_sqltext,
8           user_name => 'INSUR_CHANGDE',
9           scope=>'COMPREHENSIVE',
10          time_limit=>600,
11          task_name => 'my_sql_tuning_task_2014080804',
12          description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
13  end;
14  /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2  dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080804');
3  end;
4  /
PL/SQL procedure successfully completed.
通过下面的语句查询优化建议
SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080806') from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_2014080804
Tuning Task Owner                 : INSUR_CHANGDE
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 600
Completion Status                 : COMPLETED
Started at                        : 08/08/2014 20:03:46
Completed at                      : 08/08/2014 20:04:27
Number of SQL Profile Findings    : 1
-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID     : 0rpt6bzp60cjm
SQL Text   : select * from  v_zzzd_ylbx_ylfymxcx where
aac002='430703198202280017'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
　　为此语句找到了性能更好的执行计划。
　　Recommendation (estimated benefit: 28.75%)
　　------------------------------------------
　　- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2014080804', replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3514293130
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |   251 | 53965 |    36   (6)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    36   (6)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                   |   251 | 53965 |    35   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   252 | 52920 |    34   (3)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |   252 | 51408 |    33   (4)| 00:00:01 |
|*  5 |      HASH JOIN                        |                   |   251 | 46686 |    32   (4)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID     | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4704 |    28   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3472 |    22   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                   |    79 |  9638 |    21   (0)| 00:00:01 |
|  10 |           INLIST ITERATOR             |                   |       |       |            |          |
|  11 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|  13 |           TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 14 |            INDEX UNIQUE SCAN          | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 15 |          INDEX UNIQUE SCAN            | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN              | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
|  17 |       TABLE ACCESS FULL               | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|* 18 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 19 |     INDEX UNIQUE SCAN                 | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 20 |    INDEX UNIQUE SCAN                  | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
6 - filter("B"."VALID_FLAG"='1')
12 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1'
OR "A"."PERS_TYPE"='2'))
filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
"A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
14 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
15 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
16 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
18 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
19 - access("H"."INDI_ID"="A"."INDI_ID")
20 - access("A"."CORP_ID"="G"."CORP_ID")
2- Using SQL Profile
--------------------
Plan hash value: 484693682
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                   |   251 | 53965 |    25   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID         | MT_PAY_RECORD_FIN |     1 |    44 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                   |   251 | 53965 |    24   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                   |    28 |  4788 |    19   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                   |    28 |  4284 |    18   (6)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                   |    28 |  4116 |    17   (6)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                   |    28 |  4060 |    16   (7)| 00:00:01 |
|   8 |         NESTED LOOPS                  |                   |    28 |  3108 |    10   (0)| 00:00:01 |
|*  9 |          HASH JOIN                    |                   |    28 |  2968 |     9   (0)| 00:00:01 |
|  10 |           TABLE ACCESS FULL           | BS_BIZTYPE        |    96 |  1728 |     3   (0)| 00:00:01 |
|  11 |           INLIST ITERATOR             |                   |       |       |            |          |
|  12 |            TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN        |    79 |  6952 |     6   (0)| 00:00:01 |
|* 13 |             INDEX RANGE SCAN          | IDX$$_429C0001    |    27 |       |     1   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN            | PK_BS_CORP        |     1 |     5 |     1   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL       |     1 |    34 |     1   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN            | PK_BS_HOSPITAL    |     1 |       |     1   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN              | PK_BS_HOSP_LEVEL  |     1 |     2 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN               | PK_BS_INSURED     |     1 |     6 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                 | INX_BS_DISEASE_01 |     1 |    18 |     1   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN                  | IDX$$_429C0002    |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."VALID_FLAG"='1')
9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1'
OR "A"."PERS_TYPE"='2'))
filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
"A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
14 - access("A"."CORP_ID"="G"."CORP_ID")
16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
18 - access("H"."INDI_ID"="A"."INDI_ID")
19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
-------------------------------------------------------------------------------

posted on 2014-08-13 10:25 顺其自然EVO 阅读(416) 评论(0)  编辑  收藏 所属分类: 测试学习专栏


只有注册用户登录后才能发表评论。
网站导航:
 
<2014年8月>
日一二三四五六
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456

导航

统计

常用链接

留言簿(55)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜