今天有个兄弟跟我说sql跑得太慢了，让我看看。sql如下： select rownum row_num, pv.vendor_name, pha.segment1 po_num, prh.preparer_id, pha.org_id, pha.po_header_id, wo.department_code, wo.description oper_seq_desc, to_char(pha.creation_date, 'r
今天有个兄弟跟我说sql跑得太慢了，让我看看。sql如下：
select rownum row_num, pv.vendor_name, pha.segment1 po_num, prh.preparer_id, pha.org_id, pha.po_header_id, wo.department_code, wo.description oper_seq_desc, to_char(pha.creation_date, 'rrrr-mm-dd hh24:mi:ss') enter_date, to_char(pha.approved_date, 'rrrr-mm-dd hh24:mi:ss') approved_date, --cux_public_pkg.get_item_no(wdj.primary_item_id) item_no, we.wip_entity_name from po.po_headers_all pha, apps.po_vendors pv, po.po_lines_all pla, po.po_line_locations_all pll, po.po_distributions_all pld, po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, po.po_req_distributions_all prd, wip.wip_discrete_jobs wdj, apps.bom_standard_operations_v bso, apps.wip_operations_v wo, wip.wip_entities we where 1 = 1 and prl.wip_entity_id = we.wip_entity_id and pha.po_header_id = pla.po_header_id and pha.vendor_id = pv.vendor_id and pll.po_line_id = pla.po_line_id and pll.po_header_id = pha.po_header_id and pll.line_location_id = pld.line_location_id and prd.requisition_line_id = prl.requisition_line_id and pld.req_distribution_id = prd.distribution_id and prl.requisition_header_id = prh.requisition_header_id and prl.wip_entity_id = wdj.wip_entity_id and prl.wip_entity_id = wo.wip_entity_id and prl.wip_operation_seq_num = wo.operation_seq_num and wo.standard_operation_id = bso.standard_operation_id and wdj.organization_id = /*p_organization_id*/83 and pha.segment1 >= /*nvl(p_po_num_f, pha.segment1)*/'621337540' and pha.segment1 = nvl(pha.approved_date, sysdate + 9999) and nvl(pha.approved_date, sysdate + 9999) <=nvl(pha.approved_date, sysdate + 9999) order by pha.segment1, pla.line_num;
快速的运用sql三段分拆方法(分享过的) 扫描一下，发现没问题 (如果不知道的哥们，请自己百度 落落 sql 三段分拆方法)
sql里面有个视图wo 视图代码如下：
/*create or replace view wip_operations_v(row_id, wip_entity_id, operation_seq_num, organization_id, repetitive_schedule_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login, request_id, program_application_id, program_id, program_update_date, operation_sequence_id, standard_operation_id, operation_code, department_id, department_code, location_id, description, scheduled_quantity, quantity_in_queue, quantity_running, quantity_waiting_to_move, quantity_rejected, quantity_scrapped, quantity_completed, first_unit_start_date, first_unit_completion_date, last_unit_start_date, last_unit_completion_date, previous_operation_seq_num, next_operation_seq_num, count_point_type, count_point_flag, autocharge_flag, backflush_flag, minimum_transfer_quantity, date_last_moved, attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15, operation_yield, cumulative_scrap_quantity, operation_yield_enabled, operation_completed, shutdown_type, shutdown_type_disp, x_pos, y_pos, long_description, disable_date, recommended, progress_percentage, wsm_bonus_quantity, actual_start_date, actual_completion_date, employee_id, employee_name, lowest_acceptable_yield, check_skill)as*/select wo.rowid row_id, wo.wip_entity_id, wo.operation_seq_num, wo.organization_id, wo.repetitive_schedule_id, wo.last_update_date, wo.last_updated_by, wo.creation_date, wo.created_by, wo.last_update_login, wo.request_id, wo.program_application_id, wo.program_id, wo.program_update_date, wo.operation_sequence_id, wo.standard_operation_id, bso.operation_code, wo.department_id, bd.department_code, bd.location_id, wo.description, wo.scheduled_quantity, decode(wo.quantity_in_queue, 0, null, wo.quantity_in_queue), decode(wo.quantity_running, 0, null, wo.quantity_running), decode(wo.quantity_waiting_to_move, 0, null, wo.quantity_waiting_to_move), decode(wo.quantity_rejected, 0, null, wo.quantity_rejected), decode(wo.quantity_scrapped, 0, null, wo.quantity_scrapped), decode(wo.quantity_completed, 0, null, wo.quantity_completed), wo.first_unit_start_date, wo.first_unit_completion_date, wo.last_unit_start_date, wo.last_unit_completion_date, wo.previous_operation_seq_num, wo.next_operation_seq_num, wo.count_point_type, decode(wo.count_point_type, 1, 1, 2) count_point_flag, decode(wo.count_point_type, 3, 2, 1) autocharge_flag, wo.backflush_flag, wo.minimum_transfer_quantity, wo.date_last_moved, wo.attribute_category, wo.attribute1, wo.attribute2, wo.attribute3, wo.attribute4, wo.attribute5, wo.attribute6, wo.attribute7, wo.attribute8, wo.attribute9, wo.attribute10, wo.attribute11, wo.attribute12, wo.attribute13, wo.attribute14, wo.attribute15, wo.operation_yield, wo.cumulative_scrap_quantity, wo.operation_yield_enabled, nvl(wo.operation_completed, 'n'), wo.shutdown_type, lu1.meaning, wo.x_pos, wo.y_pos, wo.long_description, wo.disable_date, wo.recommended, wo.progress_percentage, wo.wsm_bonus_quantity, wo.actual_start_date, wo.actual_completion_date, wo.employee_id, pap.full_name, wo.lowest_acceptable_yield, nvl(wo.check_skill, 2) check_skill from bom_departments bd, bom_standard_operations bso, wip_operations wo, mfg_lookups lu1, per_all_people_f pap where bd.department_id = wo.department_id and bso.standard_operation_id(+) = wo.standard_operation_id and nvl(bso.operation_type, 1) = 1 and bso.line_id is null and lu1.lookup_type(+) = 'bom_eam_shutdown_type' and lu1.lookup_code(+) = wo.shutdown_type and wo.employee_id = pap.person_id(+) order by wo.operation_seq_num;
我靠视图里面有 order by ...... 这不是脑残吗？ 视图里面你搞order by 干嘛呢，直接在 视图外面写order by 呀。
select .... from a, v_b where a.id=b.id;
a 是一个表， v_b 是一个视图。 v_b 里面有order by 那么 v_b 是有序的， v_b 里面没order by 那么v_b 是无序的。
但是最终的 sql 返回结果 有没有顺序 是 在 最外面 搞 order by 对吧。
所以让 那哥们把视图里面的order by 给去掉 ，结果里面返回结果了。 执行计划就不 贴了，视图里面有 order by 会干扰执行计划的。
别在视图里面搞order by ，如果有需要 ，请在 外面sql 进行order by。