Brainchild design --- view contains orderby

Today, a brother told me that SQL runs too slowly. Let me see. The SQL statement is as follows: SELECTrownumrow_num, pv. vendor_name, pha. Forward, prh. preparer_id, pha. Org_Id, pha. po_header_id, wo. department_code, wo. Forward, to_char

Today, a brother told me that SQL runs too slowly. Let me see. The SQL statement is as follows: 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

Today, a brother told me that SQL runs too slowly. Let me see. The SQL statement is as follows:

 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;

Quickly use the SQL three-segment splitting method (shared) to scan and find that there is no problem (if you do not know the Buddy, please Baidu falls into the SQL three-segment splitting method)

SQL contains the following view code:

/*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;

I rely on ORDER BY... in the view. Isn't that my brain? What are you doing with order by in the view? Write order by directly outside the view.

Select... from a, v_ B where a. id = B. id;

A is a table and v_ B is a view. If order by exists in v_ B, then v_ B is ordered. If order by exists in v_ B, then v_ B is unordered.

But does the final SQL return result have order by in the outermost order.

So let the buddy remove the order by in the view and return the result in the result. The execution plan will not be pasted, and order by in the view will interfere with the execution plan.

Do not create order by in the view. If necessary, perform order by in an SQL statement.