脑残的设计---视图(view)里面包含orderby
2020-11-09
来源：个人技术集锦
今天有个兄弟跟我说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(p_po_num_t, pha.segment1)*/ '621337540' AND nvl(pha.approved_date, SYSDATE + 9999) >= 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。