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'
)
