Paging With a Cartesian Product

Question

Please do add a comment if more information is needed to answer this question.

We have a query that returns a cartesian product. Since there are a lot of results, there are potentially a ton of objects that get hydrated. We'd like to do simple paging on the database end so it's fast, and so it only pulls n results starting at row n*page where n is say, 100.

The trouble we're running into is that a distinct is being done on the query so as to only return the necessary rows. But, this ends up filtering the results so you get less than 100.

Because of this, even though we have the page number, we would have no idea where to start or stop without doing the query to first see the starting point (i.e. we can't use rownum), and potentially looping until we got 100 results, you get the idea. It's messy.

If needed we can do straight up SQL, but something like refactoring the table isn't an option.

Either way I can't imagine we're the first ones to ever encounter the situation, so I'd like to know if there's a way to do this.

Thanks in advance!

EDIT: @nicolas78 posted a comment which exactly describes the situation.

Answer 1

You need to push the DISTINCT further into the query, so that it has been done before pagination is applied. For example:

select deptno from
  ( select deptno, rownum as rn from
      ( select DISTINCT deptno
        from   emp
        order by deptno
      )
    where rownum < :pagenum*100
  )
where rn > (:pagenum-1)*100;