ORACLE 行转列谓词推入
来源：互联网 发布：连汝安 知乎 编辑：程序博客网 时间：2024/04/28 03:36
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阅读全文
1 0
- ORACLE 行转列谓词推入
- Oracle谓词推入
- ORACLE谓词推入初解
- oracle中的子查询展开和谓词推入
- 再次遇到谓词推入
- 谓词推入一列
- 又见谓词推入
- 谓词推入演示
- 视图合并和谓词推入
- 谓词推入引发的惨案
- 正常的谓词推入效率
- with as 干掉谓词推入
- oracle 子查询解嵌套和谓词推入（sqlsubquery unnest and pre_push）
- 《基于ORACLE的SQL优化读书》笔记 星形转换/谓词推入/连接因式分解/表移除
- 这里的谓词为什么推入不了?
- 强制让SQL走谓词推入
- 彻底理解谓词推入的利弊
- VIEW PUSHED PREDICATE(谓词推入)引发的惨剧
- Spring cloud
- Scala练习-直接插入排序
- c语言中全局变量重定义
- JS事件之事件类型[焦点事件]
- Git 从入门到精通（忽略某些文件.gitignore）（五）
- ORACLE 行转列谓词推入
- eclipse项目导入AS方法
- 各种链表队列的宏操作及应用
- 两条Linux删数据跑路命令
- dedecms修改文章标题限制长度
- android 监听网络状态
- 怎样调试静态lib
- 验证是否为空并抛异常
- 【集成】极验验证
