Oracle性能优化之虚拟索引

虚拟索引是定义在数据字典中的伪索引,但没有相关的索引段。虚拟索引的目的是模拟索引的存–而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响,并且不会增加存储空间的使用。如果我们观察到优化器生成了一个昂贵的执行计划并且SQL调整指导建议我们对某些的某列创建索引,但在生产数据库环境中创建索引与测试并不总是可以操作。我们需要确保创建的索引将不会对数据库中的其它查询产生负面影响,因此可以使用虚拟索引。

下面举例进行说明
1.创建一个测试表test

SQL> create table test as select * from dba_objects;

Table created.

2.从表test查询object_name等于standard的记录

SQL> select * from test where object_name='STANDARD';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED      LAST_DDL_TIM TIMESTAMP           STATUS  T G S
------------ ------------ ------------------- ------- - - -
SYS
STANDARD
                                      888                PACKAGE
19-APR-10    19-APR-10    2003-04-18:00:00:00 VALID   N N N


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED      LAST_DDL_TIM TIMESTAMP           STATUS  T G S
------------ ------------ ------------------- ------- - - -
SYS
STANDARD
                                      889                PACKAGE BODY
19-APR-10    19-APR-10    2010-04-19:10:22:58 VALID   N N N

3.查询上面查询的执行计划

SQL> set autotrace traceonly explain
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |  1416 |   155   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     8 |  1416 |   155   (1)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='STANDARD')

Note
-----
   - dynamic sampling used for this statement

4.在表test的object_name列上创建一个虚拟索引

SQL> create index test_index on test(object_name) nosegment;

Index created.

为了创建虚拟索引必须在create index语句中指定nosegment子句,并且不会创建索引段。

5.来验证虚拟索引不会创建索引段

SQL> set autotrace off
SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX';

no rows selected

SQL> col OBJECT_NAME format a20;
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX';

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
TEST_INDEX           INDEX

从上面的结果可以看到索引对象已经创建,但没有创建索引段。

6.重新执行sql查看创建的虚拟索引是否被使用

SQL> set autotrace traceonly explain
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |  1416 |   155   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     8 |  1416 |   155   (1)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='STANDARD')

Note
-----
   - dynamic sampling used for this statement

从上面的执行计划可以清楚地看到创建的虚拟索引并没有被使用

7.为了能使用所创建的虚拟索引,需要将_USE_NOSEGMENT_INDEXES设置为true

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.

8.重新执行sql查看创建的虚拟索引是否被使用

SQL> set long 900
SQL> set linesize 900
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 2627321457

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     8 |  1416 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     8 |  1416 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_INDEX |   238 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='STANDARD')

Note
-----
   - dynamic sampling used for this statement

从上面的执行计划可以看到当设置隐含参数_USE_NOSEGMENT_INDEXES后,优化器将会使用创建的虚拟索引。在使用虚拟索引需要注意,我们可以分析虚拟索引,但不能重建虚拟索引,如果重建虚拟索引会收到ORA-8114: “User attempted to alter a fake index”错误提示,可以删除虚拟索引。

Oracle 11gr2中的自动并行度

在Oracle 11.2.0.2中只有I/O统计数据被收集才能使用自动并行度。当parallel_degree_policy被设置为auto时,Oracle数据库将会基于执行计划中操作的成本和硬件特性来判断是否使用并行,当在语句级别使用parallel或parallel(auto)暗示不管parallel_degree_policy设置为何值都会使用自动并行。

IO Calibration
硬件特性包括IO Calibration统计数据,因此这些统计数据必须被收集否则Oracle数据库将不会使用自动并行这个功能。下面的执行计划是在没有收集IO Calibration统计数据时生成的,在执行计划的note部分可以看到”skipped because of IO calibrate statistics are missing”这样的信息

SQL> set long 900
SQL> set linesize 900
SQL> set autotrace traceonly explain
SQL> select /*+ parallel */ * from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |  1218 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: skipped because of IO calibrate statistics are missing


Oracle提供了PL/SQL包dbms_resource_manager.calibrate_io来收集IO Calibration的统计数据。收集IO Calibration统计数据的持续时间由num_disks变量与RAC中节点数决定的。

SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
NOT AVAILABLE

SET SERVEROUTPUT ON
DECLARE
   lat INTEGER;
   iops INTEGER;
   mbps INTEGER;
BEGIN
    --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
   DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
   DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
   dbms_output.put_line('max_mbps = ' || mbps);
END;
/

注意DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前两个参数分别为num_disks,max_latency是输入变量,并且有三个输出变量。

num_disks:为了获得最精确的结果,最好提供数据库所使用的真实物理磁盘数。如果是使用ASM来管理数据库文件,那么就是指存储数据的磁盘组,那么只有存储数据的磁盘组中的物理磁盘作为num_disks变量值,不包含FRA磁盘组中的物理磁盘。

latency:对数据库块IO操作允许的最大延迟


SQL> set long 900
SQL> set linesize 900
SQL> SET SERVEROUTPUT ON
DECLARE
SQL>   2     lat INTEGER;
  3     iops INTEGER;
  4     mbps INTEGER;
  5  BEGIN
  6      --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
  7      DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
  8     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  9     DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
 10     dbms_output.put_line('max_mbps = ' || mbps);
 11  END;
 12  /
max_iops = 390
latency = 9
max_mbps = 112

PL/SQL procedure successfully completed.

为了验证是否IO Calibration统计信息收集成功,在执行dbms_resource_manager.calibrate_io后查询v$io_calibration_status

SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY         13-APR-16 10.12.58.413 PM

再次执行看是否能使用自动并行度

SQL> set autotrace traceonly explain
SQL> select /*+ parallel */ * from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |   532 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

可以看到在收集IO Calibration统计信息后,执行计划使用自动并行度。

当使用自动并行度,可以还需要调整一些调整参数。parallel_servers_target参数应该总是比parallel_max_servers参数值小,parallel_servers_target总是处于parallel_max_servers的75%到50%。如果开始看到大量并行度下降,那么应该使用这两个参灵敏的差距增大。

使用rman增量备份来更新传输表空间

要使用rman增量备份来更新传输表空间需要了解传输表空间与rman的增量备份。这里主要介绍使用增量备份来更新传输表空间,就不介绍传输表空间与rman增量备份。下面是使用rman增量备份来更新传输表空间的操作。目标主机是weblogic29,原主机是weblogic28。
1.在两台数据库服务器上配置NFS
配置/etc/exports
nfs允许挂载的目录及权限需在文件/etc/exports中进行定义。例如,我们要将数据文件所在目录
/u01/app/oracle/oradata/jytest/与/backup目录共享出来,那么我们需要编辑/etc/exports文件,追加两行

/u01/app/oracle/oradata/jytest/ *(rw,sync)
/backup/ *(rw,sync)
[root@weblogic29 oracle]# vi /etc/exports
/u01/app/oracle/oradata/jytest/ *(rw,sync)
/backup/ *(rw,sync)

启动nfs服务

[root@weblogic29 oracle]# service portmap start
Starting portmap: [ OK ]
[root@weblogic29 oracle]# service nfs start
Starting NFS services: [ OK ]
Starting NFS quotas: [ OK ]
Starting NFS daemon: [ OK ]
Starting NFS mountd: [ OK ]

在客户端主机上挂载共享目录

[root@weblogic28 ~]# service portmap start
Starting portmap: [ OK ]

[root@weblogic28 ~]# service nfs start
Starting NFS services: [ OK ]
Starting NFS quotas: [ OK ]
Starting NFS daemon: [ OK ]
Starting NFS mountd: [ OK ]

在客户端使用showmount -e IP 查看nfs主机共享情况:

[root@weblogic28 ~]# showmount -e 10.138.130.29
Export list for 10.138.130.29:
/backup *
/u01/app/oracle/oradata/jytest *

在客户端建立NFS文件夹并执行mount挂载命令:

[root@weblogic28 ~]# mkdir /jytest_data
[root@weblogic28 ~]# mkdir /backup
[root@weblogic28 ~]# chown -R oracle:oinstall /jytest_data
[root@weblogic28 ~]# chown -R oracle:oinstall /backup
[root@weblogic28 ~]# chmod -R 777 /jytest_data
[root@weblogic28 ~]# chmod -R 777 /backup

[root@weblogic28 ~]# mount -t nfs 10.138.130.29:/u01/app/oracle/oradata/jytest /jytest_data
[root@weblogic28 ~]# mount -t nfs 10.138.130.29:/backup /backup

[root@weblogic28 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 240G 158G 71G 70% /
/dev/sda1 190M 12M 169M 7% /boot
tmpfs 16G 0 16G 0% /dev/shm
10.138.130.29:/u01/app/oracle/oradata/jytest
240G 22G 206G 10% /jytest_data
10.138.130.29:/backup
240G 22G 206G 10% /backup


2.在源数据库中创建一个表空间jytest与用户jytest

SQL> create tablespace jytest datafile '/u01/app/oracle/oradata/jytest/jytest01.dbf' size 5M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> create user jytest identified by "jytest" default tablespace jytest temporary tablespace temp;

User created.

SQL> grant connect,dba,resource to jytest;

Grant succeeded.

SQL> conn jytest/jytest
Connected.
SQL> create table t1 as select * from dba_tables;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----------
1607
SQL> insert into t1 select * from t1;

1607 rows created.

SQL> insert into t1 select * from t1;

3214 rows created.

SQL> insert into t1 select * from t1;

6428 rows created.

SQL> commit;

Commit complete.

3.将原数据库的jytest表空间设置为只读模式

SQL> alter tablespace jytest read only;

Tablespace altered.

4.对原数据库中的表空间jytest使用rman生成镜像副本并存储在NFS所挂载的/jytest_data目录中

[oracle@weblogic28 ~]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Apr 13 12:36:05 2016

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: JYTEST (DBID=3911337604)

RMAN> run
2> {
3> allocate channel c1 type disk format '/jytest_data/jytest01.dbf';
4> backup incremental level 1 tag "INCR_JYTEST" for recover of copy with tag "INCR_JYTEST" tablespace jytest;
5> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=157 devtype=DISK

Starting backup at 13-APR-16
WARNING: TAG INCR_JYTEST option is ignored; backups will be tagged with INCR_JYTEST
no parent backup or copy of datafile 8 found
channel c1: starting datafile copy
input datafile fno=00008 name=/u01/app/oracle/oradata/jytest/jytest01.dbf
output filename=/jytest_data/jytest01.dbf tag=INCR_JYTEST recid=2 stamp=909059896
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-APR-16
released channel: c1

虽然这里使用的是incremental level 1,因为这里不存在表空间数据文件jytest01.dbf的0级增量副本,因此会创建一个0级增量副本文件。

SQL> alter tablespace jytest read write;

Tablespace altered.

5.将表空间jytest附加到目标数据库

SQL> create or replace directory test_dump as '/backup/';

Directory created.

SQL> grant read,write on directory test_dump to public;

Grant succeeded.
'
SQL> create public database link dblink_jytest
2 connect to jytest identified by "jytest"
3 using '(DESCRIPTION =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.28)(PORT = 1521))
5 (CONNECT_DATA =
6 (SERVICE_NAME = jytest)
7 )
8 )';

Database link created.
SQL> select count(*) from t1@dblink_jytest;

COUNT(*)
----------
12856
[oracle@weblogic29 jytest]$ impdp jytest/jytest directory=test_dump network_link=dblink_jytest transport_tablespaces=jytest transport_full_check=n transport_datafiles=\'/u01/app/oracle/oradata/jytest/jytest01.dbf\'

Import: Release 10.2.0.5.0 - 64bit Production on Wednesday, 13 April, 2016 14:47:43

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYTEST"."SYS_IMPORT_TRANSPORTABLE_01": jytest/******** directory=test_dump network_link=dblink_jytest transport_tablespaces=jytest transport_full_check=n transport_datafiles='/u01/app/oracle/oradata/jytest/jytest01.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "JYTEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:47:48
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.3.0
SQL> select count(*) from jytest.t1;

COUNT(*)
----------
12856

6.将表空间jytest从目标数据库中删除,但保留数据文件

SQL> drop tablespace jytest including contents;

Tablespace dropped.

7.将原数据库中的表空间jytest联机,继续向表t1插入记录

SQL> alter tablespace jytest read write;

Tablespace altered.
SQL> insert into t1 select * from t1;
insert into t1 select * from t1
*
ERROR at line 1:
ORA-01653: unable to extend table JYTEST.T1 by 128 in tablespace JYTEST

由于表空间jytest没有空间了,如是向表这僮jytest增加一个数据文件jytest02.dbf来增加表空间

SQL> alter tablespace jytest add datafile '/u01/app/oracle/oradata/jytest/jytest02.dbf' size 5M;

Tablespace altered.

SQL> insert into t1 select * from t1;

12856 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)
----------
25712

8.如果自上次增量备份以后原数据库表空间jytest增加了新的数据文件,因此执行以下命令来为新增加的数据文件创建镜像副本。

SQL> alter tablespace jytest read only;

Tablespace altered.
SQL> select file#,name from v$datafile;

FILE# NAME
---------- -------------------------------------------------------------------------
1 /u01/app/oracle/oradata/jytest/system01.dbf
2 /u01/app/oracle/oradata/jytest/undotbs01.dbf
3 /u01/app/oracle/oradata/jytest/sysaux01.dbf
4 /u01/app/oracle/oradata/jytest/users01.dbf
5 /u01/app/oracle/oradata/jytest/example01.dbf
6 /u01/app/oracle/oradata/jytest/tspitr01.dbf
7 /u01/app/oracle/oradata/jytest/test01.dbf
8 /u01/app/oracle/oradata/jytest/jytest01.dbf
9 /u01/app/oracle/oradata/jytest/jytest02.dbf

9 rows selected.
RMAN> run
2> {
3> allocate channel c1 type disk format '/jytest_data/jytest02.dbf';
4> backup incremental level 1 tag "INCR_JYTEST" for recover of copy with tag "INCR_JYTEST" datafile 9;
5> }

allocated channel: c1
channel c1: sid=141 devtype=DISK

Starting backup at 13-APR-16
WARNING: TAG INCR_JYTEST option is ignored; backups will be tagged with INCR_JYTEST
no parent backup or copy of datafile 9 found
channel c1: starting datafile copy
input datafile fno=00009 name=/u01/app/oracle/oradata/jytest/jytest02.dbf
output filename=/jytest_data/jytest02.dbf tag=INCR_JYTEST recid=4 stamp=909069392
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-APR-16
released channel: c1

9.对原数据库执行RMAN增量备份并使用目标数据库文件目录中的数据文件与其合并,因些创建一组
新的数据文件

RMAN> run
2> {
3> allocate channel c1 type disk format '/jytest_data/jytest01_%t.dbf';
4> allocate channel c2 type disk format '/jytest_data/jytest02_%t.dbf';
5> backup incremental level 1 tag "INCR_JYTEST" for recover of copy with tag "INCR_JYTEST" tablespace jytest;
6> recover copy of tablespace jytest with tag "INCR_JYTEST";
7> }

allocated channel: c1
channel c1: sid=141 devtype=DISK

allocated channel: c2
channel c2: sid=139 devtype=DISK

Starting backup at 13-APR-16
WARNING: TAG INCR_JYTEST option is ignored; backups will be tagged with INCR_JYTEST
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00008 name=/u01/app/oracle/oradata/jytest/jytest01.dbf
channel c1: starting piece 1 at 13-APR-16
channel c2: starting incremental level 1 datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00009 name=/u01/app/oracle/oradata/jytest/jytest02.dbf
skipping datafile 00009 because it has not changed
channel c2: backup cancelled because all files were skipped
channel c1: finished piece 1 at 13-APR-16
piece handle=/jytest_data/jytest01_909069660.dbf tag=INCR_JYTEST comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-APR-16

Starting recover at 13-APR-16
channel c1: starting incremental datafile backupset restore
channel c1: specifying datafile copies to recover
recovering datafile copy fno=00008 name=/jytest_data/jytest01.dbf
channel c1: reading from backup piece /jytest_data/jytest01_909069660.dbf
channel c1: restored backup piece 1
piece handle=/jytest_data/jytest01_909069660.dbf tag=INCR_JYTEST
channel c1: restore complete, elapsed time: 00:00:02
Finished recover at 13-APR-16
released channel: c1
released channel: c2

10.将表空间jytest重新附加到目标数据库中

[oracle@weblogic29 jytest]$ impdp jytest/jytest directory=test_dump network_link=dblink_jytest transport_tablespaces=jytest transport_full_check=n transport_datafiles=\'/u01/app/oracle/oradata/jytest/jytest01.dbf\',\'/u01/app/oracle/oradata/jytest/jytest02.dbf\'

Import: Release 10.2.0.5.0 - 64bit Production on Wednesday, 13 April, 2016 15:50:37

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYTEST"."SYS_IMPORT_TRANSPORTABLE_01": jytest/******** directory=test_dump network_link=dblink_jytest transport_tablespaces=jytest transport_full_check=n transport_datafiles='/u01/app/oracle/oradata/jytest/jytest01.dbf','/u01/app/oracle/oradata/jytest/jytest02.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "JYTEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:50:42

SQL> select count(*) from jytest.t1;

COUNT(*)
----------
25712

SQL> alter tablespace jytest read write;

Tablespace altered.

与原数据库中表t1记录数一样,说明增量更新传输表空间成功。

总结:使用增量备份来前更新数据文件要比复制整个数据文件所花的时间少。这里使用了NFS来执行数据文件的传输避免了使用ftp等方式传输文件,使用impdp network_link避免了导入和导出元数据与传输元数据这也能节省了时间。

如何提高datapump操作性能

当执行datapump导出和导入时都想尽一切办法来提高性能,这里介绍一些可以显著提高DataPump性能的相关DataPump与数据库参数
一.影响DataPump相关的DataPump参数
access_method
在某些情况下由Data Pump API所选择的方法不能快速的访问你的数据集。在这种情况下除了显式地设置该参数来测试每一种访问方法之外你是无法知道那种访问方法更高效的。该参数有两种选项direct_path与external_table

cluster=n
在RAC环境中可以显著提供高Data Pump API基本操作的速度。注意这个参数只对Data Pump API操作起作用,在RAC环境中,建议将该参数设置为n。而如果将parallel_force_local设置为true所带来的影响不仅仅只针对Data Pump API操作

data_options=disable_append_hint
它只是impdp参数,在非常特殊的情况下,可以安全的使用并且可能减少导入数据的时间。只有满足以下所有条件时才使用data_options=disable_append_hint参数。
1.导入操作将向已经存在的表,分区或子分区导入数据
2.将被导入的已经存在的对象数非常少(比如是10或者更小)
3.当执行导入操作时其它会话对于这些被导入的对象只执行select语句。
data_options=disable_append_hint参数只有在11.2.0.1与更高版本中才可以使用。只有在要锁定由其它会话所释放对象花费很长时间的情况下使用data_option=disable_append_hint才能节省时间。

estimate
estimate参数有两个相互排斥的选项,一个是blocks,另一个是statistics.在执行导出操作时使用blocks方法来评估数据集大小比使用statistics方法消耗的时间更长。但是使用blocks方法评估的数据集大小要比使用statistics方法评估的数据集大小要精确些。如果导出文件的评估大小不是最主要关注的事,建议使用estimate=statistics。

exclude=comment
在某些情况下,终端用户不需要列和对象类型对应的注释,如果忽略这些数据,DataPump操作将会减少执行时间。

exclude=statistics
如果不需要使用排斥的include参数,那么排除和导出统计信息将会缩短整个导出操作的时间。dbms_stats.gather_database_stats过程将在数据导入到目标数据库后来生成统计信息。DataPump操作当由DataPump引擎和任何其它的RDBMS会话并行执行对小表生成统计信息时可能会hang且无限期。对于运行时间超过1小时或更长时间的DataPump操作,可以考虑禁用数据库的自动统计信息收集任务为了临时禁用11g的自动统计信息收集任务因此DataPump操作不会与该任务产生竞争,以sys用户执行以下命令:
exec dbms_auto_task_admin.diable(client_name=>’auto optimizer stats collection’,
operation=>null,window_name=>null);
在DataPump操作完成之后重新启动统计信息收集任务:
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => ‘auto optimizer stats collection’, operation => NULL, window_name => NULL);

为了临时禁用10g的自动统计信息收集任务因此DataPump操作不会与该任务产生竞争,以sys用户执行以下命令:
exec sys.dbms_scheduler.disable (‘GATHER_STATS_JOB’);
在DataPump操作完成之后重新启动统计信息收集任务:
exec sys.dbms_scheduler.enable (‘GATHER_STATS_JOB’);

network_link
使用这个参数将会有效限制DataPump API的并行度,除非你的网络吞吐量和网络带宽比本地设备更好,使用network_link将会比使用导出文件慢很多。对于DataPump API性能来说,因为它倾向于比dump文件操作要慢很多,只建议network_link作为最后一招来使用。可以考虑使用移动或共享设备来存储dump文件来代替network_link来执行数据的迁移。

