Oracle ERP query tuning - restricting rows Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Oracle ERP query tuning - restricting rows

  1. #1
    Join Date
    Mar 2010
    Los Angeles

    Oracle ERP query tuning - restricting rows

    Hi guys,

    The below query returns millions of row and all the tables are Oracle ERP tables. I need to restrict rows to 1 year. What is the best way ?

    I cannot to go with materialized view and partitioning as per my company rules as of now

    SELECT d18al1.header_id AS header_id,
    d18al1.org_id AS org_id,
    d18al1.order_number AS order_number,
    d18al1.attribute13 AS oeh_attr13,
    d18al3.attribute4 AS oel_attr4,
    d18al2.NAME AS operating_units,
    d18al1.expiration_date AS oeh_expiration_6,
    d18al1.ordered_date AS oeh_ordered_date,
    d18al1.sold_to_org_id AS oeh_sold_to_org_id,
    d18al1.request_date AS oeh_request_date,
    d18al1.attribute8 AS oeh_cfd_attribute8,
    d18al1.booked_date AS oeh_booked_date,
    d18al1.transactional_curr_code AS oeh_transaction12,
    d18al1.cust_po_number AS oeh_cust_po_number,
    d18al1.booked_flag AS oeh_booked_flag,
    d18al1.cancelled_flag AS oeh_cancelled_flag,
    d18al1.open_flag AS oeh_open_flag,
    TO_CHAR (d18al3.line_id) AS line_id,
    d18al3.order_line_number AS order_line_number,
    d18al3.inventory_item_id AS inventory_item_id,
    d18al3.creation_date AS line_creation_date,
    d18al3.request_date AS request_date,
    d18al3.promise_date AS promise_date,
    d18al3.schedule_ship_date AS schedule_ship_date,
    d18al3.schedule_arrival_date AS schedule_arriva24,
    d18al3.actual_arrival_date AS actual_arrival_25,
    d18al3.actual_shipment_date AS actual_shipment26,
    d18al3.cancelled_flag AS cancelled_flag,
    d18al3.open_flag AS open_flag,
    d18al3.booked_flag AS booked_flag,
    d18al3.fulfilled_flag AS fulfilled_flag,
    d18al3.fulfillment_date AS fulfillment_date,
    d18al3.ordered_quantity AS ordered_quantity,
    d18al3.fulfilled_quantity AS fulfilled_quantity,
    d18al3.shipping_quantity AS shipping_quantity,
    d18al3.cancelled_quantity AS cancelled_quantity,
    d18al3.shipped_quantity AS shipped_quantity,
    d18al3.invoiced_quantity AS invoiced_quantity,
    d18al3.pricing_quantity AS pricing_quantity,
    d18al3.returned_quantity AS returned_quantity,
    d18al3.unit_selling_price AS unit_selling_price,
    d18al3.extended_selling_price AS extended_sellin41,
    d18al3.returned_amount AS returned_amount,
    d18al3.flow_status_code AS line_flow_statu43,
    d18al3.delivery_lead_time AS delivery_lead_time,
    d18al3.line_type_id AS line_type_id,
    d18al3.ship_from_org_id AS ship_from_org_id,
    d18al3.ship_to_org_id AS ship_to_org_id,
    d18al3.invoice_to_org_id AS invoice_to_org_id,
    d18al3.ship_to_site_use_id AS ship_to_site_us49,
    d18al3.bill_to_site_use_id AS bill_to_site_us50,
    || '.'
    || d18al3.shipment_number AS shipment_number,
    d18al1.attribute9 AS ack_site_use_id,
    d18al3.org_id AS line_org_id,
    d18al5.bill_to_customer_id AS invoice_bill_to54,
    d18al5.bill_to_site_use_id AS invoice_bill_to55,
    d18al5.ship_to_customer_id AS invoice_ship_to56,
    d18al5.ship_to_site_use_id AS invoice_ship_to57,
    d18al4.customer_trx_line_id AS customer_trx_li58,
    d18al3.line_id_char AS line_id_char,
    d18al4.interface_line_attribute6 AS interface_line_60,
    d18al3.org_id AS oel_org_id,
    d18al4.org_id AS inv_line_org_id,
    d18al3.salesrep_id AS salesrep_id,
    d18al1.cust_po_number AS cust_po_number,
    d18al1.order_type_id AS order_type_id,
    d18al3.tp_attribute3 AS customer_part,
    d18al3.shipping_method_code AS shipping_method67,
    d18al3.attribute10 AS line_ult_dest_c68,
    FROM xxatazt.quote q
    WHERE (OID) =
    (SUBSTR (NVL ((d18al3.attribute4),
    AND isnumeric (SUBSTR (NVL ((d18al3.attribute4),
    ) = 'TRUE') AS quote
    FROM ont.oe_order_headers_all d18al1,
    briousr.hr_operating_units d18al2,
    briousr.xxbi_oe_order_lines_all_v d18al3,
    ar.ra_customer_trx_lines_all d18al4,
    ar.ra_customer_trx_all d18al5
    WHERE ( d18al2.organization_id(+) = d18al1.org_id
    AND d18al1.header_id = d18al3.header_id
    AND d18al4.interface_line_attribute6 =
    AND d18al5.customer_trx_id = d18al4.customer_trx_id
    AND d18al4.org_id = d18al3.org_id
    AND d18al4.interface_line_context = 'ORDER ENTRY'

  2. #2
    Join Date
    Mar 2006
    Charlotte, NC
    how can some one tell that?? that's your shop, that's your query, you need to discuss with your application team / business to identify the column to restrict the rows.

    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width