Re: Native Hash Full Join and Pagination Query 11g
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: stalinsk@xxxxxxxxx, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Nov 2009 19:54:47 +0000
Stalin
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@xxxxxxxxx>
> 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.
>
Other related posts:
