/* Formatted on 2014/03/18 09:48 (Formatter Plus v4.8.8) */
SELECT c.fee_type_desc, i.order_code, b.chn_name AS rexp_code_name,
i.rexp_code,
(CASE (SUBSTR ((f.order_name || '/' || f.SPECIFICATION),
LENGTH (f.order_name || '/' || f.SPECIFICATION),
LENGTH (f.order_name || '/' || f.SPECIFICATION)
)
)
WHEN '/'
THEN SUBSTR ((f.order_name || '/' || f.SPECIFICATION),
0,
LENGTH ((f.order_name || '/' || f.SPECIFICATION
)
)
- 1
)
ELSE (f.order_name || '/' || f.SPECIFICATION)
END
) AS order_name,
SUM (i.dosage_qty) AS dosage_qty, f.unit_code AS dosage_unit,
i.own_price, SUM (i.tot_amt) AS tot_amt
FROM icsi_ordd i, base_fee f, base_dictionary b, ins_fee_type c
WHERE i.bill_no IN ('201401241636045510')
AND i.hosp_area = '001'
AND i.hosp_area = f.hosp_area
AND i.order_code = f.order_code
AND b.GROUP_ID = 'SYS_CHARGE'
AND b.ID = i.rexp_code
AND (i.setmain_flg = 'N' OR i.setmain_flg IS NULL)
AND i.tot_amt <> 0
AND f.inspay_type = c.fee_type_code(+)
AND c.starline_flg(+) = 'N'
AND c.plan_flg(+) = 'N'
AND c.item_flg(+) = 'Y'
GROUP BY i.rexp_code,
b.chn_name,
i.order_code,
c.fee_type_desc,
(CASE (SUBSTR ((f.order_name || '/' || f.SPECIFICATION),
LENGTH (f.order_name || '/' || f.SPECIFICATION),
LENGTH (f.order_name || '/' || f.SPECIFICATION)
)
)
WHEN '/'
THEN SUBSTR ((f.order_name || '/' || f.SPECIFICATION),
0,
LENGTH ((f.order_name || '/' || f.SPECIFICATION
)
)
- 1
)
ELSE (f.order_name || '/' || f.SPECIFICATION)
END
),
f.unit_code,
i.own_price
ORDER BY i.rexp_code, i.order_code
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 137 | 1244 (1)| 00
| 1 | SORT GROUP BY | | 1 | 137 | 1244 (1)| 00
| 2 | NESTED LOOPS OUTER | | 1 | 137 | 1243 (0)| 00
| 3 | NESTED LOOPS | | 1 | 118 | 1242 (0)| 00
| 4 | NESTED LOOPS | | 1 | 77 | 1241 (0)| 00
| 5 | TABLE ACCESS BY INDEX ROWID | BASE_DICTIONARY | 6 | 168 | 4 (0)| 00
|* 6 | INDEX RANGE SCAN | PK_BASE_DICTIONARY | 6 | | 2 (0)| 00
|* 7 | TABLE ACCESS BY INDEX ROWID | ICSI_ORDD | 1 | 49 | 1241 (0)| 00
| 8 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | REXP_CODE | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | BASE_FEE | 1 | 41 | 1 (0)| 00
|* 11 | INDEX UNIQUE SCAN | BASE_FEE_PK | 1 | | 0 (0)| 00
|* 12 | TABLE ACCESS BY INDEX ROWID | INS_FEE_TYPE | 1 | 19 | 1 (0)| 00
|* 13 | INDEX UNIQUE SCAN | PK_INS_FEE_TYPE | 1 | | 0 (0)| 00
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("B"."GROUP_ID"='SYS_CHARGE')
7 - filter("I"."BILL_NO"='201401241636045510' AND "I"."TOT_AMT"<>0 AND
("I"."SETMAIN_FLG"='N' OR "I"."SETMAIN_FLG" IS NULL) AND "I"."HOSP_AREA"='001')
9 - access("B"."ID"="I"."REXP_CODE")
11 - access("F"."HOSP_AREA"='001' AND "I"."ORDER_CODE"="F"."ORDER_CODE")
12 - filter("C"."ITEM_FLG"(+)='Y' AND "C"."PLAN_FLG"(+)='N' AND "C"."STARLINE_FLG"(+)='N')
13 - access("F"."INSPAY_TYPE"="C"."FEE_TYPE_CODE"(+))
BITMAP
CREATE BITMAP INDEX BJCY.REXP_CODE ON BJCY.ICSI_ORDD
REXP_CODE是个注记 就是不同的状态代码 123 所以肯定有大量重复
干掉BITMAP 索引,创建组合索引
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 137 | 45 (3)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 137 | 45 (3)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 137 | 44 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 118 | 43 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 77 | 42 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| BASE_DICTIONARY | 6 | 168 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_BASE_DICTIONARY | 6 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| ICSI_ORDD | 1 | 49 | 18 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | REXP_CODE2 | 19 | | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | BASE_FEE | 1 | 41 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | BASE_FEE_PK | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | INS_FEE_TYPE | 1 | 19 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_INS_FEE_TYPE | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("B"."GROUP_ID"='SYS_CHARGE')
7 - filter("I"."TOT_AMT"<>0 AND ("I"."SETMAIN_FLG"='N' OR "I"."SETMAIN_FLG" IS NULL))
8 - access("I"."BILL_NO"='201401241636045510' AND "I"."HOSP_AREA"='001' AND
"B"."ID"="I"."REXP_CODE")
filter("I"."HOSP_AREA"='001' AND "B"."ID"="I"."REXP_CODE")
10 - access("F"."HOSP_AREA"='001' AND "I"."ORDER_CODE"="F"."ORDER_CODE")
11 - filter("C"."ITEM_FLG"(+)='Y' AND "C"."PLAN_FLG"(+)='N' AND "C"."STARLINE_FLG"(+)='N')
12 - access("F"."INSPAY_TYPE"="C"."FEE_TYPE_CODE"(+))
这回走索引了