CODEWITHSUNDEEP
sqlperformance
Nidheesh
Nidheesh

Reputation: 4562

Fetch records with DISTINCT and rownum

I was facing a performance issue while executing following query.Takes too much time to fetch the results.It was fetching whole results(say 3000)at a time.I was trying to fetch 10 records by using rownum <11. But it is showing only 3-4 records.Duplicate header_ids are present.Can we use DISTINCT and rownum here to get 10 rows. Is there any other way to solve this issue??

SELECT DISTINCT oh.header_id,
oh.cust_po_number purchase_order,
oh.order_number,
DECODE(oh.orig_sys_document_ref, NULL, oh.orig_sys_document_ref, SUBSTR(oh.orig_sys_document_ref, LENGTH('OE_ORDER_HEADERS_ALL')+1)) web_reference_number,
TO_CHAR(oh.ordered_date, 'FMMon-DD-YYYY') date_ordered,
(SELECT MIN(schedule_ship_date)
FROM oe_order_lines_all
WHERE header_id = oh.header_id
And Oh.Sold_To_Org_Id = 12338
) oldest_schedule_ship_date,
oe_totals_grp.get_order_total(ol.header_id,NULL,'ALL') total_value,
oh.transactional_curr_code currency_code,
COUNT(*) over() AS total_count
FROM oe_order_headers_all oh,
oe_order_lines_all ol
Where Oh.Header_Id = Ol.Header_Id
-- AND ol.actual_shipment_date BETWEEN sysdate - 180 AND sysdate
AND oh.sold_to_org_id = 12338

Upvotes: 0

Views: 1329

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

If you are using rownum, then I presume you are using Oracle.

Oracle does the distinct after applying rownum rather than before. Alas. I think this is part of the same logic where order by is applied afterwards as well.

You can fix it by using a subquery:

select s.*
from (<your query here>) s
where rownum < 11;

Part of your performance problem may be due to the subquery in the select. I think you can replace it with an analytic function:

SELECT DISTINCT oh.header_id,
      oh.cust_po_number purchase_order, oh.order_number,
      DECODE(oh.orig_sys_document_ref, NULL, oh.orig_sys_document_ref,
      SUBSTR(oh.orig_sys_document_ref, LENGTH('OE_ORDER_HEADERS_ALL')+1)) web_reference_number,
      TO_CHAR(oh.ordered_date, 'FMMon-DD-YYYY') date_ordered,
      min(schedule_ship_date) over (partition by oh.header_id) as oldest_schedule_ship_date,
      oe_totals_grp.get_order_total(ol.header_id,NULL,'ALL') total_value,
      oh.transactional_curr_code currency_code,
      COUNT(*) over() AS total_count
FROM oe_order_headers_all oh join
     oe_order_lines_all ol
     on Oh.Header_Id = Ol.Header_Id

-- AND ol.actual_shipment_date BETWEEN sysdate - 180 AND sysdate where oh.sold_to_org_id = 12338

Actually, to me, it looks like the whole query should be written using group by rather than distinct with analytic functions. That might help the performance as well.

Upvotes: 3

Related Questions