parallel
如果有多个CPU使用并且没有使用CPU绑定或磁盘I/O绑定或内存绑定且在dumpfile参数中没有使用多个dump文件,那么并行执行将会对性能产生正面影响。如果parallel参数设置为N,N>1,那么为了更好的使用并行执行建议dumpfile参数应该设置为不比parallel参数小。

需要注意的是,parallel参数是DataPump API可以使用的并发Data Pump工作进程的上限,但DataPump API可能使用的DataPump工作进程数要比这个参数指定的少,依赖于主机环境中的瓶颈,parallel参数指定的值小于可用CPU个数时Data Pump API基本操作可能会更快。

query
使用query参数会显著增加任何DataPump API基本操作的负载,这种开销与被查询表的数据量成正比。

remap_*
使用任何remap_*参数会显著增加任何DataPump API基本操作的负载,这种开销与被查询表的数据量成正比。

二.影响DataPump操作性能的相关数据库参数
aq_tm_processes=0
当这个参数被显式设置为0,可能对高级队列操作产生负面影响,进而对使用高级队列的DataPump基本操作产生负面影响。可以复原这个参数或者设置一个大于0的值

deferred_segment_creation=true
只适用于导入操作,这将会消除为空表分配空间所花费的时间。对于导出操作设置这个参数将不会对性能产生显著的影响。这个参数在11.2.0.2或更高版本中非常有用。

filesystemio_option=…
在特定情况下数据库实例将会对ACFS文件系统执行写操作,指定Data Pump API执行的写操作类型性质作为导出操作的一部分,NONE以外的其它参数值都可能造成导出操作变慢。

NLS_CHARACTERSET=… and NLS_NCHAR_CHARACTERSET=…
当源数据库与目标数据库之间这两个参数存在差异时,在任何时候执行导入操作时对于指定的分区表都不能使用多个DataPump工作进程来创建分区表和填充。在有些情况下,只有一个DataPump工作进程可以对表数据执行操作,这将会对表获得排他锁来阻止任何其它DataPump工作进程对相同的表执行操作。当分区表不存在排他锁时可以使用多个DataPump工作进程同时操作来显著提高对分区表导入数据的性能。

NLS_COMP=… and NLS_SORT=…
在一些罕见的情况下,数据库的这两个参数被设置为了binary这将显著提高DataPump API基本操作的速度。对于你的环境是否将这两个参数设置为binary能提高性能需要进行测试。在会话登录后在会话级别设置这两个参数可以通过以下的登录触发器来实现。
CREATE OR REPLACE TRIGGER sys.expdp_nls_session_settings AFTER LOGON ON DATABASE
DECLARE
V_MODULE VARCHAR2(60);
BEGIN
SELECT SYS_CONTEXT (‘USERENV’, ‘MODULE’) INTO V_MODULE FROM DUAL;
IF UPPER(V_MODULE) LIKE ‘UDE%’
THEN
BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_COMP=”BINARY”’;
EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_SORT=”BINARY”’;
END;
END IF;
END;
/

parallel_force_local=true
在RAC环境中可以显著提高DataPump API基本操作的性能并且避免并行DML操作的bug。但这个参数只能对11.2.0.2或更高版本使用。

streams_pool_size
为了避免bug 17365043 ‘STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY WHEN REDUCING STREAMS_POOL_SIZE’
建议将streams_pool_size设置以下查询所返回的结果值
select ‘ALTER SYSTEM SET STREAMS_POOL_SIZE=’||(max(to_number(trim(c.ksppstvl)))+67108864)||’ SCOPE=SPFILE;’
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and lower(a.ksppinm) in (‘__streams_pool_size’,’streams_pool_size’);

_memory_broker_stat_interval=999
如果在你的缓慢DataPump环境中resize操作消耗了大量时间,那么设置这个参数将会减少resize操作的频率,进而在一个指定时间跨度内减少resize操作延迟其它操作的所花的时间。这是因为DataPump API依赖大量的流功能来帮助导出和导入操作。建议将这个参数设置为999,如果streams_pool_size参数已经被显式设置并且频繁的出现resize操作。

三.表DDL级别影响DataPump性能的相关参数
network_link+securefiles
network_link参数当移动包含有lob列的表,且lob是为了使用securefiles将会使移动操作非常缓慢,当使用network_link参数移动包含用了使用securefiles而有lob列的表时会生成大量undo数据。原因是分布式事务分配请求被限制为跨数据库链路一次只有一个数据块,这意味着大数据集传输将会产生更多的传输。

securefiles(不使用network_link)
使用securefiles存储格式来存储LOB列数据允许包含lob列的表使用并行执行导出和导入
使用basicfiles存储格式来存储LOB列数据不允许包含lob列的表使用并行执行导出和导入

四.表DML级别影响DataPump性能的相关参数
在DataPump操作和另一个访问数据库对象的会话之间产生竞争(通常是对表,行数据的锁)
DataPump引擎在执行导出操作时将会等待由其它会话将其持有的行锁与表锁先释放,再执行相关表的导出和导入。DataPump引擎在执行导出操作时将会等待由其它会话所持有的行锁与表锁先释放再执行导出操作而典型导出工具不会等待。因此导出一张正在被频繁更新的表要比导出一个当前没有被更新的表要慢

百倍性能提升:谓词条件(a||b) vs (a,b)

某系统维护人员在执行一查询需要1个多小时才能执行完成,根本不能满足业务需求,通过应用系统日志找到了该SQL:

DEBUG: 2016-04-05 12:18:48,203: com.powerise.hygeia.framework.jdbclogger.PreparedStatementWrapper: 
exec : 2456844ms at com.powerise.hygeia.biz.medicare.entity.EnLiquidateDetailInfo.getLiquidatePersonInfo(EnLiquidateDetailInfo.java:2319)
select rowno, hos_serial, reg_flag,
       nvl((SELECT distinct t.policy_value
              FROM fc_biz_policy t
             WHERE t.policy_code = 'can_clinic_flag'
               and t.valid_flag = '1'
               and t.center_id = '430701'), (SELECT distinct t.policy_value
                FROM fc_biz_policy t
               WHERE t.policy_code =
                     'can_clinic_flag'
                 and t.valid_flag = '1'
                 and t.center_id = '0')) AS can_clinic_flag,
       decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D', '危重', '') DISEASE_TYPE,
       hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat,
       name, sex, pers_type, begin_date, end_date, fin_date, finish_date,
       reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id,
       idcard, district_code, office_grade,center_id,
       (select t.special_code
           from bs_corp_pres t
          where t.indi_id = w.indi_id) as special_code, corp_name, disease,
       in_area_name, in_dept_name,
       decode(apply_content, '126', '转外住院', '127', '转外复查', '普通住院') as apply_content,
       in_bed, bed_type, patient_id, remark, pos_code, reimburse_flag,
       fin_disease, ic_no, treatment_type,
       decode(reg_info || treatment_type, 'WD3120', '普通住院(转外住院)', 'WD2120', '普通住院(异地住院)', '1120', '普通住院(转诊转院)', 'F120', '普通住院(非首诊就诊)', 'WD1120', '普通住院(异地安置)', 'C120', '普通住院(首诊就诊)', (select treatment_name
                   from bs_treat_type
                  where treatment_type =
                        w.treatment_type
                    and center_id =
                        w.center_id)) as treatment_name,
       nvl(pay_money_jd, 0) as pay_money_jd,
       nvl(pay_money_xzf, 0) as pay_money_xzf, nvl(fees, 0) as fees,
       foregift, nvl(pay_money_allself, 0) as pay_money_allself,
       nvl(pay_money_self, 0) as pay_money_self,
       nvl(pay_money_E00self, 0) as pay_money_E00self,
       nvl(pay_money_Z00self, 0) as pay_money_Z00self,
       nvl(pay_money_S00self, 0) as pay_money_S00self,
       nvl(pay_money_S01self, 0) as pay_money_S01self,
       nvl(pay_money_S01self, 0) as pay_money_S01self,
       nvl(pay_money_C000self, 0) as pay_money_C000self,
       nvl(pay_money_C001self, 0) as pay_money_C001self,
       nvl(pay_money_C004self, 0) as pay_money_C004self,
       nvl(pay_money_C006self, 0) as pay_money_C006self,
       nvl(pay_money_C007self, 0) as pay_money_C007self,
       nvl(pay_money_C007003self, 0) as pay_money_C007003self,
       nvl(tc_money, 0) as tc_money, nvl(tc_money_xe, 0) as tc_money_xe,
       nvl(hosp_pay, 0) as hosp_pay, nvl(hosp_prise, 0) as hosp_prise,
       nvl(jmtc_money, 0) as jmtc_money, nvl(yw_money, 0) as yw_money,
       nvl(jmyw_money, 0) as jmyw_money, nvl(acct_money, 0) as acct_money,
       nvl(lx_money, 0) as lx_money, nvl(sy_money, 0) as sy_money,
       nvl(fund_money, 0) as fund_money, nvl(fund_301,0) fund_301 ,nvl(fund_003,0) fund_003 ,nvl(qfx, 0) as qfx,
       nvl(hosp_zf, 0) as hosp_zf, nvl(center_zf, 0) as center_zf,
       nvl(yw_fund_money, 0) as yw_fund_money,
       nvl(bc_fund_money, 0) as bc_fund_money, nvl(db_money, 0) as db_money,
       nvl(YWSH_DB_MONEY, 0) as YWSH_DB_MONEY,
       nvl(offi_money, 0) as offi_money, reg_man,
       nvl(zhaogu_pay, 0) as zhaogu_pay, w.area_code, PAY_MONEY_DNZF,
       medi_pay, medi_zfy, nvl(DB_MONEY_JUMIN, 0) as db_money_jumin
  from (select rownum rowno, b.hos_serial, b.reg_flag, b.disease_type,
                a.hospital_id, a.hospital_name, b.serial_no, b.biz_type,
                b.case_id, g.biz_stat, b.name, (select bs.sex_name from bs_sex bs where bs.sex =b.sex) sex,
                (select pp.pers_name from bs_person_type pp where pp.pers_type = b.pers_type_detail and pp.center_id = b.center_id) as pers_type,
                to_char(b.begin_date, 'yyyy-mm-dd') begin_date,
                to_char(b.end_date, 'yyyy-mm-dd') end_date,
                to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') fin_date,
                to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') finish_date,
                b.indi_id, t.insr_code, b.fin_staff, b.fin_man, b.corp_id,
                b.idcard,
                to_char(b.reg_date, 'yyyy-mm-dd hh24:mi:ss') reg_date,
                nvl(b.in_days, 0) in_days, b.district_code, b.center_id,
                b.office_grade, b.corp_name, b.in_disease,
                (select t.disease
                    from bs_disease t
                   where t.center_id = nvl(h.catalog_center, h.center_id)
                     and b.in_disease = t.icd) as disease, b.in_area_name,
                b.in_dept_name, b.in_bed, b.bed_type, b.patient_id,
                translate(b.remark, chr(13), '') remark, b.pos_code,
                b.reimburse_flag,
                (select q.disease
                    from bs_disease q
                   where q.center_id = nvl(h.catalog_center, h.center_id)
                     and b.fin_disease = q.icd) as fin_disease, 0 as foregift,
                b.ic_no, b.treatment_type,
                (select t.apply_content
                    from mt_apply t
                   where t.serial_apply = b.serial_apply) as apply_content,
                b.reg_man, street.QYBM as area_code, T.FEES, T.PAY_MONEY_JD,
                T.FUND_MONEY, T.FUND_301, T.FUND_003,T.QFX, T.HOSP_ZF, T.CENTER_ZF, T.YW_FUND_MONEY,
                T.BC_FUND_MONEY, T.PAY_MONEY_ALLSELF, T.PAY_MONEY_SELF,
                T.PAY_MONEY_E00SELF, T.PAY_MONEY_Z00SELF, T.PAY_MONEY_S00SELF,
                T.PAY_MONEY_S01SELF, T.PAY_MONEY_S02SELF, T.PAY_MONEY_C000SELF,
                T.PAY_MONEY_C001SELF, T.PAY_MONEY_C004SELF,
                T.PAY_MONEY_C006SELF, T.PAY_MONEY_C007SELF,
                T.PAY_MONEY_C007003SELF,
                (T.DB_MONEY - t.YWSH_DB_MONEY) as DB_MONEY, t.YWSH_DB_MONEY,
                T.TC_MONEY, T.TC_MONEY_XE, t.hosp_pay, t.hosp_prise,
                T.JMTC_MONEY, T.YW_MONEY, T.JMYW_MONEY, T.ACCT_MONEY,
                T.LX_MONEY, T.SY_MONEY, T.OFFI_MONEY, T.ZHAOGU_PAY,
                T.PAY_MONEY_XZF, PAY_MONEY_DNZF, medi_pay, medi_zfy,
                b.REG_INFO, T.DB_MONEY_JUMIN as DB_MONEY_JUMIN
           from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h,
                bs_insured j,
                (select corp.CORP_ID, corp.AREA_CODE, st.QYBM
                    from bs_corp corp
                   inner join bs_country_street st
                      on corp.AREA_CODE = st.QYBM) street,
                (SELECT B.hospital_id, B.serial_no, B.indi_id,
                         (select max(insr_code)
                             from bs_insured a
                            where a.indi_id = B.indi_id) insr_code,
                         SUM(c.real_pay) AS FEES,
                         SUM(DECODE(c.POLICY_ITEM_CODE, 'C000', c.REAL_PAY, 'C001', c.REAL_PAY, 'C002', c.REAL_PAY, 'C003', c.REAL_PAY, 'C004', c.REAL_PAY, 'C005', c.REAL_PAY, 'C006', c.REAL_PAY, 'C007', c.REAL_PAY, 0)) AS PAY_MONEY_JD,
                         SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '511', c.REAL_PAY, '202', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS FUND_MONEY,
                         SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY,  0)) AS FUND_301,
                         SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY,  0)) AS FUND_003,
                         SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_FUND_MONEY,
                         SUM(DECODE(c.FUND_ID, '306', c.REAL_PAY, 0)) AS BC_FUND_MONEY,
                         SUM(DECODE(c.POLICY_ITEM_CODE, 'S01', c.REAL_PAY, 'S02', c.REAL_PAY, 0)) AS QFX,
                         SUM(DECODE(c.FUND_ID, '996', c.REAL_PAY, 0)) AS HOSP_ZF,
                         SUM(DECODE(c.FUND_ID, '998', c.REAL_PAY, 0)) AS CENTER_ZF,
                         SUM(DECODE(c.label_flag || c.FUND_ID, '101003', c.REAL_PAY, '101999', c.REAL_PAY, 0)) AS PAY_MONEY_ALLSELF,
                         SUM(DECODE(c.label_flag || c.FUND_ID, '102003', c.REAL_PAY, '102999', c.REAL_PAY, 0)) AS PAY_MONEY_SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'E00003', c.REAL_PAY, 'E00999', c.REAL_PAY, 0)) AS PAY_MONEY_E00SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'Z00003', c.REAL_PAY, 'Z00999', c.REAL_PAY, 0)) AS PAY_MONEY_Z00SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S00003', c.REAL_PAY, 'S00999', c.REAL_PAY, 0)) AS PAY_MONEY_S00SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S01003', c.REAL_PAY, 'S01999', c.REAL_PAY, 0)) AS PAY_MONEY_S01SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S02003', c.REAL_PAY, 'S02999', c.REAL_PAY, 0)) AS PAY_MONEY_S02SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C000003', c.REAL_PAY, 'C000999', c.REAL_PAY, 0)) AS PAY_MONEY_C000SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C001003', c.REAL_PAY, 'C001999', c.REAL_PAY, 'C002003', c.REAL_PAY, 'C002999', c.REAL_PAY, 'C003003', c.REAL_PAY, 'C003999', c.REAL_PAY, 0)) AS PAY_MONEY_C001SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C004003', c.REAL_PAY, 'C004999', c.REAL_PAY, 'C005003', c.REAL_PAY, 'C005999', c.REAL_PAY, 0)) AS PAY_MONEY_C004SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C006003', c.REAL_PAY, 'C006999', c.REAL_PAY, 'C007003', c.REAL_PAY, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C006SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C007SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007003', c.REAL_PAY, 0)) AS PAY_MONEY_C007003SELF,
                         SUM(DECODE(c.FUND_ID, '201', c.REAL_PAY, 0)) AS DB_MONEY,
                         0 AS YWSH_DB_MONEY,
                         SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY,
                         case
                           when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
                            nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
                           else
                            0
                         end hosp_pay,
                         case
                           when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
                            0
                           else
                            -nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
                         end hosp_prise,
                         SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY_XE,
                         SUM(DECODE(c.FUND_ID, '801', c.REAL_PAY, 0)) AS JMTC_MONEY,
                         SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_MONEY,
                         SUM(DECODE(c.FUND_ID, '802', c.REAL_PAY, 0)) AS JMYW_MONEY,
                         SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY, 0)) AS ACCT_MONEY,
                         SUM(DECODE(c.FUND_ID, '202', c.REAL_PAY, 0)) AS LX_MONEY,
                         SUM(DECODE(c.FUND_ID, '511', c.REAL_PAY, 0)) AS SY_MONEY,
                         SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY, 0)) AS OFFI_MONEY,
                         SUM(DECODE(c.FUND_ID, '401', c.REAL_PAY, 0)) AS ZHAOGU_PAY,
                         SUM(DECODE(c.FUND_ID, '999', c.REAL_PAY, 0)) AS PAY_MONEY_XZF,
                         SUM((CASE
                               WHEN C.POLICY_ITEM_CODE IN
                                    ('C000', 'C001', 'C002', 'C003', 'C004') AND
                                    (C.fund_id = '999' OR C.fund_id = '003') THEN
                                C.REAL_PAY
                               ELSE
                                0
                             END)) AS PAY_MONEY_DNZF,
                         (select nvl(nvl(sum(ss.all_self_money), 0) +
                                       nvl(sum(ss.part_self_money), 0), 0) as not_fund
                             from mt_fee_stat_fin ss
                            where ss.hospital_id = b.hospital_id
                              and ss.serial_no = b.serial_no
                              and ss.valid_flag = '1'
                              and ss.stat_type in ('001', '002', '003')) as medi_pay,
                         (select nvl(sum(ss.money), 0) as medi_zfy
                             from mt_fee_stat_fin ss
                            where ss.hospital_id = b.hospital_id
                              and ss.serial_no = b.serial_no
                              and ss.valid_flag = '1'
                              and ss.stat_type in ('001', '002', '003')) as medi_zfy,
                         SUM(DECODE(c.FUND_ID, '803', c.REAL_PAY, 0)) AS DB_MONEY_JUMIN
                    FROM MT_BIZ_FIN B, MT_PAY_RECORD_FIN C, BS_BIZTYPE G,
                         BS_INSURED J
                   WHERE B.HOSPITAL_ID = C.HOSPITAL_ID
                     AND B.SERIAL_NO = C.SERIAL_NO
                     AND B.CENTER_ID = G.CENTER_ID
                     AND B.BIZ_TYPE = G.BIZ_TYPE
                     AND B.VALID_FLAG = '1'
                     AND C.VALID_FLAG = '1'
                     and b.indi_id = j.indi_id
                     and b.hospital_id || b.serial_no in
                         (select hospital_id || serial_no
                            from pm_account_biz
                           where month_decl_sn in
                                 (select rela_decl_sn
                                    from pm_bill
                                   where outpay_bill_no = '23') and ((biz_flag = '18' and biz_type = '12') or biz_type <>'12' ))
                   GROUP BY b.hospital_id, b.serial_no, b.indi_id) T
          where a.hospital_id = b.hospital_id
            and b.center_id = g.center_id
            and b.biz_type = g.biz_type
            and b.center_id = h.center_id(+)
            and b.valid_flag = '1'
            and b.indi_id = j.indi_id
            and b.CORP_ID = street.CORP_ID(+)
            AND B.HOSPITAL_ID = T.HOSPITAL_ID
            AND B.SERIAL_NO = T.SERIAL_NO
          order by rowno) w

从上面的信息可以看到执行花费了2456844ms。从awr报告可以看到主要等待事件也是属于I/O
11

13

从sql报告来看
14

2

执行sql调整任务

