SELECT *
FROM STORESUM
WHERE (ORG_ID IN (SELECT EP_LOC.ORG_ID
FROM EP_LOC, EP_USER_LOC
WHERE EP_LOC.LOC_ID = EP_USER_LOC.LOC_ID
AND EP_USER_LOC.USER_ID = :1) AND
((STORE_ID IN (SELECT STORE_ID FROM STOREMAS_LOC WHERE LOC_ID = :2)) OR
(STORE_ID IN
(SELECT STORE_ID FROM EP_USER_STORE WHERE USER_ID = :3))))
ORDER BY STORE_ID DESC, STK_ID ASC
xabd@RBDBON8> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2885993211
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 172K| 84M| | 31588 (1)| 00:06:20 |
| 1 | SORT GROUP BY | | 172K| 84M| 89M| 31588 (1)| 00:06:20 |
|* 2 | HASH JOIN RIGHT OUTER | | 172K| 84M| | 12919 (1)| 00:02:36 |
| 3 | TABLE ACCESS FULL | INVCOST | 29969 | 1346K| | 103 (0)| 00:00:02 |
|* 4 | HASH JOIN | | 172K| 76M| | 12815 (1)| 00:02:34 |
| 5 | TABLE ACCESS FULL | STOREMAS | 466 | 11184 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 194K| 81M| 11M| 12809 (1)| 00:02:34 |
| 7 | TABLE ACCESS FULL | STKMAS | 34131 | 11M| | 755 (1)| 00:00:10 |
|* 8 | HASH JOIN RIGHT SEMI | | 194K| 15M| | 10565 (1)| 00:02:07 |
| 9 | VIEW | VW_NSO_2 | 3 | 18 | | 5 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | | |
| 11 | NESTED LOOPS | | 3 | 87 | | 5 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | UNQ_EP_USER_LOC | 3 | 45 | | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | UNQ_EP_LOC | 1 | | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID| EP_LOC | 1 | 14 | | 1 (0)| 00:00:01 |
|* 15 | HASH JOIN | | 583K| 43M| | 10558 (1)| 00:02:07 |
| 16 | VIEW | VW_NSO_1 | 73 | 584 | | 6 (0)| 00:00:01 |
| 17 | HASH UNIQUE | | 73 | 1099 | | 6 (34)| 00:00:01 |
| 18 | UNION-ALL | | | | | | |
|* 19 | INDEX RANGE SCAN | UNQ_EP_USER_STORE | 69 | 1035 | | 2 (0)| 00:00:01 |
|* 20 | INDEX FAST FULL SCAN | UNQ_STOREMAS_LOC | 4 | 64 | | 4 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | INV_SUMMARY | 2327K| 157M| | 10545 (1)| 00:02:07 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."STK_ID"="C"."STK_ID"(+) AND "A"."ORG_ID"="C"."ORG_ID"(+) AND
"A"."VALAREA_ID"="C"."VALAREA_ID"(+) AND "A"."BATCH_ID1"="C"."BATCH_ID1"(+) AND
"A"."BATCH_ID2"="C"."BATCH_ID2"(+) AND "A"."BATCH_ID3"="C"."BATCH_ID3"(+) AND
"A"."BATCH_ID4"="C"."BATCH_ID4"(+) AND "A"."SRN_ID"="C"."SRN_ID"(+))
4 - access("A"."STORE_ID"="D"."STORE_ID")
6 - access("A"."STK_ID"="B"."STK_ID")
8 - access("A"."ORG_ID"="ORG_ID")
12 - access("EP_USER_LOC"."USER_ID"=:1)
13 - access("EP_LOC"."LOC_ID"="EP_USER_LOC"."LOC_ID")
15 - access("A"."STORE_ID"="STORE_ID")
19 - access("USER_ID"=:3)
20 - filter("LOC_ID"=:2)
44 rows selected.
xabd@RBDBON8> select count(*) from STOREMAS;
COUNT(*)
----------
475
xabd@RBDBON8> select count(*) from STKMAS;
COUNT(*)
----------
34204
xabd@RBDBON8> select count(*) from INV_SUMMARY;
COUNT(*)
----------
2384596
OWNER SEGMENT_NAME PART_SIZE_MB TAB_SIZE_MB STA LAST_ANALYZED PART_NUM_ROWS SAMPLE_CNT ESTIMATE_PCT TAB_NUM_ROWS
---------- ------------ ------------ ----------- --- ------------------- ------------- ---------- ------------ ------------
XABD INV_SUMMARY 368 368 NO 2014-10-28 22:16:43 2327317 2327317 100 2327317
XABD STKMAS 22 22 NO 2014-10-26 18:06:39 34131 34131 100 34131
XABD INVCOST 3 3 NO 2014-11-10 22:15:27 29969 29969 100 29969
XABD EP_LOC .1875 .1875 NO 2014-10-31 22:20:12 427 427 100 427
XABD STOREMAS .125 .125 NO 2014-10-09 22:21:19 466 466 100 466
storesum 是个视图
CREATE OR REPLACE VIEW XABD.STORESUM AS
SELECT SUM(A.REC_KEY),
A.ORG_ID,
D.DEF_WH_ID,
A.STORE_ID,
D.VALAREA_ID,
A.STK_ID,
B.NAME,
B.MODEL,
B.UOM_ID,
B.STATUS_FLG,
B.TYPE,
B.SOURCE,
B.COST_TYPE,
B.BRAND_ID,
B.CAT1_ID,
B.CAT2_ID,
B.CAT3_ID,
B.CAT4_ID,
B.CAT5_ID,
B.CAT6_ID,
B.CAT7_ID,
B.CAT8_ID,
EP_SALESPB.get_mas_list_price(a.org_id,
0,
a.stk_id,
trunc(sysdate),
'*',
'*',
'*',
'*',
'*'),
EP_SALESPB.get_mas_price(a.org_id,
0,
a.stk_id,
trunc(sysdate),
'*',
'*',
'*',
'*',
'*'),
ep_misc.get_std_cost_loc(a.org_id, a.store_id, a.stk_id),
EP_SALESPB.get_mas_retail_list_price(a.org_id,
0,
a.stk_id,
trunc(sysdate),
'*',
'*',
'*',
'*',
'*'),
EP_SALESPB.get_mas_retail_price(a.org_id,
0,
a.stk_id,
trunc(sysdate),
'*',
'*',
'*',
'*',
'*'),
B.REF1,
B.REF2,
B.REF3,
B.REF4,
B.REF5,
B.REF6,
B.REF7,
B.REF8,
B.REF9,
B.REF10,
B.REF11,
B.REF12,
B.REF13,
B.REF14,
B.REF15,
B.REF16,
SUM(A.DRCR_FLG * A.STK_QTY),
SUM(A.DRCR_FLG * A.STK_VALUE),
SUM(A.DRCR_FLG * A.STK_QTY * C.UNIT_COST),
SUM(A.DRCR_FLG * A.TRN_STK_VALUE),
SUM(A.DRCR_FLG * A.STK_QTY * C.TRN_UNIT_COST),
SUM(A.DRCR_FLG * A.STK_QTY *
ep_misc.get_last_cal_unit_cost(a.org_id, a.store_id, a.stk_id)),
DECODE(SUM(A.DRCR_FLG * A.STK_QTY),
0,
0,
SUM(A.DRCR_FLG * A.STK_VALUE) / SUM(A.DRCR_FLG * A.STK_QTY)),
DECODE(SUM(A.DRCR_FLG * A.STK_QTY),
0,
0,
SUM(A.DRCR_FLG * A.TRN_STK_VALUE) /
SUM(A.DRCR_FLG * A.STK_QTY)),
DECODE(SUM(A.DRCR_FLG * A.STK_QTY),
0,
0,
SUM(A.DRCR_FLG * A.STK_QTY * C.UNIT_COST) /
SUM(A.DRCR_FLG * A.STK_QTY)),
DECODE(SUM(A.DRCR_FLG * A.STK_QTY),
0,
0,
SUM(A.DRCR_FLG * A.STK_QTY * C.TRN_UNIT_COST) /
SUM(A.DRCR_FLG * A.STK_QTY)),
ep_misc.get_last_cal_unit_cost(a.org_id, a.store_id, a.stk_id)
FROM INV_SUMMARY A, STKMAS B, INVCOST C, STOREMAS D
WHERE A.STK_ID = B.STK_ID
AND A.STORE_ID = D.STORE_ID
AND A.STK_ID = C.STK_ID(+)
AND A.ORG_ID = C.ORG_ID(+)
AND A.VALAREA_ID = C.VALAREA_ID(+)
AND A.BATCH_ID1 = C.BATCH_ID1(+)
AND A.BATCH_ID2 = C.BATCH_ID2(+)
AND A.BATCH_ID3 = C.BATCH_ID3(+)
AND A.BATCH_ID4 = C.BATCH_ID4(+)
AND A.SRN_ID = C.SRN_ID(+)
GROUP BY A.ORG_ID,
D.DEF_WH_ID,
A.STORE_ID,
D.VALAREA_ID,
A.STK_ID,
B.NAME,
B.MODEL,
B.UOM_ID,
B.STATUS_FLG,
B.TYPE,
B.SOURCE,
B.COST_TYPE,
B.BRAND_ID,
B.CAT1_ID,
B.CAT2_ID,
B.CAT3_ID,
B.CAT4_ID,
B.CAT5_ID,
B.CAT6_ID,
B.CAT7_ID,
B.CAT8_ID,
B.LIST_PRICE,
B.NET_PRICE,
B.STD_COST,
B.RETAIL_LIST_PRICE,
B.RETAIL_NET_PRICE,
B.REF1,
B.REF2,
B.REF3,
B.REF4,
B.REF5,
B.REF6,
B.REF7,
B.REF8,
B.REF9,
B.REF10,
B.REF11,
B.REF12,
B.REF13,
B.REF14,
B.REF15,
B.REF16;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3089718421
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2062K| 977M| | 238K (1)| 00:47:38 |
| 1 | HASH GROUP BY | | 2062K| 977M| 1074M| 238K (1)| 00:47:38 |
|* 2 | HASH JOIN RIGHT OUTER| | 2062K| 977M| | 21159 (1)| 00:04:14 |
| 3 | TABLE ACCESS FULL | INVCOST | 29969 | 1346K| | 103 (0)| 00:00:02 |
|* 4 | HASH JOIN | | 2062K| 887M| | 21049 (1)| 00:04:13 |
| 5 | TABLE ACCESS FULL | STOREMAS | 466 | 11184 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 2327K| 947M| 11M| 21038 (1)| 00:04:13 |
| 7 | TABLE ACCESS FULL | STKMAS | 34131 | 11M| | 755 (1)| 00:00:10 |
| 8 | TABLE ACCESS FULL | INV_SUMMARY | 2327K| 157M| | 10545 (1)| 00:02:07 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."STK_ID"="C"."STK_ID"(+) AND "A"."ORG_ID"="C"."ORG_ID"(+) AND
"A"."VALAREA_ID"="C"."VALAREA_ID"(+) AND "A"."BATCH_ID1"="C"."BATCH_ID1"(+) AND
"A"."BATCH_ID2"="C"."BATCH_ID2"(+) AND "A"."BATCH_ID3"="C"."BATCH_ID3"(+) AND
"A"."BATCH_ID4"="C"."BATCH_ID4"(+) AND "A"."SRN_ID"="C"."SRN_ID"(+))
4 - access("A"."STORE_ID"="D"."STORE_ID")
6 - access("A"."STK_ID"="B"."STK_ID")
25 rows selected.
这个视图单独跑了40多分钟!上面的ROW是差不多对的
SELECT EP_LOC.ORG_ID
FROM EP_LOC, EP_USER_LOC
WHERE EP_LOC.LOC_ID = EP_USER_LOC.LOC_ID
AND EP_USER_LOC.USER_ID = :1) AND
((STORE_ID IN (SELECT STORE_ID FROM STOREMAS_LOC WHERE LOC_ID = :2)) OR
(STORE_ID IN
(SELECT STORE_ID FROM EP_USER_STORE WHERE USER_ID = :3)))
这个返回多少记录
---返回400多行
SELECT *
FROM STORESUM
WHERE (ORG_ID IN (SELECT EP_LOC.ORG_ID
FROM EP_LOC, EP_USER_LOC
WHERE EP_LOC.LOC_ID = EP_USER_LOC.LOC_ID
AND EP_USER_LOC.USER_ID = :1) AND
((STORE_ID IN (SELECT STORE_ID FROM STOREMAS_LOC WHERE LOC_ID = :2)) OR
(STORE_ID IN
(SELECT STORE_ID FROM EP_USER_STORE WHERE USER_ID = :3))))
ORDER BY STORE_ID DESC, STK_ID ASC
这里可以考虑把子查询的结果集,推入到视图中。
视图作为被驱动表 然后根据ORG_ID列推入后 走索引 返回少量数据