在程序的SQL语句使用了/*+ rule */提示，就会让优化器使用RBO，不使用CBO。 这样使得优化器少了许多选择CBO高效执行计划的机会。当我们去掉/*+ rule */，使用CBO时其执行结果如下：
SQL> select
2 a.stat_type,
3 a.his_item_code,
4 a.his_item_name,
5 a.item_code,
6 max((select count(*)
7 from mt_fee_fin aa
8 where a.hospital_id = aa.hospital_id
9 and a.serial_no = aa.serial_no
10 and a.item_code = aa.item_code)) as item_sn,
11 a.item_name,
12 a.medi_item_type,
13 a.price,
14 sum(a.dosage) as dosage,
15 a.model,
16 replace(a.standard, ' ', '') as standard,
17 sum(a.money) as money,
18 sum(nvl(d.audit_money, 0)) as audit_money,
19 d.hosp_reason_staff as hosp_reason_staff,
20 d.hosp_reason_date as hosp_reason_date,
21 d.hosp_reason_staffid as hosp_reason_staffid,
22 d.hosp_reason as hosp_reason,
23 d.center_resualt as center_resualt,
24 d.center_flag as center_flag,
25 d.audit_reason_id as audit_reason_id,
26 sum(nvl(b.all_cash, 0)) as all_cash,
27 (case
28 when a.medi_item_type = '0' then
29 (SELECT bo_flag
30 FROM bs_item
31 WHERE bs_item.item_code = a.item_code
32 AND ROWNUM < 2)
33 else
34 (SELECT bo_flag
35 FROM bs_medi
36 WHERE bs_medi.medi_code = a.item_code
37 AND ROWNUM < 2)
38 end) as bo_flag,
39 sum(nvl(b.part_cash, 0)) as part_cash,
40 decode(nvl(d.audit_reason_id, 0),
41 0,
42 d.audit_reason,
43 '%%' || to_char(d.audit_reason_id) || '%%') as audit_reason
44 from mt_fee_fin a,
45 pm_account_biz c,
46 pm_fee_audit d,
47 (select hospital_id,
48 serial_no,
49 policy_item_code,
50 serial_fee,
51 fee_batch,
52 SUM(decode(fund_id,
53 '999',
54 decode(b.label_flag, '101', real_pay, 0),
55 '003',
56 decode(label_flag, '101', real_pay, 0),
57 0)) AS all_cash,
58 SUM(decode(fund_id,
59 '999',
60 decode(b.label_flag, '102', real_pay, 0),
61 '003',
62 decode(label_flag, '102', real_pay, 0),
63 0)) AS part_cash
64 from mt_pay_record_fin b
65 where b.hospital_id = '4307210003'
66 and b.serial_no = '25735455'
67 and serial_fee <> 0
68 and valid_flag = '1'
69 group by hospital_id,
70 serial_no,
71 policy_item_code,
72 serial_fee,
73 fee_batch) b
74 where a.hospital_id = c.hospital_id
75 and a.serial_no = c.serial_no
76 and a.hospital_id = '4307210003'
77 and a.serial_no = '25735455'
78 and a.hospital_id = b.hospital_id(+)
79 and a.serial_fee = b.serial_fee(+)
80 and a.serial_no = b.serial_no(+)
81 and a.fee_batch = b.fee_batch(+)
82 and a.valid_flag = '1'
83 and c.valid_flag = '1'
84 and d.audit_staff_id(+) = 2103
85 and d.AUDIT_PHASE(+) = '1'
86 and d.serial_fee(+) <> 0
87 and a.serial_fee = d.serial_fee(+)
88 and d.account_id(+) = 16905170
89 and c.account_id = 16905170
90 group by a.stat_type,
91 a.item_name,
92 a.his_item_name,
93 a.price,
94 a.his_item_code,
95 a.item_code,
96 a.medi_item_type,
97 a.model,
98 a.standard,
99 d.hosp_reason,
100 d.center_resualt,
101 d.center_flag,
102 d.hosp_reason_staff,
103 d.hosp_reason_date,
104 d.hosp_reason_staffid,
105 d.audit_reason_id,
106 d.audit_reason
107 Order By a.stat_type, a.item_name, a.his_item_name
108 ;
....省略...
277 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1363767461