TeraData dedicated pagination support

[timowest]

Efficient TeraData mechanism for result pagination; currently see three options2
a. Run the query again with a select count(*).
b. Cursor to end of result set and skip any between rows [rs.last();int nRowCount = rs.getRow();]
c. Add an Ordered Analytic to the query to have it include the count.

[timowest]

Concerning c. count(*) over() could be used as an additional projection element to get the count out.

[timowest]

Related
https://groups.google.com/forum/#!topic/jooq-user/G9Op6cQwMkY
http://www.inf.unideb.hu/~gabora/pagination/results.html

[lukaseder]

Learning from the best? :-)

[timowest]

I knew this would provoke some comment from you. ;)
Yes, I will try to unify the pagination logic in Querydsl SQL.

[lukaseder]

Hmm, I have to work on my trolling predictability ;-)
Good luck! That's probably the most challenging part of SQL transformation...

[timowest]

@lukaseder Does jOOQ also have single query support for paged data and total count? count(*) over() seems to work for databases that support this function. For other cases Querydsl does two queries.

Good luck! That's probably the most challenging part of SQL transformation...

Yes, it's quite a pita.

[lukaseder]

Does jOOQ also have single query support for paged data and total count? count(*) over() seems to work for databases that support this function.

COUNT(*) OVER() is certainly the best way for those 5-6 databases that support window functions. jOOQ doesn't have any such flags to auto-calculate these things...

[timowest]

Thanks for the confirmation.

[timowest]

Released in 3.3.0