SQL> @C:\oracle\product\10.2.0\client_1\rdbms\admin\sqltrpt.sql
Cannot SET TAB
Cannot SET LONGCHUNKSIZE
 
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
newl
---------

 
SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
arb763ht1q5j0 17462.1402 select hospital_id,         serial_no,         serial_f
2m613a70mtp33 14098.6959 call usp_pay_account_declare(:1,:2,:3,:4,:5,:6,:7,:8,:9
ah4mrap4wpyk3 11941.5544 INSERT INTO BS_CATALOG_MATCH (CENTER_ID, SERIAL_MATCH,
fj88hvyfjf7u3 7571.78878 insert into mt_biz_fin(  hospital_id, serial_no, fee_ba
17janqkjd2bx3 7172.00961 select hospital_id,          serial_no,          fee_ba
0skcw15cj8kg7 5772.45381 select rowno, hos_serial, reg_flag,        nvl((SELECT
691vbnfyxyvr3 5277.95397 update bs_mdi_indi_acc   set last_balance = last_balanc
83h4yucvjnyap 5228.42888 UPDATE PM_ACCOUNT_BIZ A SET A.YEAR_PAY_TYPE = A.FINAL_P
37j74mj2t6d3g 5055.72843 insert into mt_biz_scene_fin(hospital_id, serial_no, fe
dfrc1yjumpkgb 3495.65936 insert into mt_pay_record_fin(hospital_id, serial_no, f
07q1m7pybb7sn 3450.10698 insert into BS_HOSP_LOGIN_LOGFIN select * from BS_HOSP_
35ckgb9fcpbpd 3349.30750 select  nvl(sum(decode(a.total_type,'C0000',nvl(a.sum_y
1urbbnr5sc8u3  3010.5935 select a.biz_type,a.special_code,a.indi_id, a.reimburse
bp5ppsp50z4cg 2728.77444 insert into bs_icfee (serial_ic, card_no,  card_type, i
6agr1962w3wfw 2667.30935 insert into bs_biztotal_record (id,indi_id,hospital_id,
newl
---------

 
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- --------------------------------------------------------------------------------
41w2uhn9uukx2 46992.5939  BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_08',
0skcw15cj8kg7 40589.7354 select rowno, hos_serial, reg_flag,        nvl((SELECT
d2nqy2m0d6k02 25714.3942 select rowno, hos_serial, reg_flag,        nvl((SELECT
57vdxh1xps2r7 21134.4932  BEGIN    SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_03',
dbknuva8j82f6 9400.07834 DECLARE job BINARY_INTEGER := :job; next_date DATE := :
b6usrg82hwsa3 7914.57973 call dbms_stats.gather_database_stats_job_proc (  )
ga24g7vg22nh5 5493.49117 select  distinct a.busi_bill_sn,a.audit_flag,a.make_bil
5vagsa3xhxduu 5212.08508 BEGIN usp_kettle_exec_proc ( :1,  :2,  :3,  :4,  :5); E
arb763ht1q5j0 4786.13909 select hospital_id,         serial_no,         serial_f
gdbumbxg088f2 4220.72077 select zf.district_code,zf.center_id , sum(zf.zzf) as "
4c3s78cg7qgaw 3534.64534 INSERT INTO ZXJ_YLJ_QS (ND, CENTER_ID, DISTRICT_CODE, R
2m613a70mtp33 3491.63022 call usp_pay_account_declare(:1,:2,:3,:4,:5,:6,:7,:8,:9
datjs0356h3n4 3351.04771 select w.hospital_id,t.hospital_name,sum(mzrc) mzrc,sum
9su2cmt6gg3rf 3336.44954 INSERT INTO ZXJ_YLJ_QS (ND, CENTER_ID, DISTRICT_CODE, R
g2xvxhuqb3mm7 3323.54692 select tab_pay.zj_code as corp_five_no,tab_pay.corp_id,
newl
---------

 
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Sql Id specified: 0skcw15cj8kg7
 
Tune the sql
~~~~~~~~~~~~
err
---------
0
task_name
---------
TASK_39762
err
---------
0
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_39762
Tuning Task Owner                 : INSUR_CHANGDE
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 04/05/2016 16:22:22
Completed at                      : 04/05/2016 16:32:24
Number of SQL Profile Findings    : 1
 
-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID     : 0skcw15cj8kg7
SQL Text   : select rowno, hos_serial, reg_flag,
                    nvl((SELECT distinct t.policy_value
                           FROM fc_biz_policy t
                          WHERE t.policy_code = 'can_clinic_flag'
                            and t.valid_flag = '1'
                            and t.center_id = '430701'), (SELECT distinct
             t.policy_value
                             FROM fc_biz_policy t
                            WHERE t.policy_code =
                                  'can_clinic_flag'
                              and t.valid_flag = '1'
                              and t.center_id = '0')) AS can_clinic_flag,
                    decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发',
             'D', '危重', '') DISEASE_TYPE,
                    hospital_id, hospital_name, serial_no, biz_type, case_id,
             biz_stat,
                    name, sex, pers_type, begin_date, end_date, fin_date,
             finish_date,
                    reg_date, in_days, indi_id, insr_code, fin_staff,
             fin_man, corp_id,
                    idcard, district_code, office_grade,center_id,
                    (select t.special_code
                        from bs_corp_pres t
                       where t.indi_id = w.indi_id) as special_code,
             corp_name, disease,
                    in_area_name, in_dept_name,
                    decode(apply_content, '126', '转外住院', '127', '转外复查',
             '普通住院') as apply_content,
                    in_bed, bed_type, patient_id, remark, pos_code,
             reimburse_flag,
                    fin_disease, ic_no, treatment_type,
                    decode(reg_info || treatment_type, 'WD3120',
             '普通住院(转外住院)', 'WD2120', '普通住院(异地住院)', '1120', '普通住院(转诊转院)',
             'F120', '普通住院(非首诊就诊)', 'WD1120', '普通住院(异地安置)', 'C120',
             '普通住院(首诊就诊)', (select treatment_name
                                from bs_treat_type
                               where treatment_type =
                                     w.treatment_type
                                 and center_id =
                                     w.center_id)) as treatment_name,
                    nvl(pay_money_jd, 0) as pay_money_jd,
                    nvl(pay_money_xzf, 0) as pay_money_xzf, nvl(fees, 0) as
             fees,
                    foregift, nvl(pay_money_allself, 0) as pay_money_allself,
                    nvl(pay_money_self, 0) as pay_money_self,
                    nvl(pay_money_E00self, 0) as pay_money_E00self,
                    nvl(pay_money_Z00self, 0) as pay_money_Z00self,
                    nvl(pay_money_S00self, 0) as pay_money_S00self,
                    nvl(pay_money_S01self, 0) as pay_money_S01self,
                    nvl(pay_money_S01self, 0) as pay_money_S01self,
                    nvl(pay_money_C000self, 0) as pay_money_C000self,
                    nvl(pay_money_C001self, 0) as pay_money_C001self,
                    nvl(pay_money_C004self, 0) as pay_money_C004self,
                    nvl(pay_money_C006self, 0) as pay_money_C006self,
                    nvl(pay_money_C007self, 0) as pay_money_C007self,
                    nvl(pay_money_C007003self, 0) as pay_money_C007003self,
                    nvl(tc_money, 0) as tc_money, nvl(tc_money_xe, 0) as
             tc_money_xe,
                    nvl(hosp_pay, 0) as hosp_pay, nvl(hosp_prise, 0) as
             hosp_prise,
                    nvl(jmtc_money, 0) as jmtc_money, nvl(yw_money, 0) as
             yw_money,
                    nvl(jmyw_money, 0) as jmyw_money, nvl(acct_money, 0) as
             acct_money,
                    nvl(lx_money, 0) as lx_money, nvl(sy_money, 0) as
             sy_money,
                    nvl(fund_money, 0) as fund_money, nvl(fund_301,0)
             fund_301 ,nvl(fund_003,0) fund_003 ,nvl(qfx, 0) as qfx,
                    nvl(hosp_zf, 0) as hosp_zf, nvl(center_zf, 0) as
             center_zf,
                    nvl(yw_fund_money, 0) as yw_fund_money,
                    nvl(bc_fund_money, 0) as bc_fund_money, nvl(db_money, 0)
             as db_money,
                    nvl(YWSH_DB_MONEY, 0) as YWSH_DB_MONEY,
                    nvl(offi_money, 0) as offi_money, reg_man,
                    nvl(zhaogu_pay, 0) as zhaogu_pay, w.area_code,
             PAY_MONEY_DNZF,
                    medi_pay, medi_zfy, nvl(DB_MONEY_JUMIN, 0) as
             db_money_jumin
               from (select rownum rowno, b.hos_serial, b.reg_flag,
             b.disease_type,
                             a.hospital_id, a.hospital_name, b.serial_no,
             b.biz_type,
                             b.case_id, g.biz_stat, b.name, (select
             bs.sex_name from bs_sex bs where bs.sex =b.sex) sex,
                             (select pp.pers_name from bs_person_type pp
             where pp.pers_type = b.pers_type_detail and pp.center_id =
             b.center_id) as pers_type,
                             to_char(b.begin_date, 'yyyy-mm-dd') begin_date,
                             to_char(b.end_date, 'yyyy-mm-dd') end_date,
                             to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss')
             fin_date,
                             to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss')
             finish_date,
                             b.indi_id, t.insr_code, b.fin_staff, b.fin_man,
             b.corp_id,
                             b.idcard,
                             to_char(b.reg_date, 'yyyy-mm-dd hh24:mi:ss')
             reg_date,
                             nvl(b.in_days, 0) in_days, b.district_code,
             b.center_id,
                             b.office_grade, b.corp_name, b.in_disease,
                             (select t.disease
                                 from bs_disease t
                                where t.center_id = nvl(h.catalog_center,
             h.center_id)
                                  and b.in_disease = t.icd) as disease,
             b.in_area_name,
                             b.in_dept_name, b.in_bed, b.bed_type,
             b.patient_id,
                             translate(b.remark, chr(13), '') remark,
             b.pos_code,
                             b.reimburse_flag,
                             (select q.disease
                                 from bs_disease q
                                where q.center_id = nvl(h.catalog_center,
             h.center_id)
                                  and b.fin_disease = q.icd) as fin_disease,
             0 as foregift,
                             b.ic_no, b.treatment_type,
                             (select t.apply_content
                                 from mt_apply t
                                where t.serial_apply = b.serial_apply) as
             apply_content,
                             b.reg_man, street.QYBM as area_code, T.FEES,
             T.PAY_MONEY_JD,
                             T.FUND_MONEY, T.FUND_301, T.FUND_003,T.QFX,
             T.HOSP_ZF, T.CENTER_ZF, T.YW_FUND_MONEY,
                             T.BC_FUND_MONEY, T.PAY_MONEY_ALLSELF,
             T.PAY_MONEY_SELF,
                             T.PAY_MONEY_E00SELF, T.PAY_MONEY_Z00SELF,
             T.PAY_MONEY_S00SELF,
                             T.PAY_MONEY_S01SELF, T.PAY_MONEY_S02SELF,
             T.PAY_MONEY_C000SELF,
                             T.PAY_MONEY_C001SELF, T.PAY_MONEY_C004SELF,
                             T.PAY_MONEY_C006SELF, T.PAY_MONEY_C007SELF,
                             T.PAY_MONEY_C007003SELF,
                             (T.DB_MONEY - t.YWSH_DB_MONEY) as DB_MONEY,
             t.YWSH_DB_MONEY,
                             T.TC_MONEY, T.TC_MONEY_XE, t.hosp_pay,
             t.hosp_prise,
                             T.JMTC_MONEY, T.YW_MONEY, T.JMYW_MONEY,
             T.ACCT_MONEY,
                             T.LX_MONEY, T.SY_MONEY, T.OFFI_MONEY,
             T.ZHAOGU_PAY,
                             T.PAY_MONEY_XZF, PAY_MONEY_DNZF, medi_pay,
             medi_zfy,
                             b.REG_INFO, T.DB_MONEY_JUMIN as DB_MONEY_JUMIN
                        from bs_hospital a, bs_biztype g, mt_biz_fin b,
             bs_center h,
                             bs_insured j,
                             (select corp.CORP_ID, corp.AREA_CODE, st.QYBM
                                 from bs_corp corp
                                inner join bs_country_street st
                                   on corp.AREA_CODE = st.QYBM) street,
                             (SELECT B.hospital_id, B.serial_no, B.indi_id,
                                      (select max(insr_code)
                                          from bs_insured a
                                         where a.indi_id = B.indi_id)
             insr_code,
                                      SUM(c.real_pay) AS FEES,
                                      SUM(DECODE(c.POLICY_ITEM_CODE, 'C000',
             c.REAL_PAY, 'C001', c.REAL_PAY, 'C002', c.REAL_PAY, 'C003',
             c.REAL_PAY, 'C004', c.REAL_PAY, 'C005', c.REAL_PAY, 'C006',
             c.REAL_PAY, 'C007', c.REAL_PAY, 0)) AS PAY_MONEY_JD,
                                      SUM(DECODE(c.FUND_ID, '001',
             c.REAL_PAY, '511', c.REAL_PAY, '202', c.REAL_PAY, '801',
             c.REAL_PAY, 0)) AS FUND_MONEY,
                                      SUM(DECODE(c.FUND_ID, '301',
             c.REAL_PAY,  0)) AS FUND_301,
                                      SUM(DECODE(c.FUND_ID, '003',
             c.REAL_PAY,  0)) AS FUND_003,
                                      SUM(DECODE(c.FUND_ID, '901',
             c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_FUND_MONEY,
                                      SUM(DECODE(c.FUND_ID, '306',
             c.REAL_PAY, 0)) AS BC_FUND_MONEY,
                                      SUM(DECODE(c.POLICY_ITEM_CODE, 'S01',
             c.REAL_PAY, 'S02', c.REAL_PAY, 0)) AS QFX,
                                      SUM(DECODE(c.FUND_ID, '996',
             c.REAL_PAY, 0)) AS HOSP_ZF,
                                      SUM(DECODE(c.FUND_ID, '998',
             c.REAL_PAY, 0)) AS CENTER_ZF,
                                      SUM(DECODE(c.label_flag || c.FUND_ID,
             '101003', c.REAL_PAY, '101999', c.REAL_PAY, 0)) AS
             PAY_MONEY_ALLSELF,
                                      SUM(DECODE(c.label_flag || c.FUND_ID,
             '102003', c.REAL_PAY, '102999', c.REAL_PAY, 0)) AS
             PAY_MONEY_SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'E00003', c.REAL_PAY, 'E00999', c.REAL_PAY, 0)) AS
             PAY_MONEY_E00SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'Z00003', c.REAL_PAY, 'Z00999', c.REAL_PAY, 0)) AS
             PAY_MONEY_Z00SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'S00003', c.REAL_PAY, 'S00999', c.REAL_PAY, 0)) AS
             PAY_MONEY_S00SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'S01003', c.REAL_PAY, 'S01999', c.REAL_PAY, 0)) AS
             PAY_MONEY_S01SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'S02003', c.REAL_PAY, 'S02999', c.REAL_PAY, 0)) AS
             PAY_MONEY_S02SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'C000003', c.REAL_PAY, 'C000999', c.REAL_PAY, 0)) AS
             PAY_MONEY_C000SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'C001003', c.REAL_PAY, 'C001999', c.REAL_PAY,
             'C002003', c.REAL_PAY, 'C002999', c.REAL_PAY, 'C003003',
             c.REAL_PAY, 'C003999', c.REAL_PAY, 0)) AS PAY_MONEY_C001SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'C004003', c.REAL_PAY, 'C004999', c.REAL_PAY,
             'C005003', c.REAL_PAY, 'C005999', c.REAL_PAY, 0)) AS
             PAY_MONEY_C004SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'C006003', c.REAL_PAY, 'C006999', c.REAL_PAY,
             'C007003', c.REAL_PAY, 'C007999', c.REAL_PAY, 0)) AS
             PAY_MONEY_C006SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C007SELF,
                                      SUM(DECODE(c.policy_item_code ||
             c.FUND_ID, 'C007003', c.REAL_PAY, 0)) AS PAY_MONEY_C007003SELF,
                                      SUM(DECODE(c.FUND_ID, '201',
             c.REAL_PAY, 0)) AS DB_MONEY,
                                      0 AS YWSH_DB_MONEY,
                                      SUM(DECODE(c.FUND_ID, '001',
             c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY,
                                      case
                                        when nvl(sum(decode(c.fund_id, '996',
             nvl(c.real_pay, 0), 0)), 0) >= 0 then
                                         nvl(sum(decode(c.fund_id, '996',
             nvl(c.real_pay, 0), 0)), 0)
                                        else
                                         0
                                      end hosp_pay,
                                      case
                                        when nvl(sum(decode(c.fund_id, '996',
             nvl(c.real_pay, 0), 0)), 0) >= 0 then
                                         0
                                        else
                                         -nvl(sum(decode(c.fund_id, '996',
             nvl(c.real_pay, 0), 0)), 0)
                                      end hosp_prise,
                                      SUM(DECODE(c.FUND_ID, '001',
             c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY_XE,
                                      SUM(DECODE(c.FUND_ID, '801',
             c.REAL_PAY, 0)) AS JMTC_MONEY,
                                      SUM(DECODE(c.FUND_ID, '901',
             c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_MONEY,
                                      SUM(DECODE(c.FUND_ID, '802',
             c.REAL_PAY, 0)) AS JMYW_MONEY,
                                      SUM(DECODE(c.FUND_ID, '003',
             c.REAL_PAY, 0)) AS ACCT_MONEY,
                                      SUM(DECODE(c.FUND_ID, '202',
             c.REAL_PAY, 0)) AS LX_MONEY,
                                      SUM(DECODE(c.FUND_ID, '511',
             c.REAL_PAY, 0)) AS SY_MONEY,
                                      SUM(DECODE(c.FUND_ID, '301',
             c.REAL_PAY, 0)) AS OFFI_MONEY,
                                      SUM(DECODE(c.FUND_ID, '401',
             c.REAL_PAY, 0)) AS ZHAOGU_PAY,
                                      SUM(DECODE(c.FUND_ID, '999',
             c.REAL_PAY, 0)) AS PAY_MONEY_XZF,
                                      SUM((CASE
                                            WHEN C.POLICY_ITEM_CODE IN
                                                 ('C000', 'C001', 'C002',
             'C003', 'C004') AND
                                                 (C.fund_id = '999' OR
             C.fund_id = '003') THEN
                                             C.REAL_PAY
                                            ELSE
                                             0
                                          END)) AS PAY_MONEY_DNZF,
                                      (select nvl(nvl(sum(ss.all_self_money),
             0) +
 
             nvl(sum(ss.part_self_money), 0), 0) as not_fund
                                          from mt_fee_stat_fin ss
                                         where ss.hospital_id = b.hospital_id
                                           and ss.serial_no = b.serial_no
                                           and ss.valid_flag = '1'
                                           and ss.stat_type in ('001', '002',
             '003')) as medi_pay,
                                      (select nvl(sum(ss.money), 0) as
             medi_zfy
                                          from mt_fee_stat_fin ss
                                         where ss.hospital_id = b.hospital_id
                                           and ss.serial_no = b.serial_no
                                           and ss.valid_flag = '1'
                                           and ss.stat_type in ('001', '002',
             '003')) as medi_zfy,
                                      SUM(DECODE(c.FUND_ID, '803',
             c.REAL_PAY, 0)) AS DB_MONEY_JUMIN
                                 FROM MT_BIZ_FIN B, MT_PAY_RECORD_FIN C,
             BS_BIZTYPE G,
                                      BS_INSURED J
                                WHERE B.HOSPITAL_ID = C.HOSPITAL_ID
                                  AND B.SERIAL_NO = C.SERIAL_NO
                                  AND B.CENTER_ID = G.CENTER_ID
                                  AND B.BIZ_TYPE = G.BIZ_TYPE
                                  AND B.VALID_FLAG = '1'
                                  AND C.VALID_FLAG = '1'
                                  and b.indi_id = j.indi_id
                                  and b.hospital_id || b.serial_no in
                                      (select hospital_id || serial_no
                                         from pm_account_biz
                                        where month_decl_sn in
                                              (select rela_decl_sn
                                                 from pm_bill
                                                where outpay_bill_no = '23')
             and ((biz_flag = '18' and biz_type = '12') or biz_type <>'12' ))
                                GROUP BY b.hospital_id, b.serial_no,
             b.indi_id) T
                       where a.hospital_id = b.hospital_id
                         and b.center_id = g.center_id
                         and b.biz_type = g.biz_type
                         and b.center_id = h.center_id(+)
                         and b.valid_flag = '1'
                         and b.indi_id = j.indi_id
                         and b.CORP_ID = street.CORP_ID(+)
                         AND B.HOSPITAL_ID = T.HOSPITAL_ID
                         AND B.SERIAL_NO = T.SERIAL_NO
                       order by rowno) w
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
 
  Recommendation (estimated benefit: 99.99%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_39762',
            replace => TRUE);
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

调整前的执行计划

1- Original With Adjusted Cost
------------------------------
Plan hash value: 462538689
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                          |   648G|   923T|       |    18G  (2)|999:59:59 |
|   1 |  HASH UNIQUE                               |                          |     1 |    31 |       |     2  (50)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID              | FC_BIZ_POLICY            |     1 |    31 |       |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                        | PK_FC_BIZ_POLICY         |     1 |       |       |     1   (0)| 00:00:01 |
|   4 |    HASH UNIQUE                             |                          |     1 |    31 |       |     2  (50)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID            | FC_BIZ_POLICY            |     1 |    31 |       |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                      | PK_FC_BIZ_POLICY         |     1 |       |       |     1   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY INDEX ROWID               | BS_SEX                   |     1 |     6 |       |     1   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN                        | PK_BS_SEX                |     1 |       |       |     1   (0)| 00:00:01 |
|   9 |  TABLE ACCESS BY INDEX ROWID               | BS_PERSON_TYPE           |     1 |    18 |       |     1   (0)| 00:00:01 |
|* 10 |   INDEX UNIQUE SCAN                        | PK_BS_PERSON_TYPE        |     1 |       |       |     1   (0)| 00:00:01 |
|  11 |  SORT AGGREGATE                            |                          |     1 |    18 |       |            |          |
|  12 |   TABLE ACCESS BY INDEX ROWID              | BS_INSURED               |     1 |    18 |       |     1   (0)| 00:00:01 |
|* 13 |    INDEX UNIQUE SCAN                       | PK_BS_INSURED            |     1 |       |       |     1   (0)| 00:00:01 |
|  14 |  TABLE ACCESS BY INDEX ROWID               | BS_CORP_PRES             |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 15 |   INDEX RANGE SCAN                         | INDEX_BS_CORP_PRES_INDI  |     1 |       |       |     1   (0)| 00:00:01 |
|  16 |  TABLE ACCESS BY INDEX ROWID               | BS_DISEASE               |     1 |    33 |       |     1   (0)| 00:00:01 |
|* 17 |   INDEX RANGE SCAN                         | INX_BS_DISEASE_01        |     1 |       |       |     1   (0)| 00:00:01 |
|  18 |  TABLE ACCESS BY INDEX ROWID               | MT_APPLY                 |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 19 |   INDEX UNIQUE SCAN                        | PK_MT_APPLY              |     1 |       |       |     1   (0)| 00:00:01 |
|  20 |  TABLE ACCESS BY INDEX ROWID               | BS_DISEASE               |     1 |    33 |       |     1   (0)| 00:00:01 |
|* 21 |   INDEX RANGE SCAN                         | INX_BS_DISEASE_01        |     1 |       |       |     1   (0)| 00:00:01 |
|  22 |  TABLE ACCESS BY INDEX ROWID               | BS_TREAT_TYPE            |     1 |    23 |       |     1   (0)| 00:00:01 |
|* 23 |   INDEX UNIQUE SCAN                        | PK_BS_TREAT_TYPE         |     1 |       |       |     1   (0)| 00:00:01 |
|  24 |  SORT AGGREGATE                            |                          |     1 |    34 |       |            |          |
|* 25 |   TABLE ACCESS BY INDEX ROWID              | MT_FEE_STAT_FIN          |     1 |    34 |       |     1   (0)| 00:00:01 |
|* 26 |    INDEX RANGE SCAN                        | PK_MT_FEE_STAT_FIN       |     1 |       |       |     1   (0)| 00:00:01 |
|  27 |  SORT AGGREGATE                            |                          |     1 |    31 |       |            |          |
|* 28 |   TABLE ACCESS BY INDEX ROWID              | MT_FEE_STAT_FIN          |     1 |    31 |       |     1   (0)| 00:00:01 |
|* 29 |    INDEX RANGE SCAN                        | PK_MT_FEE_STAT_FIN       |     1 |       |       |     1   (0)| 00:00:01 |
|  30 |  VIEW                                      |                          |   648G|   923T|       |    18G  (2)|999:59:59 |
|  31 |   SORT ORDER BY                            |                          |   648G|   516T|  1072T|    18G  (2)|999:59:59 |
|  32 |    COUNT                                   |                          |       |       |       |            |          |
|* 33 |     HASH JOIN RIGHT OUTER                  |                          |   648G|   516T|       |  9061M  (2)|999:59:59 |
|  34 |      VIEW                                  |                          |     1 |    12 |       |   117   (2)| 00:00:02 |
|* 35 |       HASH JOIN                            |                          |     1 |    18 |       |   117   (2)| 00:00:02 |
|  36 |        TABLE ACCESS FULL                   | BS_COUNTRY_STREET        |     1 |     7 |       |     2   (0)| 00:00:01 |
|* 37 |        TABLE ACCESS FULL                   | BS_CORP                  |  9523 |   102K|       |   114   (1)| 00:00:02 |
|* 38 |      HASH JOIN                             |                          |   648G|   509T|       |  9058M  (2)|999:59:59 |
|  39 |       TABLE ACCESS FULL                    | BS_BIZTYPE               |    97 |  1164 |       |     3   (0)| 00:00:01 |
|* 40 |       HASH JOIN RIGHT OUTER                |                          |   648G|   502T|       |  9054M  (2)|999:59:59 |
|  41 |        TABLE ACCESS FULL                   | BS_CENTER                |    12 |   168 |       |     3   (0)| 00:00:01 |
|  42 |        MERGE JOIN                          |                          |   648G|   493T|       |  9051M  (2)|999:59:59 |
|  43 |         NESTED LOOPS                       |                          |   809G|   592T|       |  9051M  (2)|999:59:59 |
|  44 |          MERGE JOIN                        |                          |   808G|   587T|       |  9042M  (2)|999:59:59 |
|  45 |           SORT JOIN                        |                          |   819G|   427T|       |  9040M  (2)|999:59:59 |
|  46 |            VIEW                            |                          |   819G|   427T|       |  9040M  (2)|999:59:59 |
|  47 |             HASH GROUP BY                  |                          |   819G|    93T|   200T|  9040M  (2)|999:59:59 |
|* 48 |              HASH JOIN                     |                          |   819G|    93T|   114M|  5209K (86)| 17:21:55 |
|  49 |               VIEW                         | VW_NSO_1                 |  3750K|    71M|       | 41283   (1)| 00:08:16 |
|  50 |                HASH UNIQUE                 |                          |  3750K|   146M|   404M| 41283   (1)| 00:08:16 |
|* 51 |                 TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ           |   205 |  6560 |       |     9   (0)| 00:00:01 |
|  52 |                  NESTED LOOPS              |                          |  3750K|   146M|       |  1405   (2)| 00:00:17 |
|* 53 |                   TABLE ACCESS FULL        | PM_BILL                  |    31 |   279 |       |  1119   (2)| 00:00:14 |
|* 54 |                   INDEX RANGE SCAN         | IDX_PM_ACCOUNT_BIZ_MONTH |   213 |       |       |     1   (0)| 00:00:01 |
|  55 |               NESTED LOOPS                 |                          |    21M|  2208M|       |   598K  (2)| 01:59:45 |
|* 56 |                HASH JOIN                   |                          |    21M|  2080M|       |   598K  (1)| 01:59:42 |
|  57 |                 INDEX FULL SCAN            | PK_BS_BIZTYPE            |    97 |   970 |       |     1   (0)| 00:00:01 |
|* 58 |                 HASH JOIN                  |                          |    21M|  1872M|  1089M|   598K  (1)| 01:59:40 |
|* 59 |                  TABLE ACCESS FULL         | MT_BIZ_FIN               |    21M|   838M|       |   209K  (2)| 00:41:54 |
|* 60 |                  TABLE ACCESS FULL         | MT_PAY_RECORD_FIN        |    62M|  3003M|       |   149K  (2)| 00:29:52 |
|* 61 |                INDEX UNIQUE SCAN           | PK_BS_INSURED            |     1 |     6 |       |     1   (0)| 00:00:01 |
|* 62 |           SORT JOIN                        |                          |    21M|  4715M|    12G|  1282K  (1)| 04:16:26 |
|* 63 |            TABLE ACCESS FULL               | MT_BIZ_FIN               |    21M|  4715M|       |   209K  (2)| 00:41:57 |
|* 64 |          INDEX UNIQUE SCAN                 | PK_BS_INSURED            |     1 |     6 |       |     1   (0)| 00:00:01 |
|* 65 |         SORT JOIN                          |                          |  1325 | 43725 |       |     3  (34)| 00:00:01 |
|  66 |          INDEX FULL SCAN                   | IDX_BS_HOSPITAL_NAME     |  1325 | 43725 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("T"."VALID_FLAG"='1')
   3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
       filter("T"."CENTER_ID"='430701')
   5 - filter("T"."VALID_FLAG"='1')
   6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
       filter("T"."CENTER_ID"='0')
   8 - access("BS"."SEX"=TO_NUMBER(:B1))
  10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
  13 - access("A"."INDI_ID"=:B1)
  15 - access("T"."INDI_ID"=:B1)
  17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
  19 - access("T"."SERIAL_APPLY"=:B1)
  21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
  23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
  25 - filter("SS"."VALID_FLAG"='1')
  26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
  28 - filter("SS"."VALID_FLAG"='1')
  29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
  33 - access("B"."CORP_ID"="STREET"."CORP_ID"(+))
  35 - access("CORP"."AREA_CODE"="ST"."QYBM")
  37 - filter("CORP"."AREA_CODE" IS NOT NULL)
  38 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  40 - access("B"."CENTER_ID"="H"."CENTER_ID"(+))
  48 - access("$nso_col_1"="B"."HOSPITAL_ID"||"B"."SERIAL_NO")
  51 - filter("BIZ_TYPE"<>'12' OR "BIZ_FLAG"=18 AND "BIZ_TYPE"='12')
  53 - filter("OUTPAY_BILL_NO"=23)
  54 - access("MONTH_DECL_SN"="RELA_DECL_SN")
  56 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  58 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
  59 - filter("B"."VALID_FLAG"='1')
  60 - filter("C"."VALID_FLAG"='1')
  61 - access("B"."INDI_ID"="J"."INDI_ID")
  62 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
       filter("B"."SERIAL_NO"="T"."SERIAL_NO" AND "B"."HOSPITAL_ID"="T"."HOSPITAL_ID")
  63 - filter("B"."VALID_FLAG"='1')
  64 - access("B"."INDI_ID"="J"."INDI_ID")
  65 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
       filter("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")

使用调整任务生成的sql profile优化后的执行计划

2- Using SQL Profile
--------------------
Plan hash value: 3772053484
 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                          | 20043 |    29M|       |   309K  (1)| 01:01:52 |
|   1 |  HASH UNIQUE                                  |                          |     1 |    31 |       |     2  (50)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID                 | FC_BIZ_POLICY            |     1 |    31 |       |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                           | PK_FC_BIZ_POLICY         |     1 |       |       |     1   (0)| 00:00:01 |
|   4 |    HASH UNIQUE                                |                          |     1 |    31 |       |     2  (50)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID               | FC_BIZ_POLICY            |     1 |    31 |       |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                         | PK_FC_BIZ_POLICY         |     1 |       |       |     1   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY INDEX ROWID                  | BS_SEX                   |     1 |     6 |       |     1   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN                           | PK_BS_SEX                |     1 |       |       |     1   (0)| 00:00:01 |
|   9 |  TABLE ACCESS BY INDEX ROWID                  | BS_PERSON_TYPE           |     1 |    18 |       |     1   (0)| 00:00:01 |
|* 10 |   INDEX UNIQUE SCAN                           | PK_BS_PERSON_TYPE        |     1 |       |       |     1   (0)| 00:00:01 |
|  11 |  SORT AGGREGATE                               |                          |     1 |    18 |       |            |          |
|  12 |   TABLE ACCESS BY INDEX ROWID                 | BS_INSURED               |     1 |    18 |       |     1   (0)| 00:00:01 |
|* 13 |    INDEX UNIQUE SCAN                          | PK_BS_INSURED            |     1 |       |       |     1   (0)| 00:00:01 |
|  14 |  TABLE ACCESS BY INDEX ROWID                  | BS_CORP_PRES             |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 15 |   INDEX RANGE SCAN                            | INDEX_BS_CORP_PRES_INDI  |     1 |       |       |     1   (0)| 00:00:01 |
|  16 |  TABLE ACCESS BY INDEX ROWID                  | BS_DISEASE               |     1 |    33 |       |     1   (0)| 00:00:01 |
|* 17 |   INDEX RANGE SCAN                            | INX_BS_DISEASE_01        |     1 |       |       |     1   (0)| 00:00:01 |
|  18 |  TABLE ACCESS BY INDEX ROWID                  | MT_APPLY                 |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 19 |   INDEX UNIQUE SCAN                           | PK_MT_APPLY              |     1 |       |       |     1   (0)| 00:00:01 |
|  20 |  TABLE ACCESS BY INDEX ROWID                  | BS_DISEASE               |     1 |    33 |       |     1   (0)| 00:00:01 |
|* 21 |   INDEX RANGE SCAN                            | INX_BS_DISEASE_01        |     1 |       |       |     1   (0)| 00:00:01 |
|  22 |  TABLE ACCESS BY INDEX ROWID                  | BS_TREAT_TYPE            |     1 |    23 |       |     1   (0)| 00:00:01 |
|* 23 |   INDEX UNIQUE SCAN                           | PK_BS_TREAT_TYPE         |     1 |       |       |     1   (0)| 00:00:01 |
|  24 |  SORT AGGREGATE                               |                          |     1 |    34 |       |            |          |
|* 25 |   TABLE ACCESS BY INDEX ROWID                 | MT_FEE_STAT_FIN          |     1 |    34 |       |     1   (0)| 00:00:01 |
|* 26 |    INDEX RANGE SCAN                           | PK_MT_FEE_STAT_FIN       |     1 |       |       |     1   (0)| 00:00:01 |
|  27 |  SORT AGGREGATE                               |                          |     1 |    31 |       |            |          |
|* 28 |   TABLE ACCESS BY INDEX ROWID                 | MT_FEE_STAT_FIN          |     1 |    31 |       |     1   (0)| 00:00:01 |
|* 29 |    INDEX RANGE SCAN                           | PK_MT_FEE_STAT_FIN       |     1 |       |       |     1   (0)| 00:00:01 |
|  30 |  VIEW                                         |                          | 20043 |    29M|       |   309K  (1)| 01:01:52 |
|  31 |   SORT ORDER BY                               |                          | 20043 |    16M|    34M|   309K  (1)| 01:01:52 |
|  32 |    COUNT                                      |                          |       |       |       |            |          |
|  33 |     NESTED LOOPS                              |                          | 20043 |    16M|       |   305K  (1)| 01:01:08 |
|* 34 |      HASH JOIN                                |                          | 20011 |    16M|       |   305K  (1)| 01:01:07 |
|  35 |       TABLE ACCESS FULL                       | BS_BIZTYPE               |    97 |  1164 |       |     3   (0)| 00:00:01 |
|* 36 |       HASH JOIN RIGHT OUTER                   |                          | 20011 |    16M|       |   305K  (1)| 01:01:07 |
|  37 |        TABLE ACCESS FULL                      | BS_CENTER                |    12 |   168 |       |     3   (0)| 00:00:01 |
|* 38 |        HASH JOIN RIGHT OUTER                  |                          | 20011 |    16M|       |   305K  (1)| 01:01:07 |
|  39 |         VIEW                                  |                          |     1 |    12 |       |   117   (2)| 00:00:02 |
|* 40 |          HASH JOIN                            |                          |     1 |    18 |       |   117   (2)| 00:00:02 |
|  41 |           TABLE ACCESS FULL                   | BS_COUNTRY_STREET        |     1 |     7 |       |     2   (0)| 00:00:01 |
|* 42 |           TABLE ACCESS FULL                   | BS_CORP                  |  9523 |   102K|       |   114   (1)| 00:00:02 |
|* 43 |         HASH JOIN                             |                          | 20011 |    15M|       |   305K  (1)| 01:01:06 |
|  44 |          INDEX FULL SCAN                      | IDX_BS_HOSPITAL_NAME     |  1325 | 43725 |       |     2   (0)| 00:00:01 |
|* 45 |          TABLE ACCESS BY INDEX ROWID          | MT_BIZ_FIN               |     1 |   225 |       |     1   (0)| 00:00:01 |
|  46 |           NESTED LOOPS                        |                          | 25021 |    19M|       |   305K  (1)| 01:01:06 |
|  47 |            VIEW                               |                          | 25349 |    13M|       |   295K  (1)| 00:59:04 |
|  48 |             HASH GROUP BY                     |                          | 25349 |  1460K|       |   295K  (1)| 00:59:04 |
|  49 |              VIEW                             |                          | 25349 |  1460K|       |   295K  (1)| 00:59:04 |
|  50 |               HASH UNIQUE                     |                          | 25349 |  4876K|    10M|   295K  (1)| 00:59:04 |
|  51 |                NESTED LOOPS                   |                          | 25349 |  4876K|       |   294K  (1)| 00:58:51 |
|  52 |                 NESTED LOOPS                  |                          | 25309 |  4424K|       |   294K  (1)| 00:58:51 |
|  53 |                  NESTED LOOPS                 |                          | 25309 |  3880K|       |   294K  (1)| 00:58:51 |
|* 54 |                   HASH JOIN                   |                          | 25502 |  2365K|   189M|   289K  (1)| 00:57:50 |
|* 55 |                    TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ           |   205 |  6560 |       |     9   (0)| 00:00:01 |
|  56 |                     NESTED LOOPS              |                          |  3750K|   146M|       |  1405   (2)| 00:00:17 |
|* 57 |                      TABLE ACCESS FULL        | PM_BILL                  |    31 |   279 |       |  1119   (2)| 00:00:14 |
|* 58 |                      INDEX RANGE SCAN         | IDX_PM_ACCOUNT_BIZ_MONTH |   213 |       |       |     1   (0)| 00:00:01 |
|* 59 |                    TABLE ACCESS FULL          | MT_BIZ_FIN               |    21M|  1131M|       |   209K  (2)| 00:41:54 |
|* 60 |                   TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN        |     1 |    62 |       |     1   (0)| 00:00:01 |
|* 61 |                    INDEX RANGE SCAN           | IDX$$_429C0002           |     1 |       |       |     1   (0)| 00:00:01 |
|* 62 |                  INDEX UNIQUE SCAN            | PK_BS_BIZTYPE            |     1 |    22 |       |     1   (0)| 00:00:01 |
|* 63 |                 INDEX UNIQUE SCAN             | PK_BS_INSURED            |     1 |    18 |       |     1   (0)| 00:00:01 |
|* 64 |            INDEX RANGE SCAN                   | PK_MT_BIZ_FIN            |     1 |       |       |     1   (0)| 00:00:01 |
|* 65 |      INDEX UNIQUE SCAN                        | PK_BS_INSURED            |     1 |     6 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("T"."VALID_FLAG"='1')
   3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
       filter("T"."CENTER_ID"='430701')
   5 - filter("T"."VALID_FLAG"='1')
   6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
       filter("T"."CENTER_ID"='0')
   8 - access("BS"."SEX"=TO_NUMBER(:B1))
  10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
  13 - access("A"."INDI_ID"=:B1)
  15 - access("T"."INDI_ID"=:B1)
  17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
  19 - access("T"."SERIAL_APPLY"=:B1)
  21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
  23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
  25 - filter("SS"."VALID_FLAG"='1')
  26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
  28 - filter("SS"."VALID_FLAG"='1')
  29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003')
  34 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  36 - access("B"."CENTER_ID"="H"."CENTER_ID"(+))
  38 - access("B"."CORP_ID"="STREET"."CORP_ID"(+))
  40 - access("CORP"."AREA_CODE"="ST"."QYBM")
  42 - filter("CORP"."AREA_CODE" IS NOT NULL)
  43 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
  45 - filter("B"."VALID_FLAG"='1')
  54 - access("B"."HOSPITAL_ID"||"B"."SERIAL_NO"="HOSPITAL_ID"||"SERIAL_NO")
  55 - filter("BIZ_TYPE"<>'12' OR "BIZ_FLAG"=18 AND "BIZ_TYPE"='12')
  57 - filter("OUTPAY_BILL_NO"=23)
  58 - access("MONTH_DECL_SN"="RELA_DECL_SN")
  59 - filter("B"."VALID_FLAG"='1')
  60 - filter("C"."VALID_FLAG"='1')
  61 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
  62 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  63 - access("B"."INDI_ID"="J"."INDI_ID")
  64 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
  65 - access("B"."INDI_ID"="J"."INDI_ID")
 
-------------------------------------------------------------------------------
 
task_name
---------
TASK_39762
err
---------
0
err
---------
0

接受调整优化生成的sql profile

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_39762',replace => TRUE);
 
PL/SQL procedure successfully completed

SQL> select * from dba_sql_profiles where sql_text like 'select rowno, hos_serial, reg_flag,%';
 
NAME                           CATEGORY    SIGNATURE SQL_TEXT                              CREATED     LAST_MODIFIED DESCRIPTION   TYPE      STATUS   FORCE_MATCHING
------------------------------ ---------- ---------- ------------------------------------- ----------- ------------- ------------- --------- -------- --------------
SYS_SQLPROF_0154fb8617518000   DEFAULT    9.22119799 select rowno, hos_serial, reg_flag,   2016/4/5 16: 2016/4/5 16:31               MANUAL    ENABLED  NO
             

重新执行该SQL需要几分钟才能执行完成
4
查看其执行计划

SQL> set long 900
SQL> set linesize 900
SQL> select * from table(dbms_xplan.display_cursor('010tbjyy9cztf',null,'ALL ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  010tbjyy9cztf, child number 0
-------------------------------------
select rowno, hos_serial, reg_flag,        nvl((SELECT distinct t.policy_value               FROM fc_biz_policy t              WHERE t.policy_code =
'can_clinic_flag'                and t.valid_flag = '1'                and t.center_id = '430701'), (SELECT distinct t.policy_value
FROM fc_biz_policy t                WHERE t.policy_code =                      'can_clinic_flag'                  and t.valid_flag = '1'
    and t.center_id = '0')) AS can_clinic_flag,        decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D', '危重', '') DISEASE_TYPE,
hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat,        name, sex, pers_type, begin_date, end_date, fin_date, finish_date,
reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id,        idcard, district_code, office_grade,center_id,        (select
t.special_code            from bs_corp_pres t           where t.indi_id = w.indi_id) as special_code, corp

Plan hash value: 3772053484



--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                     | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH UNIQUE                                  |                          |      1 |    31 |       |     2  (50)| 00:00:01 |  1307K|  1307K|          |
|*  2 |   TABLE ACCESS BY INDEX ROWID                 | FC_BIZ_POLICY            |      1 |    31 |       |     1   (0)| 00:00:01 |       |       |          |
|*  3 |    INDEX RANGE SCAN                           | PK_FC_BIZ_POLICY         |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|   4 |    HASH UNIQUE                                |                          |      1 |    31 |       |     2  (50)| 00:00:01 |  1307K|  1307K|          |
|*  5 |     TABLE ACCESS BY INDEX ROWID               | FC_BIZ_POLICY            |      1 |    31 |       |     1   (0)| 00:00:01 |       |       |          |
|*  6 |      INDEX RANGE SCAN                         | PK_FC_BIZ_POLICY         |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|   7 |  TABLE ACCESS BY INDEX ROWID                  | BS_SEX                   |      1 |     6 |       |     1   (0)| 00:00:01 |       |       |          |
|*  8 |   INDEX UNIQUE SCAN                           | PK_BS_SEX                |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|   9 |  TABLE ACCESS BY INDEX ROWID                  | BS_PERSON_TYPE           |      1 |    18 |       |     1   (0)| 00:00:01 |       |       |          |
|* 10 |   INDEX UNIQUE SCAN                           | PK_BS_PERSON_TYPE        |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  11 |  SORT AGGREGATE                               |                          |      1 |    18 |       |            |          |       |       |          |
|  12 |   TABLE ACCESS BY INDEX ROWID                 | BS_INSURED               |      1 |    18 |       |     1   (0)| 00:00:01 |       |       |          |
|* 13 |    INDEX UNIQUE SCAN                          | PK_BS_INSURED            |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  14 |  TABLE ACCESS BY INDEX ROWID                  | BS_CORP_PRES             |      1 |     8 |       |     1   (0)| 00:00:01 |       |       |          |
|* 15 |   INDEX RANGE SCAN                            | INDEX_BS_CORP_PRES_INDI  |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  16 |  TABLE ACCESS BY INDEX ROWID                  | BS_DISEASE               |      1 |    33 |       |     1   (0)| 00:00:01 |       |       |          |
|* 17 |   INDEX RANGE SCAN                            | INX_BS_DISEASE_01        |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  18 |  TABLE ACCESS BY INDEX ROWID                  | MT_APPLY                 |      1 |     9 |       |     1   (0)| 00:00:01 |       |       |          |
|* 19 |   INDEX UNIQUE SCAN                           | PK_MT_APPLY              |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  20 |  TABLE ACCESS BY INDEX ROWID                  | BS_DISEASE               |      1 |    33 |       |     1   (0)| 00:00:01 |       |       |          |
|* 21 |   INDEX RANGE SCAN                            | INX_BS_DISEASE_01        |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  22 |  TABLE ACCESS BY INDEX ROWID                  | BS_TREAT_TYPE            |      1 |    23 |       |     1   (0)| 00:00:01 |       |       |          |
|* 23 |   INDEX UNIQUE SCAN                           | PK_BS_TREAT_TYPE         |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  24 |  SORT AGGREGATE                               |                          |      1 |    34 |       |            |          |       |       |          |
|* 25 |   TABLE ACCESS BY INDEX ROWID                 | MT_FEE_STAT_FIN          |      1 |    34 |       |     1   (0)| 00:00:01 |       |       |          |
|* 26 |    INDEX RANGE SCAN                           | PK_MT_FEE_STAT_FIN       |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  27 |  SORT AGGREGATE                               |                          |      1 |    31 |       |            |          |       |       |          |
|* 28 |   TABLE ACCESS BY INDEX ROWID                 | MT_FEE_STAT_FIN          |      1 |    31 |       |     1   (0)| 00:00:01 |       |       |          |
|* 29 |    INDEX RANGE SCAN                           | PK_MT_FEE_STAT_FIN       |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|  30 |  VIEW                                         |                          |  20043 |    29M|       |   309K  (1)| 01:01:52 |       |       |          |
|  31 |   SORT ORDER BY                               |                          |  20043 |    16M|    34M|   309K  (1)| 01:01:52 |    18M|  1609K|          |
|  32 |    COUNT                                      |                          |        |       |       |            |          |       |       |          |
|  33 |     NESTED LOOPS                              |                          |  20043 |    16M|       |   305K  (1)| 01:01:08 |       |       |          |
|* 34 |      HASH JOIN                                |                          |  20011 |    16M|       |   305K  (1)| 01:01:07 |  1000K|  1000K|          |
|  35 |       TABLE ACCESS FULL                       | BS_BIZTYPE               |     97 |  1164 |       |     3   (0)| 00:00:01 |       |       |          |
|* 36 |       HASH JOIN RIGHT OUTER                   |                          |  20011 |    16M|       |   305K  (1)| 01:01:07 |  1000K|  1000K|          |
|  37 |        TABLE ACCESS FULL                      | BS_CENTER                |     12 |   168 |       |     3   (0)| 00:00:01 |       |       |          |
|* 38 |        HASH JOIN RIGHT OUTER                  |                          |  20011 |    16M|       |   305K  (1)| 01:01:07 |  1023K|  1023K|          |
|  39 |         VIEW                                  |                          |      1 |    12 |       |   117   (2)| 00:00:02 |       |       |          |
|* 40 |          HASH JOIN                            |                          |      1 |    18 |       |   117   (2)| 00:00:02 |  1133K|  1133K|     1/0/0|
|  41 |           TABLE ACCESS FULL                   | BS_COUNTRY_STREET        |      1 |     7 |       |     2   (0)| 00:00:01 |       |       |          |
|* 42 |           TABLE ACCESS FULL                   | BS_CORP                  |   9523 |   102K|       |   114   (1)| 00:00:02 |       |       |          |
|* 43 |         HASH JOIN                             |                          |  20011 |    15M|       |   305K  (1)| 01:01:06 |   876K|   876K|          |
|  44 |          INDEX FULL SCAN                      | IDX_BS_HOSPITAL_NAME     |   1325 | 43725 |       |     2   (0)| 00:00:01 |       |       |          |
|* 45 |          TABLE ACCESS BY INDEX ROWID          | MT_BIZ_FIN               |      1 |   225 |       |     1   (0)| 00:00:01 |       |       |          |
|  46 |           NESTED LOOPS                        |                          |  25021 |    19M|       |   305K  (1)| 01:01:06 |       |       |          |
|  47 |            VIEW                               |                          |  25349 |    13M|       |   295K  (1)| 00:59:04 |       |       |          |
|  48 |             HASH GROUP BY                     |                          |  25349 |  1460K|       |   295K  (1)| 00:59:04 |  5134K|  1981K|          |
|  49 |              VIEW                             |                          |  25349 |  1460K|       |   295K  (1)| 00:59:04 |       |       |          |
|  50 |               HASH UNIQUE                     |                          |  25349 |  4876K|    10M|   295K  (1)| 00:59:04 |    32M|  3975K|          |
|  51 |                NESTED LOOPS                   |                          |  25349 |  4876K|       |   294K  (1)| 00:58:51 |       |       |          |
|  52 |                 NESTED LOOPS                  |                          |  25309 |  4424K|       |   294K  (1)| 00:58:51 |       |       |          |
|  53 |                  NESTED LOOPS                 |                          |  25309 |  3880K|       |   294K  (1)| 00:58:51 |       |       |          |
|* 54 |                   HASH JOIN                   |                          |  25502 |  2365K|   189M|   289K  (1)| 00:57:50 |  1314K|  1168K|     1/0/0|
|* 55 |                    TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ           |    205 |  6560 |       |     9   (0)| 00:00:01 |       |       |          |
|  56 |                     NESTED LOOPS              |                          |   3750K|   146M|       |  1405   (2)| 00:00:17 |       |       |          |
|* 57 |                      TABLE ACCESS FULL        | PM_BILL                  |     31 |   279 |       |  1119   (2)| 00:00:14 |       |       |          |
|* 58 |                      INDEX RANGE SCAN         | IDX_PM_ACCOUNT_BIZ_MONTH |    213 |       |       |     1   (0)| 00:00:01 |       |       |          |
|* 59 |                    TABLE ACCESS FULL          | MT_BIZ_FIN               |     21M|  1131M|       |   209K  (2)| 00:41:54 |       |       |          |
|* 60 |                   TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN        |      1 |    62 |       |     1   (0)| 00:00:01 |       |       |          |
|* 61 |                    INDEX RANGE SCAN           | IDX$$_429C0002           |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|* 62 |                  INDEX UNIQUE SCAN            | PK_BS_BIZTYPE            |      1 |    22 |       |     1   (0)| 00:00:01 |       |       |          |
|* 63 |                 INDEX UNIQUE SCAN             | PK_BS_INSURED            |      1 |    18 |       |     1   (0)| 00:00:01 |       |       |          |
|* 64 |            INDEX RANGE SCAN                   | PK_MT_BIZ_FIN            |      1 |       |       |     1   (0)| 00:00:01 |       |       |          |
|* 65 |      INDEX UNIQUE SCAN                        | PK_BS_INSURED            |      1 |     6 |       |     1   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2
   2 - SEL$2        / T@SEL$2
   3 - SEL$2        / T@SEL$2
   4 - SEL$3
   5 - SEL$3        / T@SEL$3
   6 - SEL$3        / T@SEL$3
   7 - SEL$7        / BS@SEL$7
   8 - SEL$7        / BS@SEL$7
   9 - SEL$8        / PP@SEL$8
  10 - SEL$8        / PP@SEL$8
  11 - SEL$15
  12 - SEL$15       / A@SEL$15
  13 - SEL$15       / A@SEL$15
  14 - SEL$4        / T@SEL$4
  15 - SEL$4        / T@SEL$4
  16 - SEL$9        / T@SEL$9
  17 - SEL$9        / T@SEL$9
  18 - SEL$11       / T@SEL$11
  19 - SEL$11       / T@SEL$11
  20 - SEL$10       / Q@SEL$10
  21 - SEL$10       / Q@SEL$10
  22 - SEL$5        / BS_TREAT_TYPE@SEL$5
  23 - SEL$5        / BS_TREAT_TYPE@SEL$5
  24 - SEL$16
  25 - SEL$16       / SS@SEL$16
 26 - SEL$16       / SS@SEL$16
  27 - SEL$17
  28 - SEL$17       / SS@SEL$17
  29 - SEL$17       / SS@SEL$17
  30 - SEL$6        / W@SEL$1
  31 - SEL$6
  35 - SEL$6        / G@SEL$6
  37 - SEL$6        / H@SEL$6
  39 - SEL$8F7BCF6F / STREET@SEL$6
  40 - SEL$8F7BCF6F
  41 - SEL$8F7BCF6F / ST@SEL$12
  42 - SEL$8F7BCF6F / CORP@SEL$12
  44 - SEL$6        / A@SEL$6
  45 - SEL$6        / B@SEL$6
  47 - SEL$6C11BF2C / T@SEL$6
  48 - SEL$6C11BF2C
  49 - SEL$93984FCC / $vm_view@SEL$6C11BF2C
  50 - SEL$93984FCC
  55 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
  57 - SEL$93984FCC / PM_BILL@SEL$19
  58 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
  59 - SEL$93984FCC / B@SEL$14
  60 - SEL$93984FCC / C@SEL$14
  61 - SEL$93984FCC / C@SEL$14
  62 - SEL$93984FCC / G@SEL$14
  63 - SEL$93984FCC / J@SEL$14
  64 - SEL$6        / B@SEL$6
  65 - SEL$6        / J@SEL$6

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

   2 - filter("T"."VALID_FLAG"='1')
   3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
       filter("T"."CENTER_ID"='430701')
   5 - filter("T"."VALID_FLAG"='1')
   6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
       filter("T"."CENTER_ID"='0')
   8 - access("BS"."SEX"=TO_NUMBER(:B1))
  10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
  13 - access("A"."INDI_ID"=:B1)
  15 - access("T"."INDI_ID"=:B1)
  17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
  19 - access("T"."SERIAL_APPLY"=:B1)
  21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
  23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
  25 - filter("SS"."VALID_FLAG"='1')
  26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
  28 - filter("SS"."VALID_FLAG"='1')
  29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
  34 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  36 - access("B"."CENTER_ID"="H"."CENTER_ID")
  38 - access("B"."CORP_ID"="STREET"."CORP_ID")
  40 - access("CORP"."AREA_CODE"="ST"."QYBM")
  42 - filter("CORP"."AREA_CODE" IS NOT NULL)
  43 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
  45 - filter("B"."VALID_FLAG"='1')
  54 - access("B"."HOSPITAL_ID"||"B"."SERIAL_NO"="HOSPITAL_ID"||"SERIAL_NO")
  55 - filter(("BIZ_TYPE"<>'12' OR ("BIZ_FLAG"=18 AND "BIZ_TYPE"='12')))
  57 - filter("OUTPAY_BILL_NO"=23)
  58 - access("MONTH_DECL_SN"="RELA_DECL_SN")
  59 - filter("B"."VALID_FLAG"='1')
  60 - filter("C"."VALID_FLAG"='1')
  61 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
  62 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  63 - access("B"."INDI_ID"="J"."INDI_ID")
  64 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
  65 - access("B"."INDI_ID"="J"."INDI_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."POLICY_VALUE"[VARCHAR2,200]
   2 - "T"."POLICY_VALUE"[VARCHAR2,200]
   3 - "T".ROWID[ROWID,10]
   4 - "T"."POLICY_VALUE"[VARCHAR2,200]
   5 - "T"."POLICY_VALUE"[VARCHAR2,200]
   6 - "T".ROWID[ROWID,10]
   7 - "BS".ROWID[ROWID,10], "BS"."SEX_NAME"[VARCHAR2,5]
   8 - "BS".ROWID[ROWID,10]
   9 - "PP".ROWID[ROWID,10], "PP"."PERS_NAME"[VARCHAR2,20]
  10 - "PP".ROWID[ROWID,10]
  11 - (#keys=0) MAX("INSR_CODE")[30]
  12 - "INSR_CODE"[VARCHAR2,30]
  13 - "A".ROWID[ROWID,10]
  14 - "T".ROWID[ROWID,10], "T"."SPECIAL_CODE"[NUMBER,22]
  15 - "T".ROWID[ROWID,10]
  16 - "T".ROWID[ROWID,10], "T"."DISEASE"[VARCHAR2,100]
  17 - "T".ROWID[ROWID,10]
  18 - "T".ROWID[ROWID,10], "T"."APPLY_CONTENT"[VARCHAR2,3]
  19 - "T".ROWID[ROWID,10]
  20 - "Q".ROWID[ROWID,10], "Q"."DISEASE"[VARCHAR2,100]
  21 - "Q".ROWID[ROWID,10]
  22 - "BS_TREAT_TYPE".ROWID[ROWID,10], "TREATMENT_NAME"[VARCHAR2,50]
  23 - "BS_TREAT_TYPE".ROWID[ROWID,10]
  24 - (#keys=0) SUM("SS"."PART_SELF_MONEY")[22], SUM("SS"."ALL_SELF_MONEY")[22]
  25 - "SS"."PART_SELF_MONEY"[NUMBER,22], "SS"."ALL_SELF_MONEY"[NUMBER,22]
  26 - "SS".ROWID[ROWID,10]
  27 - (#keys=0) SUM("SS"."MONEY")[22]
  28 - "SS"."MONEY"[NUMBER,22]
  29 - "SS".ROWID[ROWID,10]
  30 - "ROWNO"[NUMBER,22], "HOS_SERIAL"[VARCHAR2,30], "REG_FLAG"[CHARACTER,1], "DISEASE_TYPE"[CHARACTER,1], "HOSPITAL_ID"[VARCHAR2,20],
       "HOSPITAL_NAME"[VARCHAR2,70], "SERIAL_NO"[VARCHAR2,16], "BIZ_TYPE"[VARCHAR2,2], "CASE_ID"[NUMBER,22], "BIZ_STAT"[VARCHAR2,2], "NAME"[VARCHAR2,20],
       "SEX"[VARCHAR2,5], "PERS_TYPE"[VARCHAR2,20], "BEGIN_DATE"[VARCHAR2,10], "END_DATE"[VARCHAR2,10], "FIN_DATE"[VARCHAR2,19], "FINISH_DATE"[VARCHAR2,19],
       "INDI_ID"[NUMBER,22], "INSR_CODE"[VARCHAR2,30], "FIN_STAFF"[VARCHAR2,10], "FIN_MAN"[VARCHAR2,30], "CORP_ID"[NUMBER,22], "IDCARD"[VARCHAR2,25],
       "REG_DATE"[VARCHAR2,19], "IN_DAYS"[NUMBER,22], "DISTRICT_CODE"[VARCHAR2,6], "CENTER_ID"[VARCHAR2,10], "OFFICE_GRADE"[VARCHAR2,3],
       "CORP_NAME"[VARCHAR2,70], "DISEASE"[VARCHAR2,100], "IN_AREA_NAME"[VARCHAR2,20], "IN_DEPT_NAME"[VARCHAR2,20], "IN_BED"[VARCHAR2,10],
       "BED_TYPE"[CHARACTER,1], "PATIENT_ID"[VARCHAR2,20], "REMARK"[VARCHAR2,1000], "POS_CODE"[VARCHAR2,10], "REIMBURSE_FLAG"[CHARACTER,1],
       "FIN_DISEASE"[VARCHAR2,100], "FOREGIFT"[NUMBER,1], "IC_NO"[VARCHAR2,25], "TREATMENT_TYPE"[VARCHAR2,3], "APPLY_CONTENT"[VARCHAR2,3],
       "REG_MAN"[VARCHAR2,20], "W"."AREA_CODE"[VARCHAR2,10], "FEES"[NUMBER,22], "PAY_MONEY_JD"[NUMBER,22], "FUND_MONEY"[NUMBER,22], "FUND_301"[NUMBER,22],
       "FUND_003"[NUMBER,22], "QFX"[NUMBER,22], "HOSP_ZF"[NUMBER,22], "CENTER_ZF"[NUMBER,22], "YW_FUND_MONEY"[NUMBER,22], "BC_FUND_MONEY"[NUMBER,22],
       "PAY_MONEY_ALLSELF"[NUMBER,22], "PAY_MONEY_SELF"[NUMBER,22], "PAY_MONEY_E00SELF"[NUMBER,22], "PAY_MONEY_Z00SELF"[NUMBER,22],
       "PAY_MONEY_S00SELF"[NUMBER,22], "PAY_MONEY_S01SELF"[NUMBER,22], "PAY_MONEY_C000SELF"[NUMBER,22], "PAY_MONEY_C001SELF"[NUMBER,22],
       "PAY_MONEY_C004SELF"[NUMBER,22], "PAY_MONEY_C006SELF"[NUMBER,22], "PAY_MONEY_C007SELF"[NUMBER,22], "PAY_MONEY_C007003SELF"[NUMBER,22],
       "DB_MONEY"[NUMBER,22], "YWSH_DB_MONEY"[NUMBER,1], "TC_MONEY"[NUMBER,22], "TC_MONEY_XE"[NUMBER,22], "HOSP_PAY"[NUMBER,22], "HOSP_PRISE"[NUMBER,22],
       "JMTC_MONEY"[NUMBER,22], "YW_MONEY"[NUMBER,22], "JMYW_MONEY"[NUMBER,22], "ACCT_MONEY"[NUMBER,22], "LX_MONEY"[NUMBER,22], "SY_MONEY"[NUMBER,22],
       "OFFI_MONEY"[NUMBER,22], "ZHAOGU_PAY"[NUMBER,22], "PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "REG_INFO"[VARCHAR2,10], "DB_MONEY_JUMIN"[NUMBER,22]
  31 - (#keys=1) ROWNUM[22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."REG_FLAG"[CHARACTER,1],
      "B"."DISEASE_TYPE"[CHARACTER,1], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."BIZ_TYPE"[VARCHAR2,2], "B"."CASE_ID"[NUMBER,22], "G"."BIZ_STAT"[VARCHAR2,2], "B"."NAME"[VARCHAR2,20], [5], [20],
       TO_CHAR(INTERNAL_FUNCTION("B"."BEGIN_DATE"),'yyyy-mm-dd')[10], TO_CHAR(INTERNAL_FUNCTION("B"."END_DATE"),'yyyy-mm-dd')[10],
       TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19],
       "B"."INDI_ID"[NUMBER,22], "T"."INSR_CODE"[VARCHAR2,30], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CORP_ID"[NUMBER,22],
       "B"."IDCARD"[VARCHAR2,25], TO_CHAR(INTERNAL_FUNCTION("B"."REG_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], NVL("B"."IN_DAYS",0)[22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."CENTER_ID"[VARCHAR2,10], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."CORP_NAME"[VARCHAR2,70],
       "B"."IN_DISEASE"[VARCHAR2,20], [100], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
','')[1000], "B"."POS_CODE"[VARCHAR2,10],PATIENT_ID"[VARCHAR2,20], TRANSLATE("B"."REMARK",'
       "B"."REIMBURSE_FLAG"[CHARACTER,1], [100], 0[1], "B"."IC_NO"[VARCHAR2,25], "B"."TREATMENT_TYPE"[VARCHAR2,3], [3], "B"."REG_MAN"[VARCHAR2,20],
       "STREET"."QYBM"[VARCHAR2,10], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
      "T"."FUND_003"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
       "T"."BC_FUND_MONEY"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
       "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
       "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"-"T"."YWSH_DB_MONEY"[22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
       "T"."TC_MONEY_XE"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
       "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
       "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
       "B"."REG_INFO"[VARCHAR2,10]
  32 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
       "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
       "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
       "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
       "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
       "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
       "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
       "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
       "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
       "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
       "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
       "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], ROWNUM[4]
  33 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
       "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
       "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
       "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
      "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
       "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
       "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
       "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
       "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
      "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
       "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
       "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30]
  34 - (#keys=2) "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
       "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
       "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
       "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
       "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
       "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
       "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
       "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
       "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
       "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
       "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
       "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30]
  35 - "G"."CENTER_ID"[VARCHAR2,10], "G"."BIZ_TYPE"[CHARACTER,2], "G"."BIZ_STAT"[VARCHAR2,2]
  36 - (#keys=1) "H"."CENTER_ID"[VARCHAR2,10], "B"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22],
       "STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
       "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
       "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
       "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
       "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
       "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
       "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
       "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
       "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
       "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
       "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30],
       "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
       "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
       "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
       "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
       "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
  37 - "H"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10]
  38 - (#keys=1) "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70],
       "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
      "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
       "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
       "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
       "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
       "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
       "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
       "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
       "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
       "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
       "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
       "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
  39 - "STREET"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10]
  40 - (#keys=1) "ST"."QYBM"[VARCHAR2,10], "CORP"."CORP_ID"[NUMBER,22]
  41 - "ST"."QYBM"[VARCHAR2,10]
  42 - "CORP"."CORP_ID"[NUMBER,22], "CORP"."AREA_CODE"[VARCHAR2,20]
  43 - (#keys=1) "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
       "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
       "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
       "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
       "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
       "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
       "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
       "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
       "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
       "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
       "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22],
       "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
       "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
       "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
       "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
       "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3],
       "B"."HOS_SERIAL"[VARCHAR2,30]
  44 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70]
  45 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
       "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20],
       "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7],
       "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3],
       "B"."HOS_SERIAL"[VARCHAR2,30], "B"."DISEASE_TYPE"[CHARACTER,1]
  46 - "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
       "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
       "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
       "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
       "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
       "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
       "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
       "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
       "T"."DB_MONEY_JUMIN"[NUMBER,22], "SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
  47 - "T"."HOSPITAL_ID"[VARCHAR2,20], "T"."SERIAL_NO"[VARCHAR2,16], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
       "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
       "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
       "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
       "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
       "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
       "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
       "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
       "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
       "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22]
  48 - "$vm_col_8"[VARCHAR2,20], "$vm_col_9"[VARCHAR2,16], "$vm_col_7"[NUMBER,22], SUM(DECODE("$vm_col_5",'803',"$vm_col_4",0))[22], SUM(CASE  WHEN
       (("$vm_col_3"='C000' OR "$vm_col_3"='C001' OR "$vm_col_3"='C002' OR "$vm_col_3"='C003' OR "$vm_col_3"='C004') AND ("$vm_col_5"='999' OR
       "$vm_col_5"='003')) THEN "$vm_col_4" ELSE 0 END )[22], SUM(DECODE("$vm_col_5",'999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_5",'401',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'301',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'511',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_5",'202',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'003',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'802',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_5",'901',"$vm_col_4",'802',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'801',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_5",'001',"$vm_col_4",'801',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'996',NVL("$vm_col_4",0),0))[22],
       SUM(DECODE("$vm_col_5",'201',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C007003',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_5",'C007999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C006003',"$vm_col_4",'C006999',"$vm_col_4",'C0
       07003',"$vm_col_4",'C007999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C004003',"$vm_col_4",'C004999',"$vm_col_4",'C005003',"$vm_col_4
       ",'C005999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C001003',"$vm_col_4",'C001999',"$vm_col_4",'C002003',"$vm_col_4",'C002999',"$vm_
       col_4",'C003003',"$vm_col_4",'C003999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3"||"$vm_col_5",'C000003',"$vm_col_4",'C000999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_5",'S02003',"$vm_col_4",'S02999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_5",'S01003',"$vm_col_4",'S01999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_5",'S00003',"$vm_col_4",'S00999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_5",'Z00003',"$vm_col_4",'Z00999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_5",'E00003',"$vm_col_4",'E00999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_2"||"$vm_col_5",'102003',"$vm_col_4",'102999',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_2"||"$vm_col_5",'101003',"$vm_col_4",'101999',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'998',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_5",'996',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_3",'S01',"$vm_col_4",'S02',"$vm_col_4",0))[22],
       SUM(DECODE("$vm_col_5",'306',"$vm_col_4",0))[22], SUM(DECODE("$vm_col_5",'001',"$vm_col_4",'511',"$vm_col_4",'202',"$vm_col_4",'801',"$vm_col_4",0))[2
       2], SUM(DECODE("$vm_col_3",'C000',"$vm_col_4",'C001',"$vm_col_4",'C002',"$vm_col_4",'C003',"$vm_col_4",'C004',"$vm_col_4",'C005',"$vm_col_4",'C006',"$
       vm_col_4",'C007',"$vm_col_4",0))[22], SUM("$vm_col_4")[22]
  49 - "$vm_col_8"[VARCHAR2,20], "$vm_col_9"[VARCHAR2,16], "$vm_col_7"[NUMBER,22], "$vm_col_2"[VARCHAR2,3], "$vm_col_3"[VARCHAR2,20],
       "$vm_col_4"[NUMBER,22], "$vm_col_5"[VARCHAR2,3]
  50 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."INDI_ID"[NUMBER,22], ROWID[ROWID,10], ROWID[ROWID,10], ROWID[ROWID,10],
       ROWID[ROWID,10], "HOSPITAL_ID"||"SERIAL_NO"[36], "C"."LABEL_FLAG"[VARCHAR2,3], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."REAL_PAY"[NUMBER,22],
       "C"."FUND_ID"[VARCHAR2,3], "FINISH_FLAG"[CHARACTER,1]
  51 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
       "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10], ROWID[ROWID,10]
  52 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
       "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10]
  53 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10], ROWID[ROWID,10],
       "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
  54 - (#keys=1) "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
  55 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16]
  56 - "PM_ACCOUNT_BIZ".ROWID[ROWID,10]
  57 - "RELA_DECL_SN"[NUMBER,22]
  58 - "PM_ACCOUNT_BIZ".ROWID[ROWID,10]
  59 - ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22],
       "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
  60 - ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
  61 - ROWID[ROWID,10]
  62 - ROWID[ROWID,10]
  63 - ROWID[ROWID,10]
  64 - "SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]

Note
-----
   - SQL profile "SYS_SQLPROF_0154fb8617518000" used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


456 rows selected.

8

9

10

使用sql profile后执行时间是几分种,执行计划如上图所示,消耗时间的操作是对表mt_biz_fin执行全表扫描,该表的记录数是21M,访问该表的谓词条件用到了该表的hostpital_id与serial_no,而它们是该表的复合主键的前导列,而谓词条件写成了
b.hospital_id||b.serial_no in() 这就没有办法用到主键索引,应该用(b.hosptial_id,b.serial_no) in()。删除sql profile:

SQL> execute dbms_sqltune.drop_sql_profile(name => 'SYS_SQLPROF_0154fb8617518000');
 
PL/SQL procedure successfully completed

SQL> select * from dba_sql_profiles where sql_text like 'select rowno, hos_serial, reg_flag,%';
 
NAME                           CATEGORY                        SIGNATURE SQL_TEXT          CREATED     LAST_MODIFIED DESCRIPTION   TYPE      STATUS   FORCE_MATCHING
------------------------------ ------------------------------ ---------- ----------------- ----------- ------------- ------------- --------- -------- --------------

将谓词条件b.hospital_id||b.serial_no in()修改为(b.hosptial_id,b.serial_no) in()。修改后的sql如下所示:

select /*+ jy_20160405_01 */ rowno, hos_serial, reg_flag,
       nvl((SELECT distinct t.policy_value
              FROM fc_biz_policy t
             WHERE t.policy_code = 'can_clinic_flag'
               and t.valid_flag = '1'
               and t.center_id = '430701'), (SELECT distinct t.policy_value
                FROM fc_biz_policy t
               WHERE t.policy_code =
                     'can_clinic_flag'
                 and t.valid_flag = '1'
                 and t.center_id = '0')) AS can_clinic_flag,
       decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D', '危重', '') DISEASE_TYPE,
       hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat,
       name, sex, pers_type, begin_date, end_date, fin_date, finish_date,
       reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id,
       idcard, district_code, office_grade,center_id,
       (select t.special_code
           from bs_corp_pres t
          where t.indi_id = w.indi_id) as special_code, corp_name, disease,
       in_area_name, in_dept_name,
       decode(apply_content, '126', '转外住院', '127', '转外复查', '普通住院') as apply_content,
       in_bed, bed_type, patient_id, remark, pos_code, reimburse_flag,
       fin_disease, ic_no, treatment_type,
       decode(reg_info || treatment_type, 'WD3120', '普通住院(转外住院)', 'WD2120', '普通住院(异地住院)', '1120', '普通住院(转诊转院)', 'F120', '普通住院(非首诊就诊)', 'WD1120', '普通住院(异地安置)', 'C120', '普通住院(首诊就诊)', (select treatment_name
                   from bs_treat_type
                  where treatment_type =
                        w.treatment_type
                    and center_id =
                        w.center_id)) as treatment_name,
       nvl(pay_money_jd, 0) as pay_money_jd,
       nvl(pay_money_xzf, 0) as pay_money_xzf, nvl(fees, 0) as fees,
       foregift, nvl(pay_money_allself, 0) as pay_money_allself,
       nvl(pay_money_self, 0) as pay_money_self,
       nvl(pay_money_E00self, 0) as pay_money_E00self,
       nvl(pay_money_Z00self, 0) as pay_money_Z00self,
       nvl(pay_money_S00self, 0) as pay_money_S00self,
       nvl(pay_money_S01self, 0) as pay_money_S01self,
       nvl(pay_money_S01self, 0) as pay_money_S01self,
       nvl(pay_money_C000self, 0) as pay_money_C000self,
       nvl(pay_money_C001self, 0) as pay_money_C001self,
       nvl(pay_money_C004self, 0) as pay_money_C004self,
       nvl(pay_money_C006self, 0) as pay_money_C006self,
       nvl(pay_money_C007self, 0) as pay_money_C007self,
       nvl(pay_money_C007003self, 0) as pay_money_C007003self,
       nvl(tc_money, 0) as tc_money, nvl(tc_money_xe, 0) as tc_money_xe,
       nvl(hosp_pay, 0) as hosp_pay, nvl(hosp_prise, 0) as hosp_prise,
       nvl(jmtc_money, 0) as jmtc_money, nvl(yw_money, 0) as yw_money,
       nvl(jmyw_money, 0) as jmyw_money, nvl(acct_money, 0) as acct_money,
       nvl(lx_money, 0) as lx_money, nvl(sy_money, 0) as sy_money,
       nvl(fund_money, 0) as fund_money, nvl(fund_301,0) fund_301 ,nvl(fund_003,0) fund_003 ,nvl(qfx, 0) as qfx,
       nvl(hosp_zf, 0) as hosp_zf, nvl(center_zf, 0) as center_zf,
       nvl(yw_fund_money, 0) as yw_fund_money,
       nvl(bc_fund_money, 0) as bc_fund_money, nvl(db_money, 0) as db_money,
       nvl(YWSH_DB_MONEY, 0) as YWSH_DB_MONEY,
       nvl(offi_money, 0) as offi_money, reg_man,
       nvl(zhaogu_pay, 0) as zhaogu_pay, w.area_code, PAY_MONEY_DNZF,
       medi_pay, medi_zfy, nvl(DB_MONEY_JUMIN, 0) as db_money_jumin
  from (select rownum rowno, b.hos_serial, b.reg_flag, b.disease_type,
                a.hospital_id, a.hospital_name, b.serial_no, b.biz_type,
                b.case_id, g.biz_stat, b.name, (select bs.sex_name from bs_sex bs where bs.sex =b.sex) sex,
                (select pp.pers_name from bs_person_type pp where pp.pers_type = b.pers_type_detail and pp.center_id = b.center_id) as pers_type,
                to_char(b.begin_date, 'yyyy-mm-dd') begin_date,
                to_char(b.end_date, 'yyyy-mm-dd') end_date,
                to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') fin_date,
                to_char(b.fin_date, 'yyyy-mm-dd hh24:mi:ss') finish_date,
                b.indi_id, t.insr_code, b.fin_staff, b.fin_man, b.corp_id,
                b.idcard,
                to_char(b.reg_date, 'yyyy-mm-dd hh24:mi:ss') reg_date,
                nvl(b.in_days, 0) in_days, b.district_code, b.center_id,
                b.office_grade, b.corp_name, b.in_disease,
                (select t.disease
                    from bs_disease t
                   where t.center_id = nvl(h.catalog_center, h.center_id)
                     and b.in_disease = t.icd) as disease, b.in_area_name,
                b.in_dept_name, b.in_bed, b.bed_type, b.patient_id,
                translate(b.remark, chr(13), '') remark, b.pos_code,
                b.reimburse_flag,
                (select q.disease
                    from bs_disease q
                   where q.center_id = nvl(h.catalog_center, h.center_id)
                     and b.fin_disease = q.icd) as fin_disease, 0 as foregift,
                b.ic_no, b.treatment_type,
                (select t.apply_content
                    from mt_apply t
                   where t.serial_apply = b.serial_apply) as apply_content,
                b.reg_man, street.QYBM as area_code, T.FEES, T.PAY_MONEY_JD,
                T.FUND_MONEY, T.FUND_301, T.FUND_003,T.QFX, T.HOSP_ZF, T.CENTER_ZF, T.YW_FUND_MONEY,
                T.BC_FUND_MONEY, T.PAY_MONEY_ALLSELF, T.PAY_MONEY_SELF,
                T.PAY_MONEY_E00SELF, T.PAY_MONEY_Z00SELF, T.PAY_MONEY_S00SELF,
                T.PAY_MONEY_S01SELF, T.PAY_MONEY_S02SELF, T.PAY_MONEY_C000SELF,
                T.PAY_MONEY_C001SELF, T.PAY_MONEY_C004SELF,
                T.PAY_MONEY_C006SELF, T.PAY_MONEY_C007SELF,
                T.PAY_MONEY_C007003SELF,
                (T.DB_MONEY - t.YWSH_DB_MONEY) as DB_MONEY, t.YWSH_DB_MONEY,
                T.TC_MONEY, T.TC_MONEY_XE, t.hosp_pay, t.hosp_prise,
                T.JMTC_MONEY, T.YW_MONEY, T.JMYW_MONEY, T.ACCT_MONEY,
                T.LX_MONEY, T.SY_MONEY, T.OFFI_MONEY, T.ZHAOGU_PAY,
                T.PAY_MONEY_XZF, PAY_MONEY_DNZF, medi_pay, medi_zfy,
                b.REG_INFO, T.DB_MONEY_JUMIN as DB_MONEY_JUMIN
           from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h,
                bs_insured j,
                (select corp.CORP_ID, corp.AREA_CODE, st.QYBM
                    from bs_corp corp
                   inner join bs_country_street st
                      on corp.AREA_CODE = st.QYBM) street,
                (SELECT B.hospital_id, B.serial_no, B.indi_id,
                         (select max(insr_code)
                             from bs_insured a
                            where a.indi_id = B.indi_id) insr_code,
                         SUM(c.real_pay) AS FEES,
                         SUM(DECODE(c.POLICY_ITEM_CODE, 'C000', c.REAL_PAY, 'C001', c.REAL_PAY, 'C002', c.REAL_PAY, 'C003', c.REAL_PAY, 'C004', c.REAL_PAY, 'C005', c.REAL_PAY, 'C006', c.REAL_PAY, 'C007', c.REAL_PAY, 0)) AS PAY_MONEY_JD,
                         SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '511', c.REAL_PAY, '202', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS FUND_MONEY,
                         SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY,  0)) AS FUND_301,
                         SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY,  0)) AS FUND_003,
                         SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_FUND_MONEY,
                         SUM(DECODE(c.FUND_ID, '306', c.REAL_PAY, 0)) AS BC_FUND_MONEY,
                         SUM(DECODE(c.POLICY_ITEM_CODE, 'S01', c.REAL_PAY, 'S02', c.REAL_PAY, 0)) AS QFX,
                         SUM(DECODE(c.FUND_ID, '996', c.REAL_PAY, 0)) AS HOSP_ZF,
                         SUM(DECODE(c.FUND_ID, '998', c.REAL_PAY, 0)) AS CENTER_ZF,
                         SUM(DECODE(c.label_flag || c.FUND_ID, '101003', c.REAL_PAY, '101999', c.REAL_PAY, 0)) AS PAY_MONEY_ALLSELF,
                         SUM(DECODE(c.label_flag || c.FUND_ID, '102003', c.REAL_PAY, '102999', c.REAL_PAY, 0)) AS PAY_MONEY_SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'E00003', c.REAL_PAY, 'E00999', c.REAL_PAY, 0)) AS PAY_MONEY_E00SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'Z00003', c.REAL_PAY, 'Z00999', c.REAL_PAY, 0)) AS PAY_MONEY_Z00SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S00003', c.REAL_PAY, 'S00999', c.REAL_PAY, 0)) AS PAY_MONEY_S00SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S01003', c.REAL_PAY, 'S01999', c.REAL_PAY, 0)) AS PAY_MONEY_S01SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'S02003', c.REAL_PAY, 'S02999', c.REAL_PAY, 0)) AS PAY_MONEY_S02SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C000003', c.REAL_PAY, 'C000999', c.REAL_PAY, 0)) AS PAY_MONEY_C000SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C001003', c.REAL_PAY, 'C001999', c.REAL_PAY, 'C002003', c.REAL_PAY, 'C002999', c.REAL_PAY, 'C003003', c.REAL_PAY, 'C003999', c.REAL_PAY, 0)) AS PAY_MONEY_C001SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C004003', c.REAL_PAY, 'C004999', c.REAL_PAY, 'C005003', c.REAL_PAY, 'C005999', c.REAL_PAY, 0)) AS PAY_MONEY_C004SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C006003', c.REAL_PAY, 'C006999', c.REAL_PAY, 'C007003', c.REAL_PAY, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C006SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007999', c.REAL_PAY, 0)) AS PAY_MONEY_C007SELF,
                         SUM(DECODE(c.policy_item_code || c.FUND_ID, 'C007003', c.REAL_PAY, 0)) AS PAY_MONEY_C007003SELF,
                         SUM(DECODE(c.FUND_ID, '201', c.REAL_PAY, 0)) AS DB_MONEY,
                         0 AS YWSH_DB_MONEY,
                         SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY,
                         case
                           when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
                            nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
                           else
                            0
                         end hosp_pay,
                         case
                           when nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0) >= 0 then
                            0
                           else
                            -nvl(sum(decode(c.fund_id, '996', nvl(c.real_pay, 0), 0)), 0)
                         end hosp_prise,
                         SUM(DECODE(c.FUND_ID, '001', c.REAL_PAY, '801', c.REAL_PAY, 0)) AS TC_MONEY_XE,
                         SUM(DECODE(c.FUND_ID, '801', c.REAL_PAY, 0)) AS JMTC_MONEY,
                         SUM(DECODE(c.FUND_ID, '901', c.REAL_PAY, '802', c.REAL_PAY, 0)) AS YW_MONEY,
                         SUM(DECODE(c.FUND_ID, '802', c.REAL_PAY, 0)) AS JMYW_MONEY,
                         SUM(DECODE(c.FUND_ID, '003', c.REAL_PAY, 0)) AS ACCT_MONEY,
                         SUM(DECODE(c.FUND_ID, '202', c.REAL_PAY, 0)) AS LX_MONEY,
                         SUM(DECODE(c.FUND_ID, '511', c.REAL_PAY, 0)) AS SY_MONEY,
                         SUM(DECODE(c.FUND_ID, '301', c.REAL_PAY, 0)) AS OFFI_MONEY,
                         SUM(DECODE(c.FUND_ID, '401', c.REAL_PAY, 0)) AS ZHAOGU_PAY,
                         SUM(DECODE(c.FUND_ID, '999', c.REAL_PAY, 0)) AS PAY_MONEY_XZF,
                         SUM((CASE
                               WHEN C.POLICY_ITEM_CODE IN
                                    ('C000', 'C001', 'C002', 'C003', 'C004') AND
                                    (C.fund_id = '999' OR C.fund_id = '003') THEN
                                C.REAL_PAY
                               ELSE
                                0
                             END)) AS PAY_MONEY_DNZF,
                         (select nvl(nvl(sum(ss.all_self_money), 0) +
                                       nvl(sum(ss.part_self_money), 0), 0) as not_fund
                             from mt_fee_stat_fin ss
                            where ss.hospital_id = b.hospital_id
                              and ss.serial_no = b.serial_no
                              and ss.valid_flag = '1'
                              and ss.stat_type in ('001', '002', '003')) as medi_pay,
                         (select nvl(sum(ss.money), 0) as medi_zfy
                             from mt_fee_stat_fin ss
                            where ss.hospital_id = b.hospital_id
                              and ss.serial_no = b.serial_no
                              and ss.valid_flag = '1'
                              and ss.stat_type in ('001', '002', '003')) as medi_zfy,
                         SUM(DECODE(c.FUND_ID, '803', c.REAL_PAY, 0)) AS DB_MONEY_JUMIN
                    FROM MT_BIZ_FIN B, MT_PAY_RECORD_FIN C, BS_BIZTYPE G,
                         BS_INSURED J
                   WHERE B.HOSPITAL_ID = C.HOSPITAL_ID
                     AND B.SERIAL_NO = C.SERIAL_NO
                     AND B.CENTER_ID = G.CENTER_ID
                     AND B.BIZ_TYPE = G.BIZ_TYPE
                     AND B.VALID_FLAG = '1'
                     AND C.VALID_FLAG = '1'
                     and b.indi_id = j.indi_id
                     and (b.hospital_id,b.serial_no) in
                         (select hospital_id,serial_no
                            from pm_account_biz
                           where month_decl_sn in
                                 (select rela_decl_sn
                                    from pm_bill
                                   where outpay_bill_no = '23') and ((biz_flag = '18' and biz_type = '12') or biz_type <>'12' ))
                   GROUP BY b.hospital_id, b.serial_no, b.indi_id) T
          where a.hospital_id = b.hospital_id
            and b.center_id = g.center_id
            and b.biz_type = g.biz_type
            and b.center_id = h.center_id(+)
            and b.valid_flag = '1'
            and b.indi_id = j.indi_id
            and b.CORP_ID = street.CORP_ID(+)
            AND B.HOSPITAL_ID = T.HOSPITAL_ID
            AND B.SERIAL_NO = T.SERIAL_NO
          order by rowno) w

修改后的sql执行时间在10秒以内:
20160405_jy

20160405_jy_2

查看其执行计划:

SQL> select * from table(dbms_xplan.display_cursor('3vgjr2kwtyncm',null,'ALL ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3vgjr2kwtyncm, child number 0
-------------------------------------
select /*+ jy_20160405_01 */ rowno, hos_serial, reg_flag,        nvl((SELECT distinct t.policy_value               FROM fc_biz_policy t
WHERE t.policy_code = 'can_clinic_flag'                and t.valid_flag = '1'                and t.center_id = '430701'), (SELECT distinct
t.policy_value                 FROM fc_biz_policy t                WHERE t.policy_code =                      'can_clinic_flag'                  and
t.valid_flag = '1'                  and t.center_id = '0')) AS can_clinic_flag,        decode(DISEASE_TYPE, 'A', '病种单纯', 'B', '严重', 'C', '严重并发', 'D',
'危重', '') DISEASE_TYPE,        hospital_id, hospital_name, serial_no, biz_type, case_id, biz_stat,        name, sex, pers_type, begin_date, end_date,
fin_date, finish_date,        reg_date, in_days, indi_id, insr_code, fin_staff, fin_man, corp_id,        idcard, district_code, office_grade,center_id,
      (select t.special_code            from bs_corp_pres t           where t.indi_id = w.indi_id)

Plan hash value: 3988998535
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem |  O/1/M   | Max-Tmp |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH UNIQUE                                   |                          |      1 |    31 |     2  (50)| 00:00:01 |  1307K|  1307K|     1/0/0|         |
|*  2 |   TABLE ACCESS BY INDEX ROWID                  | FC_BIZ_POLICY            |      1 |    31 |     1   (0)| 00:00:01 |       |       |          |         |
|*  3 |    INDEX RANGE SCAN                            | PK_FC_BIZ_POLICY         |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|   4 |    HASH UNIQUE                                 |                          |      1 |    31 |     2  (50)| 00:00:01 |  1594K|  1594K|     1/0/0|         |
|*  5 |     TABLE ACCESS BY INDEX ROWID                | FC_BIZ_POLICY            |      1 |    31 |     1   (0)| 00:00:01 |       |       |          |         |
|*  6 |      INDEX RANGE SCAN                          | PK_FC_BIZ_POLICY         |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|   7 |  TABLE ACCESS BY INDEX ROWID                   | BS_SEX                   |      1 |     6 |     1   (0)| 00:00:01 |       |       |          |         |
|*  8 |   INDEX UNIQUE SCAN                            | PK_BS_SEX                |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|   9 |  TABLE ACCESS BY INDEX ROWID                   | BS_PERSON_TYPE           |      1 |    18 |     1   (0)| 00:00:01 |       |       |          |         |
|* 10 |   INDEX UNIQUE SCAN                            | PK_BS_PERSON_TYPE        |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  11 |  SORT AGGREGATE                                |                          |      1 |    18 |            |          |       |       |          |         |
|  12 |   TABLE ACCESS BY INDEX ROWID                  | BS_INSURED               |      1 |    18 |     1   (0)| 00:00:01 |       |       |          |         |
|* 13 |    INDEX UNIQUE SCAN                           | PK_BS_INSURED            |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  14 |  TABLE ACCESS BY INDEX ROWID                   | BS_CORP_PRES             |      1 |     8 |     1   (0)| 00:00:01 |       |       |          |         |
|* 15 |   INDEX RANGE SCAN                             | INDEX_BS_CORP_PRES_INDI  |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  16 |  TABLE ACCESS BY INDEX ROWID                   | BS_DISEASE               |      1 |    33 |     1   (0)| 00:00:01 |       |       |          |         |
|* 17 |   INDEX RANGE SCAN                             | INX_BS_DISEASE_01        |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  18 |  TABLE ACCESS BY INDEX ROWID                   | MT_APPLY                 |      1 |     9 |     1   (0)| 00:00:01 |       |       |          |         |
|* 19 |   INDEX UNIQUE SCAN                            | PK_MT_APPLY              |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  20 |  TABLE ACCESS BY INDEX ROWID                   | BS_DISEASE               |      1 |    33 |     1   (0)| 00:00:01 |       |       |          |         |
|* 21 |   INDEX RANGE SCAN                             | INX_BS_DISEASE_01        |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  22 |  TABLE ACCESS BY INDEX ROWID                   | BS_TREAT_TYPE            |      1 |    23 |     1   (0)| 00:00:01 |       |       |          |         |
|* 23 |   INDEX UNIQUE SCAN                            | PK_BS_TREAT_TYPE         |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  24 |  SORT AGGREGATE                                |                          |      1 |    34 |            |          |       |       |          |         |
|* 25 |   TABLE ACCESS BY INDEX ROWID                  | MT_FEE_STAT_FIN          |      1 |    34 |     1   (0)| 00:00:01 |       |       |          |         |
|* 26 |    INDEX RANGE SCAN                            | PK_MT_FEE_STAT_FIN       |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  27 |  SORT AGGREGATE                                |                          |      1 |    31 |            |          |       |       |          |         |
|* 28 |   TABLE ACCESS BY INDEX ROWID                  | MT_FEE_STAT_FIN          |      1 |    31 |     1   (0)| 00:00:01 |       |       |          |         |
|* 29 |    INDEX RANGE SCAN                            | PK_MT_FEE_STAT_FIN       |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|  30 |  VIEW                                          |                          |    504 |   771K|  1923   (2)| 00:00:24 |       |       |          |         |
|  31 |   SORT ORDER BY                                |                          |    504 |   431K|  1923   (2)| 00:00:24 |  5935K|   992K|     1/0/0|         |
|  32 |    COUNT                                       |                          |        |       |            |          |       |       |          |         |
|  33 |     NESTED LOOPS                               |                          |    504 |   431K|  1922   (2)| 00:00:24 |       |       |          |         |
|* 34 |      HASH JOIN                                 |                          |    504 |   428K|  1921   (2)| 00:00:24 |  1095K|  1095K|     2/0/0|         |
|  35 |       TABLE ACCESS FULL                        | BS_BIZTYPE               |     97 |  1164 |     3   (0)| 00:00:01 |       |       |          |         |
|* 36 |       HASH JOIN RIGHT OUTER                    |                          |    504 |   422K|  1918   (2)| 00:00:24 |  1078K|  1078K|     2/0/0|         |
|  37 |        TABLE ACCESS FULL                       | BS_CENTER                |     12 |   168 |     3   (0)| 00:00:01 |       |       |          |         |
|* 38 |        HASH JOIN RIGHT OUTER                   |                          |    504 |   415K|  1914   (1)| 00:00:23 |  1593K|  1593K|     2/0/0|         |
|  39 |         VIEW                                   |                          |      1 |    12 |   117   (2)| 00:00:02 |       |       |          |         |
|* 40 |          HASH JOIN                             |                          |      1 |    18 |   117   (2)| 00:00:02 |  1133K|  1133K|     2/0/0|         |
|  41 |           TABLE ACCESS FULL                    | BS_COUNTRY_STREET        |      1 |     7 |     2   (0)| 00:00:01 |       |       |          |         |
|* 42 |           TABLE ACCESS FULL                    | BS_CORP                  |   9523 |   102K|   114   (1)| 00:00:02 |       |       |          |         |
|* 43 |         HASH JOIN                              |                          |    504 |   409K|  1797   (1)| 00:00:22 |   915K|   915K|     2/0/0|         |
|  44 |          INDEX FULL SCAN                       | IDX_BS_HOSPITAL_NAME     |   1325 | 43725 |     2   (0)| 00:00:01 |       |       |          |         |
|* 45 |          TABLE ACCESS BY INDEX ROWID           | MT_BIZ_FIN               |      1 |   225 |     1   (0)| 00:00:01 |       |       |          |         |
|  46 |           NESTED LOOPS                         |                          |    630 |   491K|  1794   (1)| 00:00:22 |       |       |          |         |
|  47 |            VIEW                                |                          |    638 |   357K|  1539   (2)| 00:00:19 |       |       |          |         |
|  48 |             HASH GROUP BY                      |                          |    638 | 37642 |  1539   (2)| 00:00:19 |    10M|  1830K|          |   17408 |
|  49 |              VIEW                              |                          |    638 | 37642 |  1539   (2)| 00:00:19 |       |       |          |         |
|  50 |               HASH UNIQUE                      |                          |    638 |   122K|  1539   (2)| 00:00:19 |    32M|  3970K|          |   30720 |
|  51 |                NESTED LOOPS                    |                          |    638 |   122K|  1538   (2)| 00:00:19 |       |       |          |         |
|  52 |                 NESTED LOOPS                   |                          |    637 |   111K|  1537   (2)| 00:00:19 |       |       |          |         |
|  53 |                  NESTED LOOPS                  |                          |    637 |    97K|  1536   (2)| 00:00:19 |       |       |          |         |
|  54 |                   NESTED LOOPS                 |                          |    642 | 60990 |  1407   (2)| 00:00:17 |       |       |          |         |
|  55 |                    NESTED LOOPS                |                          |    651 | 26691 |  1147   (2)| 00:00:14 |       |       |          |         |
|* 56 |                     TABLE ACCESS FULL          | PM_BILL                  |      3 |    27 |  1119   (2)| 00:00:14 |       |       |          |         |
|* 57 |                     TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ           |    205 |  6560 |     9   (0)| 00:00:01 |       |       |          |         |
|* 58 |                      INDEX RANGE SCAN          | IDX_PM_ACCOUNT_BIZ_MONTH |    213 |       |     1   (0)| 00:00:01 |       |       |          |         |
|* 59 |                    TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN               |      1 |    54 |     1   (0)| 00:00:01 |       |       |          |         |
|* 60 |                     INDEX RANGE SCAN           | PK_MT_BIZ_FIN            |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|* 61 |                   TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN        |      1 |    62 |     1   (0)| 00:00:01 |       |       |          |         |
|* 62 |                    INDEX RANGE SCAN            | IDX$$_429C0002           |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|* 63 |                  INDEX UNIQUE SCAN             | PK_BS_BIZTYPE            |      1 |    22 |     1   (0)| 00:00:01 |       |       |          |         |
|* 64 |                 INDEX UNIQUE SCAN              | PK_BS_INSURED            |      1 |    18 |     1   (0)| 00:00:01 |       |       |          |         |
|* 65 |            INDEX RANGE SCAN                    | PK_MT_BIZ_FIN            |      1 |       |     1   (0)| 00:00:01 |       |       |          |         |
|* 66 |      INDEX UNIQUE SCAN                         | PK_BS_INSURED            |      1 |     6 |     1   (0)| 00:00:01 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2
   2 - SEL$2        / T@SEL$2
   3 - SEL$2        / T@SEL$2
   4 - SEL$3
   5 - SEL$3        / T@SEL$3
   6 - SEL$3        / T@SEL$3
   7 - SEL$7        / BS@SEL$7
   8 - SEL$7        / BS@SEL$7
   9 - SEL$8        / PP@SEL$8
  10 - SEL$8        / PP@SEL$8
  11 - SEL$15
  12 - SEL$15       / A@SEL$15
  13 - SEL$15       / A@SEL$15
  14 - SEL$4        / T@SEL$4
  15 - SEL$4        / T@SEL$4
  16 - SEL$9        / T@SEL$9
  17 - SEL$9        / T@SEL$9
  18 - SEL$11       / T@SEL$11
  19 - SEL$11       / T@SEL$11
  20 - SEL$10       / Q@SEL$10
  21 - SEL$10       / Q@SEL$10
  22 - SEL$5        / BS_TREAT_TYPE@SEL$5
  23 - SEL$5        / BS_TREAT_TYPE@SEL$5
  24 - SEL$16
  25 - SEL$16       / SS@SEL$16
  26 - SEL$16       / SS@SEL$16
  27 - SEL$17
  28 - SEL$17       / SS@SEL$17
  29 - SEL$17       / SS@SEL$17
  30 - SEL$6        / W@SEL$1
  31 - SEL$6
  35 - SEL$6        / G@SEL$6
  37 - SEL$6        / H@SEL$6
  39 - SEL$8F7BCF6F / STREET@SEL$6
  40 - SEL$8F7BCF6F
  41 - SEL$8F7BCF6F / ST@SEL$12
  42 - SEL$8F7BCF6F / CORP@SEL$12
  44 - SEL$6        / A@SEL$6
  45 - SEL$6        / B@SEL$6
  47 - SEL$6C11BF2C / T@SEL$6
  48 - SEL$6C11BF2C
  49 - SEL$93984FCC / $vm_view@SEL$6C11BF2C
  50 - SEL$93984FCC
  56 - SEL$93984FCC / PM_BILL@SEL$19
  57 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
  58 - SEL$93984FCC / PM_ACCOUNT_BIZ@SEL$18
  59 - SEL$93984FCC / B@SEL$14
  60 - SEL$93984FCC / B@SEL$14
  61 - SEL$93984FCC / C@SEL$14
  62 - SEL$93984FCC / C@SEL$14
  63 - SEL$93984FCC / G@SEL$14
  64 - SEL$93984FCC / J@SEL$14
  65 - SEL$6        / B@SEL$6
  66 - SEL$6        / J@SEL$6

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."VALID_FLAG"='1')
   3 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='430701')
       filter("T"."CENTER_ID"='430701')
   5 - filter("T"."VALID_FLAG"='1')
   6 - access("T"."POLICY_CODE"='can_clinic_flag' AND "T"."CENTER_ID"='0')
       filter("T"."CENTER_ID"='0')
   8 - access("BS"."SEX"=TO_NUMBER(:B1))
  10 - access("PP"."PERS_TYPE"=TO_NUMBER(:B1) AND "PP"."CENTER_ID"=:B2)
  13 - access("A"."INDI_ID"=:B1)
  15 - access("T"."INDI_ID"=:B1)
  17 - access("T"."CENTER_ID"=NVL(:B1,:B2) AND "T"."ICD"=:B3)
  19 - access("T"."SERIAL_APPLY"=:B1)
  21 - access("Q"."CENTER_ID"=NVL(:B1,:B2) AND "Q"."ICD"=:B3)
  23 - access("TREATMENT_TYPE"=:B1 AND "CENTER_ID"=:B2)
  25 - filter("SS"."VALID_FLAG"='1')
  26 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
  28 - filter("SS"."VALID_FLAG"='1')
  29 - access("SS"."HOSPITAL_ID"=:B1 AND "SS"."SERIAL_NO"=:B2)
       filter(("SS"."STAT_TYPE"='001' OR "SS"."STAT_TYPE"='002' OR "SS"."STAT_TYPE"='003'))
  34 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  36 - access("B"."CENTER_ID"="H"."CENTER_ID")
  38 - access("B"."CORP_ID"="STREET"."CORP_ID")
  40 - access("CORP"."AREA_CODE"="ST"."QYBM")
  42 - filter("CORP"."AREA_CODE" IS NOT NULL)
  43 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID")
  45 - filter("B"."VALID_FLAG"='1')
  56 - filter("OUTPAY_BILL_NO"=23)
  57 - filter(("BIZ_TYPE"<>'12' OR ("BIZ_FLAG"=18 AND "BIZ_TYPE"='12')))
  58 - access("MONTH_DECL_SN"="RELA_DECL_SN")
  59 - filter("B"."VALID_FLAG"='1')
  60 - access("B"."HOSPITAL_ID"="HOSPITAL_ID" AND "B"."SERIAL_NO"="SERIAL_NO")
  61 - filter("C"."VALID_FLAG"='1')
  62 - access("B"."HOSPITAL_ID"="C"."HOSPITAL_ID" AND "B"."SERIAL_NO"="C"."SERIAL_NO")
  63 - access("B"."CENTER_ID"="G"."CENTER_ID" AND "B"."BIZ_TYPE"="G"."BIZ_TYPE")
  64 - access("B"."INDI_ID"="J"."INDI_ID")
  65 - access("B"."HOSPITAL_ID"="T"."HOSPITAL_ID" AND "B"."SERIAL_NO"="T"."SERIAL_NO")
  66 - access("B"."INDI_ID"="J"."INDI_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."POLICY_VALUE"[VARCHAR2,200]
   2 - "T"."POLICY_VALUE"[VARCHAR2,200]
   3 - "T".ROWID[ROWID,10]
   4 - "T"."POLICY_VALUE"[VARCHAR2,200]
   5 - "T"."POLICY_VALUE"[VARCHAR2,200]
   6 - "T".ROWID[ROWID,10]
   7 - "BS".ROWID[ROWID,10], "BS"."SEX_NAME"[VARCHAR2,5]
   8 - "BS".ROWID[ROWID,10]
   9 - "PP".ROWID[ROWID,10], "PP"."PERS_NAME"[VARCHAR2,20]
  10 - "PP".ROWID[ROWID,10]
  11 - (#keys=0) MAX("INSR_CODE")[30]
  12 - "INSR_CODE"[VARCHAR2,30]
  13 - "A".ROWID[ROWID,10]
  14 - "T".ROWID[ROWID,10], "T"."SPECIAL_CODE"[NUMBER,22]
  15 - "T".ROWID[ROWID,10]
  16 - "T".ROWID[ROWID,10], "T"."DISEASE"[VARCHAR2,100]
  17 - "T".ROWID[ROWID,10]
  18 - "T".ROWID[ROWID,10], "T"."APPLY_CONTENT"[VARCHAR2,3]
  19 - "T".ROWID[ROWID,10]
  20 - "Q".ROWID[ROWID,10], "Q"."DISEASE"[VARCHAR2,100]
  21 - "Q".ROWID[ROWID,10]
  22 - "BS_TREAT_TYPE".ROWID[ROWID,10], "TREATMENT_NAME"[VARCHAR2,50]
  23 - "BS_TREAT_TYPE".ROWID[ROWID,10]
  24 - (#keys=0) SUM("SS"."PART_SELF_MONEY")[22], SUM("SS"."ALL_SELF_MONEY")[22]
  25 - "SS"."PART_SELF_MONEY"[NUMBER,22], "SS"."ALL_SELF_MONEY"[NUMBER,22]
  26 - "SS".ROWID[ROWID,10]
  27 - (#keys=0) SUM("SS"."MONEY")[22]
  28 - "SS"."MONEY"[NUMBER,22]
  29 - "SS".ROWID[ROWID,10]
  30 - "ROWNO"[NUMBER,22], "HOS_SERIAL"[VARCHAR2,30], "REG_FLAG"[CHARACTER,1], "DISEASE_TYPE"[CHARACTER,1], "HOSPITAL_ID"[VARCHAR2,20],
       "HOSPITAL_NAME"[VARCHAR2,70], "SERIAL_NO"[VARCHAR2,16], "BIZ_TYPE"[VARCHAR2,2], "CASE_ID"[NUMBER,22], "BIZ_STAT"[VARCHAR2,2], "NAME"[VARCHAR2,20],
       "SEX"[VARCHAR2,5], "PERS_TYPE"[VARCHAR2,20], "BEGIN_DATE"[VARCHAR2,10], "END_DATE"[VARCHAR2,10], "FIN_DATE"[VARCHAR2,19], "FINISH_DATE"[VARCHAR2,19],
       "INDI_ID"[NUMBER,22], "INSR_CODE"[VARCHAR2,30], "FIN_STAFF"[VARCHAR2,10], "FIN_MAN"[VARCHAR2,30], "CORP_ID"[NUMBER,22], "IDCARD"[VARCHAR2,25],
       "REG_DATE"[VARCHAR2,19], "IN_DAYS"[NUMBER,22], "DISTRICT_CODE"[VARCHAR2,6], "CENTER_ID"[VARCHAR2,10], "OFFICE_GRADE"[VARCHAR2,3],
       "CORP_NAME"[VARCHAR2,70], "DISEASE"[VARCHAR2,100], "IN_AREA_NAME"[VARCHAR2,20], "IN_DEPT_NAME"[VARCHAR2,20], "IN_BED"[VARCHAR2,10],
       "BED_TYPE"[CHARACTER,1], "PATIENT_ID"[VARCHAR2,20], "REMARK"[VARCHAR2,1000], "POS_CODE"[VARCHAR2,10], "REIMBURSE_FLAG"[CHARACTER,1],
       "FIN_DISEASE"[VARCHAR2,100], "FOREGIFT"[NUMBER,1], "IC_NO"[VARCHAR2,25], "TREATMENT_TYPE"[VARCHAR2,3], "APPLY_CONTENT"[VARCHAR2,3],
       "REG_MAN"[VARCHAR2,20], "W"."AREA_CODE"[VARCHAR2,10], "FEES"[NUMBER,22], "PAY_MONEY_JD"[NUMBER,22], "FUND_MONEY"[NUMBER,22], "FUND_301"[NUMBER,22],
       "FUND_003"[NUMBER,22], "QFX"[NUMBER,22], "HOSP_ZF"[NUMBER,22], "CENTER_ZF"[NUMBER,22], "YW_FUND_MONEY"[NUMBER,22], "BC_FUND_MONEY"[NUMBER,22],
       "PAY_MONEY_ALLSELF"[NUMBER,22], "PAY_MONEY_SELF"[NUMBER,22], "PAY_MONEY_E00SELF"[NUMBER,22], "PAY_MONEY_Z00SELF"[NUMBER,22],
       "PAY_MONEY_S00SELF"[NUMBER,22], "PAY_MONEY_S01SELF"[NUMBER,22], "PAY_MONEY_C000SELF"[NUMBER,22], "PAY_MONEY_C001SELF"[NUMBER,22],
       "PAY_MONEY_C004SELF"[NUMBER,22], "PAY_MONEY_C006SELF"[NUMBER,22], "PAY_MONEY_C007SELF"[NUMBER,22], "PAY_MONEY_C007003SELF"[NUMBER,22],
       "DB_MONEY"[NUMBER,22], "YWSH_DB_MONEY"[NUMBER,1], "TC_MONEY"[NUMBER,22], "TC_MONEY_XE"[NUMBER,22], "HOSP_PAY"[NUMBER,22], "HOSP_PRISE"[NUMBER,22],
       "JMTC_MONEY"[NUMBER,22], "YW_MONEY"[NUMBER,22], "JMYW_MONEY"[NUMBER,22], "ACCT_MONEY"[NUMBER,22], "LX_MONEY"[NUMBER,22], "SY_MONEY"[NUMBER,22],
       "OFFI_MONEY"[NUMBER,22], "ZHAOGU_PAY"[NUMBER,22], "PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
       "REG_INFO"[VARCHAR2,10], "DB_MONEY_JUMIN"[NUMBER,22]
  31 - (#keys=1) ROWNUM[22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."REG_FLAG"[CHARACTER,1],
       "B"."DISEASE_TYPE"[CHARACTER,1], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."BIZ_TYPE"[VARCHAR2,2], "B"."CASE_ID"[NUMBER,22], "G"."BIZ_STAT"[VARCHAR2,2], "B"."NAME"[VARCHAR2,20], [5], [20],
       TO_CHAR(INTERNAL_FUNCTION("B"."BEGIN_DATE"),'yyyy-mm-dd')[10], TO_CHAR(INTERNAL_FUNCTION("B"."END_DATE"),'yyyy-mm-dd')[10],
       TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], TO_CHAR(INTERNAL_FUNCTION("B"."FIN_DATE"),'yyyy-mm-dd hh24:mi:ss')[19],
       "B"."INDI_ID"[NUMBER,22], "T"."INSR_CODE"[VARCHAR2,30], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CORP_ID"[NUMBER,22],
       "B"."IDCARD"[VARCHAR2,25], TO_CHAR(INTERNAL_FUNCTION("B"."REG_DATE"),'yyyy-mm-dd hh24:mi:ss')[19], NVL("B"."IN_DAYS",0)[22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."CENTER_ID"[VARCHAR2,10], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."CORP_NAME"[VARCHAR2,70],
       "B"."IN_DISEASE"[VARCHAR2,20], [100], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10],
','')[1000], "B"."POS_CODE"[VARCHAR2,10],PATIENT_ID"[VARCHAR2,20], TRANSLATE("B"."REMARK",'
       "B"."REIMBURSE_FLAG"[CHARACTER,1], [100], 0[1], "B"."IC_NO"[VARCHAR2,25], "B"."TREATMENT_TYPE"[VARCHAR2,3], [3], "B"."REG_MAN"[VARCHAR2,20],
       "STREET"."QYBM"[VARCHAR2,10], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
       "T"."FUND_003"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
       "T"."BC_FUND_MONEY"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
       "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
       "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"-"T"."YWSH_DB_MONEY"[22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22],
       "T"."TC_MONEY_XE"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22],
       "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22],
       "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22],
       "B"."REG_INFO"[VARCHAR2,10]
  32 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
       "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
       "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
       "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
       "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
       "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
       "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
       "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
       "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
       "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
       "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
       "B"."FIN_MAN"[VARCHAR2,30], ROWNUM[4]
  33 - "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
       "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
       "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
       "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
       "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
       "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
       "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
       "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
       "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
       "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
       "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
       "B"."FIN_MAN"[VARCHAR2,30]
  34 - (#keys=2) "B"."CENTER_ID"[VARCHAR2,10], "B"."BIZ_TYPE"[VARCHAR2,2], "G"."BIZ_STAT"[VARCHAR2,2], "H"."CENTER_ID"[VARCHAR2,10],
       "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10],
       "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
       "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
       "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
       "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
       "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
       "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
       "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
       "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
       "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
       "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
       "B"."FIN_MAN"[VARCHAR2,30]
  35 - "G"."CENTER_ID"[VARCHAR2,10], "G"."BIZ_TYPE"[CHARACTER,2], "G"."BIZ_STAT"[VARCHAR2,2]
  36 - (#keys=1) "H"."CENTER_ID"[VARCHAR2,10], "B"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10], "B"."CORP_ID"[NUMBER,22],
       "STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
       "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
       "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
       "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
       "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
       "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
       "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
       "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
       "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
       "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
       "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30],
       "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
       "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
       "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
       "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
       "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
  37 - "H"."CENTER_ID"[VARCHAR2,10], "H"."CATALOG_CENTER"[VARCHAR2,10]
  38 - (#keys=1) "B"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10], "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70],
       "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
       "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
       "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
       "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
       "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22],
       "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22],
       "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22],
       "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22],
       "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6],
       "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25],
       "B"."IC_NO"[VARCHAR2,25], "B"."HOS_SERIAL"[VARCHAR2,30], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22],
       "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10],
       "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20],
       "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1],
       "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30],
       "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3]
  39 - "STREET"."CORP_ID"[NUMBER,22], "STREET"."QYBM"[VARCHAR2,10]
  40 - (#keys=1) "ST"."QYBM"[VARCHAR2,10], "CORP"."CORP_ID"[NUMBER,22]
  41 - "ST"."QYBM"[VARCHAR2,10]
  42 - "CORP"."CORP_ID"[NUMBER,22], "CORP"."AREA_CODE"[VARCHAR2,20]
  43 - (#keys=1) "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22],
       "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22],
       "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22],
       "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22],
       "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22],
       "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22],
       "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22],
       "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22],
       "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22],
       "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22], "B"."DISEASE_TYPE"[CHARACTER,1], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20],
       "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3], "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22],
       "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3], "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20],
       "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7], "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20],
       "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1], "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22],
       "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7], "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500],
       "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10], "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3],
       "B"."HOS_SERIAL"[VARCHAR2,30]
  44 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."HOSPITAL_NAME"[VARCHAR2,70]
  45 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."CASE_ID"[NUMBER,22], "B"."BIZ_TYPE"[VARCHAR2,2],
       "B"."DISTRICT_CODE"[VARCHAR2,6], "B"."INDI_ID"[NUMBER,22], "B"."NAME"[VARCHAR2,20], "B"."SEX"[CHARACTER,1], "B"."OFFICE_GRADE"[VARCHAR2,3],
       "B"."IDCARD"[VARCHAR2,25], "B"."IC_NO"[VARCHAR2,25], "B"."CORP_ID"[NUMBER,22], "B"."CORP_NAME"[VARCHAR2,70], "B"."TREATMENT_TYPE"[VARCHAR2,3],
       "B"."SERIAL_APPLY"[NUMBER,22], "B"."REG_DATE"[DATE,7], "B"."REG_MAN"[VARCHAR2,20], "B"."REG_FLAG"[CHARACTER,1], "B"."BEGIN_DATE"[DATE,7],
       "B"."REG_INFO"[VARCHAR2,10], "B"."IN_DEPT_NAME"[VARCHAR2,20], "B"."IN_AREA_NAME"[VARCHAR2,20], "B"."IN_BED"[VARCHAR2,10], "B"."BED_TYPE"[CHARACTER,1],
       "B"."PATIENT_ID"[VARCHAR2,20], "B"."IN_DISEASE"[VARCHAR2,20], "B"."IN_DAYS"[NUMBER,22], "B"."FIN_DISEASE"[VARCHAR2,20], "B"."END_DATE"[DATE,7],
       "B"."REIMBURSE_FLAG"[CHARACTER,1], "B"."POS_CODE"[VARCHAR2,10], "B"."REMARK"[VARCHAR2,500], "B"."FIN_DATE"[DATE,7], "B"."FIN_STAFF"[VARCHAR2,10],
       "B"."FIN_MAN"[VARCHAR2,30], "B"."CENTER_ID"[VARCHAR2,10], "B"."PERS_TYPE_DETAIL"[VARCHAR2,3], "B"."HOS_SERIAL"[VARCHAR2,30],
       "B"."DISEASE_TYPE"[CHARACTER,1]
  46 - "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22], "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22],
       "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22], "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22],
       "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22], "T"."PAY_MONEY_E00SELF"[NUMBER,22],
       "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22], "T"."PAY_MONEY_C000SELF"[NUMBER,22],
       "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22], "T"."PAY_MONEY_C007SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1], "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22],
       "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22], "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22],
       "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22], "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22],
       "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22], "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22],
       "SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
  47 - "T"."HOSPITAL_ID"[VARCHAR2,20], "T"."SERIAL_NO"[VARCHAR2,16], "T"."INSR_CODE"[VARCHAR2,30], "T"."FEES"[NUMBER,22], "T"."PAY_MONEY_JD"[NUMBER,22],
       "T"."FUND_MONEY"[NUMBER,22], "T"."FUND_301"[NUMBER,22], "T"."FUND_003"[NUMBER,22], "T"."YW_FUND_MONEY"[NUMBER,22], "T"."BC_FUND_MONEY"[NUMBER,22],
       "T"."QFX"[NUMBER,22], "T"."HOSP_ZF"[NUMBER,22], "T"."CENTER_ZF"[NUMBER,22], "T"."PAY_MONEY_ALLSELF"[NUMBER,22], "T"."PAY_MONEY_SELF"[NUMBER,22],
       "T"."PAY_MONEY_E00SELF"[NUMBER,22], "T"."PAY_MONEY_Z00SELF"[NUMBER,22], "T"."PAY_MONEY_S00SELF"[NUMBER,22], "T"."PAY_MONEY_S01SELF"[NUMBER,22],
       "T"."PAY_MONEY_C000SELF"[NUMBER,22], "T"."PAY_MONEY_C001SELF"[NUMBER,22], "T"."PAY_MONEY_C004SELF"[NUMBER,22], "T"."PAY_MONEY_C006SELF"[NUMBER,22],
       "T"."PAY_MONEY_C007SELF"[NUMBER,22], "T"."PAY_MONEY_C007003SELF"[NUMBER,22], "T"."DB_MONEY"[NUMBER,22], "T"."YWSH_DB_MONEY"[NUMBER,1],
       "T"."TC_MONEY"[NUMBER,22], "T"."HOSP_PAY"[NUMBER,22], "T"."HOSP_PRISE"[NUMBER,22], "T"."TC_MONEY_XE"[NUMBER,22], "T"."JMTC_MONEY"[NUMBER,22],
       "T"."YW_MONEY"[NUMBER,22], "T"."JMYW_MONEY"[NUMBER,22], "T"."ACCT_MONEY"[NUMBER,22], "T"."LX_MONEY"[NUMBER,22], "T"."SY_MONEY"[NUMBER,22],
       "T"."OFFI_MONEY"[NUMBER,22], "T"."ZHAOGU_PAY"[NUMBER,22], "T"."PAY_MONEY_XZF"[NUMBER,22], "PAY_MONEY_DNZF"[NUMBER,22], "MEDI_PAY"[NUMBER,22],
       "MEDI_ZFY"[NUMBER,22], "T"."DB_MONEY_JUMIN"[NUMBER,22]
  48 - "$vm_col_9"[VARCHAR2,20], "$vm_col_10"[VARCHAR2,16], "$vm_col_8"[NUMBER,22], SUM(DECODE("$vm_col_6",'803',"$vm_col_5",0))[22], SUM(CASE  WHEN
       (("$vm_col_4"='C000' OR "$vm_col_4"='C001' OR "$vm_col_4"='C002' OR "$vm_col_4"='C003' OR "$vm_col_4"='C004') AND ("$vm_col_6"='999' OR
       "$vm_col_6"='003')) THEN "$vm_col_5" ELSE 0 END )[22], SUM(DECODE("$vm_col_6",'999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_6",'401',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'301',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'511',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_6",'202',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'003',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'802',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_6",'901',"$vm_col_5",'802',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'801',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_6",'001',"$vm_col_5",'801',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'996',NVL("$vm_col_5",0),0))[22],
       SUM(DECODE("$vm_col_6",'201',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C007003',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_4"||"$vm_col_6",'C007999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C006003',"$vm_col_5",'C006999',"$vm_col_5",'C0070
       03',"$vm_col_5",'C007999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C004003',"$vm_col_5",'C004999',"$vm_col_5",'C005003',"$vm_col_5",'C00
       5999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C001003',"$vm_col_5",'C001999',"$vm_col_5",'C002003',"$vm_col_5",'C002999',"$vm_col_5",'C
       003003',"$vm_col_5",'C003999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4"||"$vm_col_6",'C000003',"$vm_col_5",'C000999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_4"||"$vm_col_6",'S02003',"$vm_col_5",'S02999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_4"||"$vm_col_6",'S01003',"$vm_col_5",'S01999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_4"||"$vm_col_6",'S00003',"$vm_col_5",'S00999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_4"||"$vm_col_6",'Z00003',"$vm_col_5",'Z00999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_4"||"$vm_col_6",'E00003',"$vm_col_5",'E00999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_6",'102003',"$vm_col_5",'102999',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_3"||"$vm_col_6",'101003',"$vm_col_5",'101999',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'998',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_6",'996',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_4",'S01',"$vm_col_5",'S02',"$vm_col_5",0))[22],
       SUM(DECODE("$vm_col_6",'306',"$vm_col_5",0))[22], SUM(DECODE("$vm_col_6",'001',"$vm_col_5",'511',"$vm_col_5",'202',"$vm_col_5",'801',"$vm_col_5",0))[22],
        SUM(DECODE("$vm_col_4",'C000',"$vm_col_5",'C001',"$vm_col_5",'C002',"$vm_col_5",'C003',"$vm_col_5",'C004',"$vm_col_5",'C005',"$vm_col_5",'C006',"$vm_col
       _5",'C007',"$vm_col_5",0))[22], SUM("$vm_col_5")[22]
  49 - "$vm_col_9"[VARCHAR2,20], "$vm_col_10"[VARCHAR2,16], "$vm_col_8"[NUMBER,22], "$vm_col_3"[VARCHAR2,3], "$vm_col_4"[VARCHAR2,20],
       "$vm_col_5"[NUMBER,22], "$vm_col_6"[VARCHAR2,3]
  50 - "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."INDI_ID"[NUMBER,22], ROWID[ROWID,10], ROWID[ROWID,10], ROWID[ROWID,10],
       ROWID[ROWID,10], "SERIAL_NO"[VARCHAR2,16], "HOSPITAL_ID"[VARCHAR2,20], "C"."LABEL_FLAG"[VARCHAR2,3], "C"."POLICY_ITEM_CODE"[VARCHAR2,20],
       "C"."REAL_PAY"[NUMBER,22], "C"."FUND_ID"[VARCHAR2,3], "FINISH_FLAG"[CHARACTER,1]
  51 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
       "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10], ROWID[ROWID,10]
  52 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3],
       "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3], ROWID[ROWID,10]
  53 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10], ROWID[ROWID,10],
       "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
  54 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16], ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16],
       "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22], "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
  55 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16]
  56 - "RELA_DECL_SN"[NUMBER,22]
  57 - "HOSPITAL_ID"[VARCHAR2,20], "SERIAL_NO"[VARCHAR2,16]
  58 - "PM_ACCOUNT_BIZ".ROWID[ROWID,10]
  59 - ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16], "B"."BIZ_TYPE"[VARCHAR2,2], "B"."INDI_ID"[NUMBER,22],
       "FINISH_FLAG"[CHARACTER,1], "B"."CENTER_ID"[VARCHAR2,10]
  60 - ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]
  61 - ROWID[ROWID,10], "C"."POLICY_ITEM_CODE"[VARCHAR2,20], "C"."FUND_ID"[VARCHAR2,3], "C"."REAL_PAY"[NUMBER,22], "C"."LABEL_FLAG"[VARCHAR2,3]
  62 - ROWID[ROWID,10]
  63 - ROWID[ROWID,10]
  64 - ROWID[ROWID,10]
  65 - "SYS_ALIAS_10".ROWID[ROWID,10], "B"."HOSPITAL_ID"[VARCHAR2,20], "B"."SERIAL_NO"[VARCHAR2,16]

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


458 rows selected.

修改后执行时间为几秒钟,在业务需求可接受范围内,到此达到了优化目标。引起问题的原因很简单,就是开发人员根本不知道谓词条件where (a||b) in(select a||b from c)与where (a,b) in(select a,b from c)之间的差别,差别在于,如果存在复合索引(a,b..), 那么(a||b)这种写法用不了索引,当表的数据量很大时它与使用索引的写法(a,b)性能差异是巨大的。

Proudly powered by WordPress | Indrajeet by Sus Hill.