title: Fetch records with DISTINCT and rownum
body: <p>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 <code>rownum &lt;11</code>. But it is showing only 3-4 records.Duplicate <code>header_ids</code> are present.Can we use DISTINCT and rownum here to get 10 rows. Is there any other way to solve this issue??</p>
  
  <pre><code>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
  </code></pre>