请高手帮我优化一下这个sql语句。
指点一下什么地方需要优化!如何优化也可以!
非常感谢,分不够可以加的哦!谢谢了!
这个语句 我查1700条语句要5秒钟……
select batch_no "批次号",product_code "所属产品", order_no "工单号",item_parent "工单物料号",parent_note "工单物料名称",parent_graph "工单图号", item_no "领料物料号",subNote "领料物料名称",subgraph_no "子物料图号",subgrade " 领料物料规格",line_no "在工单中的行号",date_put "投放日期",date_due "需求日期", qty "库存数量",qty_req "需求数量",qty_fenpei "已分配量", qty-qty_fenpei "可用量",qty_per "单位数量",item_unit "单位",planner "计划员",stock_no "库房号",status_id "物料状态",work_id "领用工作地",print_man "打印人",print_date "打印日期",get_id "分类" from (
select(
select plan1.batch_no from product_plan plan1 where plan1.item_no=(select head1.product_code from wo_head head1 where head1.order_no =bbb)) batch_no,
(select plan2.item_no from product_plan plan2 where plan2.item_no=(select head2.product_code from wo_head head2 where head2.order_no=bbb)) product_code,
(select order_no from wo_head where order_no=bbb) order_no,
(select head1.item_no from wo_head head1 where head1.order_no=bbb) item_parent,
(select note from item where item_no =(select item_no from wo_head where order_no=bbb)) parent_note,
(select graph_no from item where item_no=(select item_no from wo_head where order_no=bbb)) parent_graph,
(select distinct item_no from process_req where item_no=aaa) item_no,
(select note from item where item_no=aaa) subNote,
(select graph_no from item where item_no=aaa) subgraph_no,
(select grade from item where item_no=aaa) subgrade,
(select line_no from process_req where item_no=aaa and order_no=bbb) line_no,
(select date_put from wo_head where order_no=bbb) date_put,
(select date_due from process_req where item_no=aaa and order_no =bbb) date_due,
(select nvl(sum(stock1.qty),0) from item_stock stock1 where stock1.item_no=aaa) qty,
(select req1.qty_req from process_req req1 where req1.item_no=aaa and req1.order_no=bbb) qty_req,
(select nvl(sum(req2.qty_req),0.0) from process_req req2 where req2.item_no =aaa and req2.status_id='P') qty_fenpei,
(select qty_per from process_req where item_no=aaa and order_no = bbb) qty_per,
(select decode(unit,'0','件','1','台','2','套','3','条','4','包','9','EA','10','毫米','11','厘米','12','米','13','公里','14','付','15','本','16','顶','20','平方毫米','21','平方厘米','22','平方米','30','立方毫米','31','立方厘米','32','立方米','33','升','34','毫升','35','把','40','毫克','41','克','42','千克','43','吨','44','克拉','50','盒','51','瓶','52','双','53','张','54','支','55','节','56','根','57','卷','58','个','59','只','60','床','61','桶','62','箱','63','块','64','袋') from item where item_no=aaa) item_unit,
(select planner from item where item_no=aaa) planner,
(select stock_no from process_req where item_no=aaa and order_no=bbb) stock_no,
(select decode(status_id,'C','缺料','P','足料') from process_req where item_no=aaa and order_no=bbb) status_id,
(select work_id from process_req where item_no=aaa and order_no=bbb) work_id,
(select print_man from process_req where item_no=aaa and order_no=bbb) print_man,
(select print_date from process_req where item_no=aaa and order_no=bbb) print_date,
(select decode(get_id,'BUY','外购件','MAK','自制件','MB','未定') from item where item_no= aaa) get_id
from (select req.item_no aaa,req.order_no bbb from process_req req where req.status_id='P'and req.order_no in(select head.order_no from wo_head head where head.batch_no in (select batch_no from product_plan) or head.product_code in ( select item_no from product_plan )))) order by qty desc
指点一下什么地方需要优化!如何优化也可以!
非常感谢,分不够可以加的哦!谢谢了!
这个语句 我查1700条语句要5秒钟……
select batch_no "批次号",product_code "所属产品", order_no "工单号",item_parent "工单物料号",parent_note "工单物料名称",parent_graph "工单图号", item_no "领料物料号",subNote "领料物料名称",subgraph_no "子物料图号",subgrade " 领料物料规格",line_no "在工单中的行号",date_put "投放日期",date_due "需求日期", qty "库存数量",qty_req "需求数量",qty_fenpei "已分配量", qty-qty_fenpei "可用量",qty_per "单位数量",item_unit "单位",planner "计划员",stock_no "库房号",status_id "物料状态",work_id "领用工作地",print_man "打印人",print_date "打印日期",get_id "分类" from (
select(
select plan1.batch_no from product_plan plan1 where plan1.item_no=(select head1.product_code from wo_head head1 where head1.order_no =bbb)) batch_no,
(select plan2.item_no from product_plan plan2 where plan2.item_no=(select head2.product_code from wo_head head2 where head2.order_no=bbb)) product_code,
(select order_no from wo_head where order_no=bbb) order_no,
(select head1.item_no from wo_head head1 where head1.order_no=bbb) item_parent,
(select note from item where item_no =(select item_no from wo_head where order_no=bbb)) parent_note,
(select graph_no from item where item_no=(select item_no from wo_head where order_no=bbb)) parent_graph,
(select distinct item_no from process_req where item_no=aaa) item_no,
(select note from item where item_no=aaa) subNote,
(select graph_no from item where item_no=aaa) subgraph_no,
(select grade from item where item_no=aaa) subgrade,
(select line_no from process_req where item_no=aaa and order_no=bbb) line_no,
(select date_put from wo_head where order_no=bbb) date_put,
(select date_due from process_req where item_no=aaa and order_no =bbb) date_due,
(select nvl(sum(stock1.qty),0) from item_stock stock1 where stock1.item_no=aaa) qty,
(select req1.qty_req from process_req req1 where req1.item_no=aaa and req1.order_no=bbb) qty_req,
(select nvl(sum(req2.qty_req),0.0) from process_req req2 where req2.item_no =aaa and req2.status_id='P') qty_fenpei,
(select qty_per from process_req where item_no=aaa and order_no = bbb) qty_per,
(select decode(unit,'0','件','1','台','2','套','3','条','4','包','9','EA','10','毫米','11','厘米','12','米','13','公里','14','付','15','本','16','顶','20','平方毫米','21','平方厘米','22','平方米','30','立方毫米','31','立方厘米','32','立方米','33','升','34','毫升','35','把','40','毫克','41','克','42','千克','43','吨','44','克拉','50','盒','51','瓶','52','双','53','张','54','支','55','节','56','根','57','卷','58','个','59','只','60','床','61','桶','62','箱','63','块','64','袋') from item where item_no=aaa) item_unit,
(select planner from item where item_no=aaa) planner,
(select stock_no from process_req where item_no=aaa and order_no=bbb) stock_no,
(select decode(status_id,'C','缺料','P','足料') from process_req where item_no=aaa and order_no=bbb) status_id,
(select work_id from process_req where item_no=aaa and order_no=bbb) work_id,
(select print_man from process_req where item_no=aaa and order_no=bbb) print_man,
(select print_date from process_req where item_no=aaa and order_no=bbb) print_date,
(select decode(get_id,'BUY','外购件','MAK','自制件','MB','未定') from item where item_no= aaa) get_id
from (select req.item_no aaa,req.order_no bbb from process_req req where req.status_id='P'and req.order_no in(select head.order_no from wo_head head where head.batch_no in (select batch_no from product_plan) or head.product_code in ( select item_no from product_plan )))) order by qty desc
表有多大,相关列上有没有索引,用Explain Plan 分析下SQL.
用Explain Plan 分析下SQL. 这个东西没用过阿 ?
清指点下
t5.item_no product_code,
t2.order_no order_no,
t2.item_no item_parent,
t3.note parent_note,
t3.graph_no parent_graph,
t1.item_no item_no,
t3.note subNote,
t3.graph_no subgraph_no,
t3.grade subgrade,
t1.line_no line_no,
t2.date_put date_put,
t1.date_due date_due,
(select nvl(sum(stock1.qty),0) from item_stock stock1 where stock1.item_no = t1.item_no) qty,
t1.qty_req qty_req,
(select nvl(sum(req2.qty_req),0.0) from process_req req2 where req2.item_no = t1.item_no and req2.status_id = 'P') qty_fenpei,
t1.qty_per qty_per,
decode(t3.unit,'0','件','1','台','2','套','3','条',
'4','包','9','EA','10','毫米','11','厘米',
'12','米','13','公里','14','付','15','本',
'16','顶','20','平方毫米','21','平方厘米',
'22','平方米','30','立方毫米','31','立方厘米',
'32','立方米','33','升','34','毫升','35','把',
'40','毫克','41','克','42','千克','43','吨',
'44','克拉','50','盒','51','瓶','52','双',
'53','张','54','支','55','节','56','根','57','卷',
'58','个','59','只','60','床','61','桶','62','箱',
'63','块','64','袋') item_unit,
t3.planner planner,
t1.stock_no stock_no,
'足料' status_id,
t1.work_id work_id,
t1.print_man print_man,
t1.print_date print_date,
decode(t1.get_id,'BUY','外购件','MAK','自制件','MB','未定') get_id
from process_req t1,
wo_head t2,
item t3,
product_plan t5
where t1.status_id = 'P'
and t1.order_no = t2.order_no
and exists (select 1 from product_plan t6 where t6.batch_no = t2.batch_no or t6.item_no = t2.product_code)
and t5.item_no(+) = t2.product_code -- batch_no
and t3.item_no(+) = t2.item_no;
不一定正確,因為不知你的主外鍵關係
我正在尝试用你的方法查询 !谢谢
這個用了兩次1沒有什麽意義,就是說如果後面的條件存在的話就返回1,也可以換成2,3,4或者字符什麽的。
t5.item_no(+) = t2.product_code 中的+号是什么意思呢!
呵呵