腦殘的設計--- 視圖(view)裡麵包含order by

今天有個兄弟跟我說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。