Re: Native Hash Full Join and Pagination Query 11g

Preliminary uestions

• How many customers don't have corresponding accounts?
• Do you care whether these come on the first or last pages?
• Do you want to be able to go at random from page 1 to page N, or only to page 2 (and then from 2 forward to 3 or back to 1)

As you know, you can recast the FULL JOIN query as:

select <a.columns>, <c.columns> from accounts a LEFT JOIN customers c on c.ActId = a.ActId
UNION ALL
select <null columns from a>, <c.columns> from customers c where not exists (select 1 from accounts a where a.customerId = c.customerId)

Now, from your app you can issue your "first page" query against the first query block only (all rows that have an ActId - with or without an CustomerId). Only when your query returns fewer than <pagesize> rows do you need to get any rows from the second query block (all rows - ie customers - *without *an ActId).

On the second and subsequent pages, you ideally want to be able to use a half-bounded range on a.ActId (for the first query block) to ensure that you pull the next <pagesize> rows without having to revisit the first pageful of rows (which is what the pagination query is doing using rn)

select q.* from (
select rownum rn, <a.columns>, <c.columns> from accounts a LEFT JOIN customers c on c.CustomerId = a.CustomerId where a.ActId > [last account id from previous page] order by a.ActId
) q
where rn <= [pagesize]

(I am assuming by the way that the relationship is A > - - - - - C - ie an Account may be owned by one and only one customer; a Customer may be the owner of zero or more accounts)

Note that this can be very efficient but you must keep track of the last value of ActId for each page (so you can page backwards as well as forwards).

Once you start getting the orphan customers from the second query block, you can use [last customer Id from previous page] as the lower bound.

Note: this is good for paging forwards page by page. If you want to be able to go to an arbitrary page, you may need to have a pre-query that calculates once which values act as the page boundaries (ie last ActId in each page for first qery block) for every possible page (or for the Nth page), using the rank() analytic function. Exercise for reader!

Hope that helps

Regards Nigel

2009/11/2 Stalin <stalinsk_at_gmail.com>

> All,
>
> I'm trying to tune a pagination query that uses Full Join but so far i
> had no luck in making it run faster.
>

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