ORACLE 行转列有很多种方式，这里不再做详细描述。之前在做开发的时候使用了 povit 做行转列，发现在使用的时候出现性能问题。详细查看之下，发现是执行计划中无法使用谓词推入。
经过详细实验发现：
1、povit 行转列大于等于20列，就不谓词推入了。
2、DECODE 行转列 30列还能谓词推入。
所以，在行转列超过20列的情况下，不推荐使用POVIT做转换。
一下是使用代码：
POVIT行转列:
SELECT * FROM (
WITH item_element AS
(SELECT *
FROM (SELECT v.inventory_item_id AS item_id,
v.element_name,
v.element_value
FROM mtl_descriptive_elements e, mtl_descr_element_values v
WHERE v.element_name = e.element_name
AND e.item_catalog_group_id = 110)
pivot(MAX(element_value) --聚合操作函数
FOR element_name --行转列标准
IN('BIS 印度认证' AS bis_印度认证,
'CCC(中国)' AS ccc中国,
'DOT(美国)' AS dot美国,
'ECE(欧盟)' AS ece欧盟,
'GCC(海湾)' AS gcc海湾,
'INMETRO(巴西)' AS inmetro巴西,
'SANCOP(尼日利亚)' AS sancop尼日利亚,
'SNI印尼认证' AS sni印尼认证,
'内销/外销' AS 内销外销,
'品牌' AS 品牌,
'噪声(UNECE)' AS 噪声unece,
'层级' AS 层级,
'市场' AS 市场,
'扁平率' AS 扁平率,
'断面宽(inch)' AS 断面宽inch,
'断面宽(mm)' AS 断面宽mm,
'断面高(mm)' AS 断面高mm,
'最大停放时间（小时）' AS 最大停放时间小时,
'最小停放时间（小时）' AS 最小停放时间小时,
'温度指数' AS 温度指数,
'滚动周长（mm）' AS 滚动周长mm,
'牵引指数' AS 牵引指数,
'磨耗指数' AS 磨耗指数,
'花纹' AS 花纹,
'花纹沟深(mm)' AS 花纹沟深mm,
'规格描述' AS 规格描述,
'负荷指数' AS 负荷指数,
'轮胎外直径（mm）' AS 轮胎外直径mm,
'轮辋直径(英寸)' AS 轮辋直径英寸,
'适用类型' AS 适用类型,
'速度级别' AS 速度级别,
'重量（Kg）' AS 重量kg,
'额定气压（KPA）' AS 额定气压kpa,
'额定气压（PSI）' AS 额定气压psi,
'额定负荷（KG）' AS 额定负荷kg,
'额定负荷（LBS）' AS 额定负荷lbs) --行转列列取值和顺序
)),
salesrep AS
(SELECT DISTINCT salesrep_id, NAME AS salesrep_name, salesrep_number
FROM jtf.jtf_rs_salesreps t
WHERE t.status = 'A'),
hlda AS
(SELECT hlda.header_id, hlda.order_hold_id
FROM (SELECT h.order_hold_id,
h.header_id,
h.released_flag,
row_number() over(PARTITION BY h.header_id ORDER BY h.order_hold_id DESC) row_num
FROM oe_order_holds_all h) hlda
WHERE hlda.released_flag = 'Y'
AND hlda.row_num = 1)
SELECT
h.org_id,
ac.CUSTOMER_NAME,
h.header_id,
h.order_number AS order_number,
h.attribute14 AS contract_number,
H.SALESREP_ID,
h.flow_status_code AS header_status_code,
h.cancelled_flag AS header_cancelled_flag,
decode(hlda.order_hold_id,
NULL,
'Y',
'N') AS order_hold_flag,
l.line_id,
l.flow_status_code AS line_status_code,
l.cancelled_flag AS line_cancelled_flag,
l.line_number || '.' || l.shipment_number AS line_ship_num,
l.inventory_item_id AS inventory_item_id,
l.ordered_item,
l.ordered_quantity,
l.order_quantity_uom,
l.unit_selling_price,
(SELECT SUM(delivery.shipped_quantity) FROM wsh.wsh_delivery_details delivery WHERE l.line_id = delivery.source_line_id ) AS shipped_quantity,
l.attribute20 AS loading_qty,
l.ship_from_org_id,
item.*,
salesrep.salesrep_number,
salesrep.salesrep_name
FROM oe_order_headers_all h,
oe_order_lines_all l,
ar_customers ac,
item_element item,
salesrep,
hlda
WHERE h.header_id = l.header_id
AND l.inventory_item_id = item.item_id(+)
AND h.salesrep_id = salesrep.salesrep_id(+)
AND h.header_id = hlda.header_id(+)
AND h.SOLD_TO_ORG_ID = ac.CUSTOMER_ID(+)
-- AND L.CANCELLED_FLAG = 'N'
AND H.FLOW_STATUS_CODE = 'BOOKED'
AND l.FLOW_STATUS_CODE = 'AWAITING_SHIPPING'
-- AND hlda.header_id IS NOT NULL
) WHERE order_number = '10141000071'
Plan Hash Value : 3142488690
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 984 | 9271 | 00:00:01 |
| * 1 | HASH JOIN OUTER | | 1 | 984 | 9271 | 00:00:01 |
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 958 | 7462 | 00:00:01 |
| * 3 | HASH JOIN OUTER | | 1 | 958 | 7462 | 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 333 | 68 | 00:00:01 |
| 5 | NESTED LOOPS | | 7 | 333 | 68 | 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 265 | 65 | 00:00:01 |
| * 7 | HASH JOIN OUTER | | 1 | 233 | 64 | 00:00:01 |
| 8 | JOIN FILTER CREATE | :BF0001 | 1 | 81 | 57 | 00:00:01 |
| * 9 | HASH JOIN OUTER | | 1 | 81 | 57 | 00:00:01 |
| * 10 | TABLE ACCESS BY INDEX ROWID BATCHED | OE_ORDER_HEADERS_ALL | 1 | 40 | 3 | 00:00:01 |
| * 11 | INDEX RANGE SCAN | OE_ORDER_HEADERS_U2 | 1 | | 2 | 00:00:01 |
| * 12 | VIEW | | 1 | 41 | 54 | 00:00:01 |
| * 13 | WINDOW SORT PUSHED RANK | | 11048 | 143624 | 54 | 00:00:01 |
| 14 | TABLE ACCESS STORAGE FULL | OE_ORDER_HOLDS_ALL | 11048 | 143624 | 52 | 00:00:01 |
| 15 | VIEW | | 153 | 23256 | 7 | 00:00:01 |
| 16 | HASH UNIQUE | | 153 | 3672 | 7 | 00:00:01 |
| 17 | JOIN FILTER USE | :BF0001 | 233 | 5592 | 6 | 00:00:01 |
| * 18 | TABLE ACCESS STORAGE FULL | JTF_RS_SALESREPS | 233 | 5592 | 6 | 00:00:01 |
| 19 | VIEW PUSHED PREDICATE | AR_CUSTOMERS | 1 | 32 | 1 | 00:00:01 |
| 20 | NESTED LOOPS | | 1316 | 61852 | 3 | 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 11 | 2 | 00:00:01 |
| * 22 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | 1 | 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1316 | 47376 | 1 | 00:00:01 |
| * 24 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | 0 | 00:00:01 |
| * 25 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 7 | | 1 | 00:00:01 |
| * 26 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 1 | 68 | 3 | 00:00:01 |
| 27 | VIEW | | 54880 | 34300000 | 7393 | 00:00:01 |
| 28 | TRANSPOSE | | | | | |
| 29 | SORT GROUP BY PIVOT | | 54880 | 2195200 | 7393 | 00:00:01 |
| * 30 | HASH JOIN | | 1052098 | 42083920 | 3859 | 00:00:01 |
| 31 | JOIN FILTER CREATE | :BF0002 | 36 | 540 | 2 | 00:00:01 |
| * 32 | INDEX RANGE SCAN | MTL_DESCRIPTIVE_ELEMENTS_U1 | 36 | 540 | 2 | 00:00:01 |
| 33 | JOIN FILTER USE | :BF0002 | 1290506 | 32262650 | 3841 | 00:00:01 |
| * 34 | TABLE ACCESS STORAGE FULL | MTL_DESCR_ELEMENT_VALUES | 1290506 | 32262650 | 3841 | 00:00:01 |
| 35 | VIEW | VW_SSQ_1 | 83988 | 2183688 | 1808 | 00:00:01 |
| 36 | HASH GROUP BY | | 83988 | 755892 | 1808 | 00:00:01 |
| 37 | JOIN FILTER USE | :BF0000 | 84799 | 763191 | 1500 | 00:00:01 |
| * 38 | TABLE ACCESS STORAGE FULL | WSH_DELIVERY_DETAILS | 84799 | 763191 | 1500 | 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("L"."LINE_ID"="ITEM_0"(+))
* 3 - access("L"."INVENTORY_ITEM_ID"="from$_subquery$_005"."ITEM_ID"(+))
* 7 - access("H"."SALESREP_ID"="SALESREP"."SALESREP_ID"(+))
* 9 - access("H"."HEADER_ID"="HLDA"."HEADER_ID"(+))
* 10 - filter("H"."FLOW_STATUS_CODE"='BOOKED')
* 11 - access("H"."ORDER_NUMBER"=10141000071)
* 12 - filter("HLDA"."RELEASED_FLAG"(+)='Y' AND "HLDA"."ROW_NUM"(+)=1)
* 13 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."HEADER_ID" ORDER BY INTERNAL_FUNCTION("H"."ORDER_HOLD_ID") DESC )<=1)
* 18 - storage("T"."STATUS"='A' AND SYS_OP_BLOOM_FILTER(:BF0001,"SALESREP_ID"))
* 18 - filter("T"."STATUS"='A' AND SYS_OP_BLOOM_FILTER(:BF0001,"SALESREP_ID"))
* 22 - access("CUST"."CUST_ACCOUNT_ID"="H"."SOLD_TO_ORG_ID")
* 24 - access("CUST"."PARTY_ID"="PARTY"."PARTY_ID")
* 25 - access("H"."HEADER_ID"="L"."HEADER_ID")
* 26 - filter("L"."FLOW_STATUS_CODE"='AWAITING_SHIPPING')
* 30 - access("V"."ELEMENT_NAME"="E"."ELEMENT_NAME")
* 32 - access("E"."ITEM_CATALOG_GROUP_ID"=110)
* 34 - storage(SYS_OP_BLOOM_FILTER(:BF0002,"V"."ELEMENT_NAME"))
* 34 - filter(SYS_OP_BLOOM_FILTER(:BF0002,"V"."ELEMENT_NAME"))
* 38 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"DELIVERY"."SOURCE_LINE_ID"))
* 38 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"DELIVERY"."SOURCE_LINE_ID"))
Note
-----
- dynamic sampling used for this statementDECODE 行转列
SELECT * FROM (
WITH item_element AS
(SELECT *
FROM (SELECT v.inventory_item_id AS item_id,
max(DECODE(v.element_name, 'BIS 印度认证',element_value)) AS bis_印度认证,
max(DECODE(v.element_name, 'CCC(中国)',element_value)) AS ccc中国,
max(DECODE(v.element_name, 'DOT(美国)',element_value)) AS dot美国,
max(DECODE(v.element_name, 'ECE(欧盟)',element_value)) AS ece欧盟,
max(DECODE(v.element_name, 'GCC(海湾)',element_value)) AS gcc海湾,
max(DECODE(v.element_name, 'INMETRO(巴西)',element_value)) AS inmetro巴西,
max(DECODE(v.element_name, 'SANCOP(尼日利亚)',element_value)) AS sancop尼日利亚,
max(DECODE(v.element_name, 'SNI印尼认证',element_value)) AS sni印尼认证,
max(DECODE(v.element_name, '内销/外销',element_value)) AS 内销外销,
max(DECODE(v.element_name, '品牌',element_value)) AS 品牌,
max(DECODE(v.element_name, '噪声(UNECE)',element_value)) AS 噪声unece,
max(DECODE(v.element_name, '层级',element_value)) AS 层级,
max(DECODE(v.element_name, '市场',element_value)) AS 市场,
max(DECODE(v.element_name, '扁平率',element_value)) AS 扁平率,
max(DECODE(v.element_name, '断面宽(inch)',element_value)) AS 断面宽inch,
max(DECODE(v.element_name, '断面宽(mm)',element_value)) AS 断面宽mm,
max(DECODE(v.element_name, '断面高(mm)',element_value)) AS 断面高mm,
max(DECODE(v.element_name, '最大停放时间（小时）',element_value)) AS 最大停放时间小时,
max(DECODE(v.element_name, '最小停放时间（小时）',element_value)) AS 最小停放时间小时,
max(DECODE(v.element_name, '温度指数',element_value)) AS 温度指数,
max(DECODE(v.element_name, '滚动周长（mm）',element_value)) AS 滚动周长mm,
max(DECODE(v.element_name, '牵引指数',element_value)) AS 牵引指数,
max(DECODE(v.element_name, '磨耗指数',element_value)) AS 磨耗指数,
max(DECODE(v.element_name, '花纹',element_value)) AS 花纹,
max(DECODE(v.element_name, '花纹沟深(mm)',element_value)) AS 花纹沟深mm,
max(DECODE(v.element_name, '规格描述',element_value)) AS 规格描述,
max(DECODE(v.element_name, '负荷指数',element_value)) AS 负荷指数,
max(DECODE(v.element_name, '轮胎外直径（mm）',element_value)) AS 轮胎外直径mm,
max(DECODE(v.element_name, '轮辋直径(英寸)',element_value)) AS 轮辋直径英寸
FROM mtl_descriptive_elements e, mtl_descr_element_values v
WHERE v.element_name = e.element_name
AND e.item_catalog_group_id = 110
--AND v.INVENTORY_ITEM_ID = 88707
GROUP BY v.inventory_item_id
)),
salesrep AS
(SELECT DISTINCT salesrep_id, NAME AS salesrep_name, salesrep_number
FROM jtf.jtf_rs_salesreps t
WHERE t.status = 'A'),
hlda AS
(SELECT hlda.header_id, hlda.order_hold_id
FROM (SELECT h.order_hold_id,
h.header_id,
h.released_flag,
row_number() over(PARTITION BY h.header_id ORDER BY h.order_hold_id DESC) row_num
FROM oe_order_holds_all h) hlda
WHERE hlda.released_flag = 'Y'
AND hlda.row_num = 1)
SELECT
h.org_id,
ac.CUSTOMER_NAME,
h.header_id,
h.order_number AS order_number,
h.attribute14 AS contract_number,
H.SALESREP_ID,
h.flow_status_code AS header_status_code,
h.cancelled_flag AS header_cancelled_flag,
decode(hlda.order_hold_id,
NULL,
'Y',
'N') AS order_hold_flag,
l.line_id,
l.flow_status_code AS line_status_code,
l.cancelled_flag AS line_cancelled_flag,
l.line_number || '.' || l.shipment_number AS line_ship_num,
l.inventory_item_id AS inventory_item_id,
l.ordered_item,
l.ordered_quantity,
l.order_quantity_uom,
l.unit_selling_price,
(SELECT SUM(delivery.shipped_quantity) FROM wsh.wsh_delivery_details delivery WHERE l.line_id = delivery.source_line_id ) AS shipped_quantity,
l.attribute20 AS loading_qty,
l.ship_from_org_id,
item.*,
salesrep.salesrep_number,
salesrep.salesrep_name
FROM oe_order_headers_all h,
oe_order_lines_all l,
ar_customers ac,
item_element item,
salesrep,
hlda
WHERE h.header_id = l.header_id
AND l.inventory_item_id = item.item_id(+)
AND h.salesrep_id = salesrep.salesrep_id(+)
AND h.header_id = hlda.header_id(+)
AND h.SOLD_TO_ORG_ID = ac.CUSTOMER_ID(+)
-- AND L.CANCELLED_FLAG = 'N'
AND H.FLOW_STATUS_CODE = 'BOOKED'
AND l.FLOW_STATUS_CODE = 'AWAITING_SHIPPING'
-- AND hlda.header_id IS NOT NULL
) WHERE order_number = '10141000071'
Plan Hash Value : 1040248332
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 829 | 23 | 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 829 | 23 | 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 323 | 17 | 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 308 | 15 | 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 240 | 12 | 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 210 | 9 | 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 69 | 6 | 00:00:01 |
| * 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OE_ORDER_HEADERS_ALL | 1 | 40 | 3 | 00:00:01 |
| * 8 | INDEX RANGE SCAN | OE_ORDER_HEADERS_U2 | 1 | | 2 | 00:00:01 |
| 9 | VIEW PUSHED PREDICATE | AR_CUSTOMERS | 1 | 29 | 3 | 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 47 | 3 | 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 11 | 2 | 00:00:01 |
| * 12 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | 1 | 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 36 | 1 | 00:00:01 |
| * 14 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | 0 | 00:00:01 |
| 15 | VIEW PUSHED PREDICATE | | 1 | 141 | 3 | 00:00:01 |
| 16 | SORT UNIQUE | | 1 | 24 | 3 | 00:00:01 |
| * 17 | TABLE ACCESS BY INDEX ROWID BATCHED | JTF_RS_SALESREPS | 2 | 48 | 2 | 00:00:01 |
| * 18 | INDEX RANGE SCAN | JTF_RS_SALESREPS_U1 | 2 | | 1 | 00:00:01 |
| * 19 | VIEW PUSHED PREDICATE | | 1 | 30 | 3 | 00:00:01 |
| 20 | WINDOW SORT | | 1 | 13 | 3 | 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID BATCHED | OE_ORDER_HOLDS_ALL | 1 | 13 | 2 | 00:00:01 |
| * 22 | INDEX RANGE SCAN | OE_ORDER_HOLDS_ALL_N1 | 1 | | 1 | 00:00:01 |
| * 23 | TABLE ACCESS BY INDEX ROWID BATCHED | OE_ORDER_LINES_ALL | 1 | 68 | 3 | 00:00:01 |
| * 24 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 7 | | 1 | 00:00:01 |
| 25 | VIEW PUSHED PREDICATE | VW_SSQ_1 | 1 | 15 | 2 | 00:00:01 |
| 26 | SORT GROUP BY | | 1 | 9 | 2 | 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID BATCHED | WSH_DELIVERY_DETAILS | 1 | 9 | 2 | 00:00:01 |
| * 28 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N3 | 1 | | 1 | 00:00:01 |
| 29 | VIEW PUSHED PREDICATE | | 1 | 506 | 6 | 00:00:01 |
| 30 | SORT GROUP BY | | 1 | 40 | 6 | 00:00:01 |
| 31 | NESTED LOOPS SEMI | | 23 | 920 | 6 | 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_DESCR_ELEMENT_VALUES | 23 | 575 | 6 | 00:00:01 |
| * 33 | INDEX RANGE SCAN | MTL_DESCR_ELEMENT_VALUES_U1 | 23 | | 3 | 00:00:01 |
| * 34 | INDEX UNIQUE SCAN | MTL_DESCRIPTIVE_ELEMENTS_U1 | 36 | 540 | 0 | 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 7 - filter("H"."FLOW_STATUS_CODE"='BOOKED')
* 8 - access("H"."ORDER_NUMBER"=10141000071)
* 12 - access("CUST"."CUST_ACCOUNT_ID"="H"."SOLD_TO_ORG_ID")
* 14 - access("CUST"."PARTY_ID"="PARTY"."PARTY_ID")
* 17 - filter("T"."STATUS"='A')
* 18 - access("SALESREP_ID"="H"."SALESREP_ID")
* 19 - filter("HLDA"."RELEASED_FLAG"(+)='Y' AND "HLDA"."ROW_NUM"(+)=1)
* 22 - access("H"."HEADER_ID"="H"."HEADER_ID")
* 23 - filter("L"."FLOW_STATUS_CODE"='AWAITING_SHIPPING')
* 24 - access("H"."HEADER_ID"="L"."HEADER_ID")
* 28 - access("DELIVERY"."SOURCE_LINE_ID"="L"."LINE_ID")
* 33 - access("V"."INVENTORY_ITEM_ID"="L"."INVENTORY_ITEM_ID")
* 34 - access("E"."ITEM_CATALOG_GROUP_ID"=110 AND "V"."ELEMENT_NAME"="E"."ELEMENT_NAME")
Note
-----
- dynamic sampling used for this statement
3716
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
