Oracle implements SQL syntax aggregation for paging queries and oracle paging SQL syntax

Oracle implements SQL syntax aggregation for paging queries and oracle paging SQL syntax

The examples in this article summarize the SQL syntax for Oracle paging query, which is for your reference. The details are as follows:

1. There is no order by statement. (Most efficient)

After testing, this method has the lowest cost, with only one layer nested and the fastest speed! Even if the queried data volume is large, it is almost unaffected and the speed is still high!

The SQL statement is as follows:

SELECT * FROM (Select ROWNUM AS ROWNO, T.*      from k_task T     where Flight_date between to_date('20060501', 'yyyymmdd') and        to_date('20060731', 'yyyymmdd')      AND ROWNUM <= 20) TABLE_ALIASWHERE TABLE_ALIAS.ROWNO >= 10;

2. There are order by sorting statements. (Most efficient)

After testing, this method slows down as the query range expands!

The SQL statement is as follows:

SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO      FROM (Select t.*          from k_task T          where flight_date between to_date('20060501', 'yyyymmdd') and             to_date('20060531', 'yyyymmdd')          ORDER BY FACT_UP_TIME, flight_no) TT     WHERE ROWNUM <= 20) TABLE_ALIASwhere TABLE_ALIAS.rowno >= 10;

3. There is no order by statement. (Method 1 is recommended)

This method slows down as the query data volume expands!

The SQL statement is as follows:

SELECT * FROM (Select ROWNUM AS ROWNO, T.*      from k_task T     where Flight_date between to_date('20060501', 'yyyymmdd') and        to_date('20060731', 'yyyymmdd')) TABLE_ALIASWHERE TABLE_ALIAS.ROWNO <= 20  AND TABLE_ALIAS.ROWNO >= 10;TABLE_ALIAS.ROWNO between 10 and 100;

4. There is a way to sort order by (method 2 is recommended)

This method will slow down as the query range expands!

The SQL statement is as follows:

SELECT * FROM (SELECT TT.*, ROWNUM AS ROWNO      FROM (Select *          from k_task T          where flight_date between to_date('20060501', 'yyyymmdd') and             to_date('20060531', 'yyyymmdd')          ORDER BY FACT_UP_TIME, flight_no) TT) TABLE_ALIASwhere TABLE_ALIAS.rowno BETWEEN 10 AND 20;

5. Alternative syntax. (Order by Statement)

This syntax is different from the traditional SQL syntax and is not easy to read and understand. It is not recommended for standardization and unified standards. The code is pasted here for your reference.

The SQL statement is as follows:

With partdata as( SELECT ROWNUM AS ROWNO, TT.* FROM (Select *         from k_task T         where flight_date between to_date('20060501', 'yyyymmdd') and            to_date('20060531', 'yyyymmdd')         ORDER BY FACT_UP_TIME, flight_no) TT  WHERE ROWNUM <= 20)  Select * from partdata where rowno >= 10;

6. Alternative syntax. (No order by Statement)

With partdata as( Select ROWNUM AS ROWNO, T.*  From K_task T  where Flight_date between to_date('20060501', 'yyyymmdd') and     To_date('20060531', 'yyyymmdd')   AND ROWNUM <= 20)  Select * from partdata where Rowno >= 10; 

I believe that the code described in this article can provide some reference for you.

Implement oracle paging SQL statements

The following is a method of programming with ORACLE database pl/SQL:
------------------- Create a package --------------------------
Create or replace package pages_query_pak
Type pages_cursor is ref cursor; -- set a cursor to save the result set obtained by the data query.
End pages_query_pak;
------------------ Create a process ----------------------
Create or replace procedure pages_pro (
TableName in varchar2,
PageSize in number, -- number of records displayed on each page
PageNow in number, -- display page number
MyRows out number, -- total number of records
PageCount out number, -- total number of pages
Page_cursor out pages_query_pak.pages_cursor -- the returned record set uses the package above.
) Is
V_ SQL varchar2 (1000 );
V_begin number: = (pageNow-1) * pageSize + 1;
V_end number: = pageNow * pageSize;
Begin
V_ SQL: = 'select * from (select bt. *, rownum rnum from (select * from '| tableName |') bt
Where rownum <= '| v_end |') where rnum> = '| v_begin;
Open page_cursor for v_ SQL;
-- Calculate myRows and pageCount

V_ SQL: = 'select count (*) from' | tableName;
Execute immediate v_ SQL into myRows;
If mod (myRows, pageSize) = 0 then
PageCount: = myRows/pageSize;
Else
PageCount: = myRows/pageSize + 1;
End if;
End;

SQL paging query and oracle paging query Conversion

Select top "+ (end-begin) + ",
Numbers,
State,
Title,
Deptname,
Time,
Fromuser
From
(Select oa_alarm.alarm_number numbers, oa_alarm.alarm_title title, expiration time, oa_user.user_realname fromuser, oa_alarm.alarm_state state, oa_dept.dept_name deptname from
Oa_alarm, oa_user, oa_dept where oa_alarm.alarm_fromuser =
Oa_user.user_number and oa_alarm.alarm_dept = oa_dept.dept_number
Deptsql order by oa_alarm.alarm_time desc)

Where numbers not in (select top "+ begin +", numbers from (select oa_alarm.alarm_number numbers from
Oa_alarm, oa_user, oa_dept where oa_alarm.alarm_fromuser =
Oa_user.user_number and oa_alarm.alarm_dept = oa_dept.dept_number
Deptsql order by oa_alarm.alarm_time desc ))
You can try it.