|
查看执行计划
SQL> c/ga/gu
1* select * from table(dbms_xplan.display_cursor('g60xas8kgutmt'))
SQL> /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g60xas8kgutmt, child number 0
-------------------------------------
SELECT d.item_no,item_name,sum(qlt0) qlt0,sum(qltG) qltG,sum(qltJ) qltJ,sum(qltS
) qltS,sum(qlt0+qltG+qltJ+qltS)
sumqlt FROM (select item_no,qlt, (case when qlt='0' then plt_qty else 0 end) qlt
0, (case when qlt='G' then
plt_qty else 0 end) qltG, (case when qlt='J' then plt_qty else 0 end) qltJ, (cas
e when qlt='T' then plt_qty else
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0 end) qltS FROM loc_dtl ) d , itm_mst i WHERE d.item_no = i.item_no AND d.ite
m_No >= :itemno1 AND d.item_No
<= :itemno2 GROUP BY d.item_no,item_name
Plan hash value: 2693555768
--------------------------------------------------------------------------------
-----------------------------------------
| Id | Operation | Name
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------------------------
| 0 | SELECT STATEMENT |
| | | 4830 (100)| |
| 1 | HASH GROUP BY |
| 1 | 70 | 4830 (3)| 00:00:58 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | VIEW |
| 1 | 70 | 4830 (3)| 00:00:58 |
| 3 | HASH GROUP BY |
| 1 | 177 | 4830 (3)| 00:00:58 |
|* 4 | FILTER |
| | | | |
| 5 | MAT_VIEW ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_TL
| 1 | 68 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | NESTED LOOPS |
| 1 | 177 | 4829 (3)| 00:00:58 |
| 7 | NESTED LOOPS |
| 1 | 109 | 4827 (3)| 00:00:58 |
|* 8 | HASH JOIN |
| 1 | 105 | 4826 (3)| 00:00:58 |
| 9 | NESTED LOOPS |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | 77 | 4755 (3)| 00:00:58 |
|* 10 | HASH JOIN |
| 177 | 10089 | 4735 (3)| 00:00:57 |
| 11 | MAT_VIEW ACCESS BY INDEX ROWID| MTL_CATEGORIES_B
| 22 | 198 | 3 (0)| 00:00:01 |
| 12 | NESTED LOOPS |
| 1 | 37 | 4 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 13 | NESTED LOOPS |
| 1 | 28 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | FND_ID_FLEX_STRUCTURES_TL_U1
| 3 | 42 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_U1
| 1 | 14 | 0 (0)| |
|* 16 | INDEX RANGE SCAN | MTL__CATEGORIES_B_N2
| 41 | | 0 (0)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 17 | MAT_VIEW ACCESS FULL | MTL_ITEM_CATEGORIES
| 88602 | 1730K| 4729 (3)| 00:00:57 |
|* 18 | MAT_VIEW ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B
| 1 | 20 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1
| 1 | | 0 (0)| |
|* 20 | TABLE ACCESS FULL | LOC_DTL
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 65 | 1820 | 70 (2)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | MTL_CATEGORIES_TL_U1
| 1 | 4 | 1 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | MTL_SYSTEM_ITEMS_TL_U1
| 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(:ITEMNO1<=:ITEMNO2)
8 - access("ITEM_NO"="SEGMENT1")
10 - access("CATEGORY_ID"="MIC"."CATEGORY_ID")
14 - access("T"."APPLICATION_ID"=401 AND "T"."ID_FLEX_CODE"='MCAT')
15 - access("B"."APPLICATION_ID"=401 AND "B"."ID_FLEX_CODE"='MCAT' AND "B"."ID
_FLEX_NUM"="T"."ID_FLEX_NUM")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
16 - access("STRUCTURE_ID"="B"."ID_FLEX_NUM")
17 - filter(("MIC"."CATEGORY_SET_ID"=1 AND INTERNAL_FUNCTION("MIC"."ORGANIZATI
ON_ID")))
18 - filter(("SEGMENT1">=:ITEMNO1 AND "SEGMENT1"<=:ITEMNO2))
19 - access("MIC"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND "MIC"."ORGANIZAT
ION_ID"="ORGANIZATION_ID")
filter(("ORGANIZATION_ID"=110 OR "ORGANIZATION_ID"=486))
20 - filter(("ITEM_NO">=:ITEMNO1 AND "ITEM_NO"<=:ITEMNO2))
21 - access("CATEGORY_ID"="MCT"."CATEGORY_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
22 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=
"T"."ORGANIZATION_ID")
filter(("T"."ORGANIZATION_ID"=110 OR "T"."ORGANIZATION_ID"=486)) |
|