Re: Design efficient pagination query

Hi Yudhi,

The best option in such cases is to use scrollable cursors and add "count() over() as total_cnt" into your query.
In this case, you don't need your second query with "count(*)" as you will have it in the first query, and with scrollable cursor, you will not need to execute your query for each page, you'll be able just scroll your cursor:

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/using-sql_statements-in-oci.html#GUID-1728D572-BED3-4F80-BDC3-E6F9A66046B2

On Thu, 14 Sep 2023, 21:29 yudhi s, <learnerdatabase99_at_gmail.com> wrote:

> Hello All,
>
> We found as part of UI search query we are getting two queries executed
> each time in the database ,because of pagination implementation at
> application level
>
> 1)Getting the required results out of the query based on input search
> criteria which is showing first 100 rows
>
> 2)Another query with exactly same join and input parameter, but its a
> count() query to see the total number of rows from the query.
>
> The count query is used to show the user , how many pages the results
> spans across. And the "limit 100" query actually fetches the all the
> required fields to show up in the same page. And with each page the user
> scrolls to, these two queries executes one after other for every page , in
> the database for every user. This takes more time and also impacts customer
> experience.
>
> While, we were trying to minimize the result set scan with multiple
> strategies so that the query can give first- N results to the UI user as
> quickly as possible, at the same time the count() query has to scan all the
> data to display the total number of rows information. So wanted to know ,
> if by anyway we can combine these two queries to single query? or say can i
> fetch total record count in the same query which fetches the first 100 rows
> for the display on the screen at one hit to the database? Or should we
> change the design someway ?
>
> Select col3, col4,col5
> from table1, table2
> where table1,col1= table2.col1
> order by transaction_date desc
> limit 100 offset 0;
>
> Select count(*)
> from table1, table2
> where table1,col1= table2.col1;
>
> Regards
> Yudhi
>

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