RE: Native Hash Full Join and Pagination Query 11g

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 3 Nov 2009 14:59:41 -0500
Message-ID: <90BC89ED4B664A588ACA2D63E620E0EC_at_rsiz.com>

you've changed the order by since the original posting. which order by do you actually want? a.actID versus c.name, a.actID

How do you want null values to sort for the sort order you actually want?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stalin
Sent: Tuesday, November 03, 2009 1:02 PM To: Dion Cho
Cc: oracle-l
Subject: Re: Native Hash Full Join and Pagination Query 11g

No Luck there. Here is the revised Plan.

SELECT * FROM (
   SELECT tmp.*, rownum rnum FROM (

      SELECT /*+ opt_param('_optimizer_native_full_outer_join', 'off') use_nl no_native_full_outer_join */ c.customerId, subscriberId, name, city, phone1, email1, a.actId

      FROM Accounts a FULL JOIN Customer c ON a.customerid = c.customerid ORDER BY c.name, a.actId

   ) tmp WHERE rownum <= 10
) WHERE rnum >= 1

<snip>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 03 2009 - 13:59:41 CST