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

Thread: Oracle ERP query tuning - restricting rows

  1. #1
    Join Date
    Mar 2010
    Location
    Los Angeles
    Posts
    20

    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.line_number
    || '.'
    || 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,
    (SELECT OID
    FROM xxatazt.quote q
    WHERE (OID) =
    TO_NUMBER
    (SUBSTR (NVL ((d18al3.attribute4),
    (d18al1.attribute13
    )
    ),
    2,
    6
    )
    )
    AND isnumeric (SUBSTR (NVL ((d18al3.attribute4),
    (d18al1.attribute13
    )
    ),
    2,
    6
    )
    ) = '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 =
    d18al3.line_id_char
    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
    Location
    Charlotte, NC
    Posts
    865
    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.

    Thanks,
    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