Paging in SQL 2

[RamHardikar]

How can I do paging in SQL

 

[KenCunningham]

Can you be more specific about your requirement, please? set pages = <number> might do for you but it's better if we see exactly what you want to do.

 

[DBAwhosaysNIE]

that's also a SQL*Plus formatting command, not SQL per se (i.e. won't work across JDBC, etc.)

he could also mean sending notifications to a pager (ex. grep ORA- alert_$ORACLE_SID.log | mail -s"ORA- errors in alsrt file:" wakeupdba@widgets.acme.com)

in either context it's not a SQL question per se but there may be one where it is.

 

[RamHardikar]

i dont want to fetch entire data at one go. but just get what is required, cud be 10 records in a bunch

 

[SantaMufasa]

Actually, Ram, I don't know if Oracle provides exactly what you are asking. The closest parameter that Oracle offers seems to be, "DB_FILE_MULTIBLOCK_READ_COUNT". This parameter specifies how many blocks will be read at once when Oracle performs a full table scan. Oracle warns against setting this either too high or too low (which causes the Oracle cost-based optimizer to either do too many full table scans, or too many index scans, respectively).

Is this parameter what you were looking for?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[RamHardikar]

i guess i need to do something like this -

SQL - select id, nickname, name from people where rownum between 0 and 20 order by 1

but if i change the WHERE condition to - rownum between 20 and 40, it does not fetch me any records, though the table holds about 500 records.

can anyone tell me why?

 

[hoinz]

[thread186-1024041]

 

[SantaMufasa]

Ram,

The problem you are having relates to the behaviour of ROWNUM. ROWNUM increments (and assigns) its value as the row "enters" the result set. ROWNUM's values can only be positive (not zero). As an example of how ROWNUM works and why you received no rows of output when you requested rows whose rownum between 20 and 40, let's first look at all the rows of a table, then let's be selective about those rows:

Section 1 -- All the rows:

col a heading "Employee Name" format a20
select rownum,first_name||' '||last_name a from s_emp;

ROWNUM Employee Name
------ --------------------
     1 Carmen Velasquez
     2 LaDoris Ngao
     3 Midori Nagayama
     4 Mark Quick-To-See
     5 Audry Ropeburn
     6 Molly Urguhart
     7 Roberta Menchu
     8 Ben Biri
     9 Antoinette Catchpole
    10 Marta Havel
    11 Colin Magee
    12 Henry Giljum
    13 Yasmin Sedeghi
    14 Mai Nguyen
    15 Andre Dumas
    16 Elena Maduro
    17 George Smith
    18 Akira Nozaki
    19 Vikram Patel
    20 Chad Newman
    21 Alexander Markarian
    22 Eddie Chang
    23 Radha Patel
    24 Bela Dancs
    25 Sylvie Schwartz

Section 2 -- Test...let's try to see rows 20-40:

select rownum, first_name||' '||last_name a
from s_emp
where rownum between 20 and 40;

no rows selected.

So why didn't we see at least rows 20-25? Here is a row-by-row analysis of what Oracle does:

step 1: fetch a row

step 2: Oracle outputs this row if this row will be the 20th-to-40th row to make it to the result set. Since this row will be ROWNUM 1 to make it to the result set, it eliminates this row.

step 3: fetch the next row

step 4: Oracle outputs this row if this row will be the 20th-to-40th row to make it to the result set. Since this row will be ROWNUM 1 to make it to the result set, it eliminates this row.

step 5: fetch the next row

step 6: Oracle outputs this row if this row will be the 20th-to-40th row to make it to the result set. Since this row will be ROWNUM 1 to make it to the result set, it eliminates this row.

...Do you see a pattern here? Even if your table has a million rows, under these circumstances, no rows will return.

So, if you want rows "x thru y" from a table (and x > 1), we must first "trick" Oracle into producing a result set which includes row numbers, then filter by the row numbers we want. (Since the example showing how to do what you want includes an ACCEPT statement, you must save it to a script and run the script):

accept row_beg prompt "What is the starting row number?: "
accept row_end prompt "What is the ending row number?: "
select rn,first_name||' '||last_name a
from (select rownum rn,first_name,last_name from s_emp)
where rn between &row_beg and &row_end
order by rn;

(I saved the above script to Ram_test.sql)
@Ram_test
What is the starting row number?: 7
What is the ending row number?: 12

        RN Employee Name
---------- --------------------
         7 Roberta Menchu
         8 Ben Biri
         9 Antoinette Catchpole
        10 Marta Havel
        11 Colin Magee
        12 Henry Giljum

Does this satisfactorily answer your question and need, Ram? Let us know.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[RamHardikar]

Mufasa,
your way of explaining the solution is just great. thx again, it really has helped.

 

[BJCooperIT]

Dave, as always a great explanation!

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Sr. App. Developer Analyst/Custom Forms & PL/SQL - Oracle/Windows
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

Thanks, Ram and Barb...it's always a pleasure to submit stuff when people appreciate what you do.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.