Re: Design efficient pagination query

Thank you so much Sayan and Andy.

I think the idea of having window function for retrieving the count of total records I. E "using Count(*) over() " In the same query in one shot looks promising. It will print same count results for all the rows, but I believe UI code can pick just one and use the same value to decide the number of pages.

Regarding scrollable cursor, I am not aware much. But in current query it just shows first 100 rows. basically , the intent is to get benefit of the first_row optimization , so that user would be able to see the initial set of rows in quick time. But if we go for scrollable cursor, then it has to switch to full_row optimization, which may take more time for user to see the initial results set. Is my understanding correct here?

On Fri, 15 Sept, 2023, 2:12 am Sayan Malakshinov, <xt.and.r_at_gmail.com> wrote:

> 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