Oracle i podział na podstrony Opcje

[mroowa]

Witam,
mam problem taki jak w temacie.

Robie zapytanie do Oracle które zwraca mi dużo wyników, potrzebuje podzielić je na podstrony w php.
Coś jak w MySQL

[SQL] pobierz, plaintext 
SELECT * FROM tabela WHERE STATUS = 'T' LIMIT 0, 20
[SQL] pobierz, plaintext 

a na następnej podstronie

[SQL] pobierz, plaintext 
SELECT * FROM tabela WHERE STATUS = 'T' LIMIT 21, 20
[SQL] pobierz, plaintext 

Jak coś takiego zrobić na oracle, słyszałem coś o FETCH, ale nie wiem z czym to sie je

Z góry dzięki za pomoc.

[bigZbig]

http://www.softax.pl/prywatne/marcink/pora...racle/968783673

edit
Specjalnie dla Ciebie fragment dokumentacji


Pagination with ROWNUM

My all-time-favorite use of ROWNUM is pagination. In this case, I use ROWNUM to get rows N through M of a result set. The general form is as follows:

Kod

select *
  from ( select /*+ FIRST_ROWS(n) */
  a.*, ROWNUM rnum
      from ( your_query_goes_here,
      with order by ) a
      where ROWNUM <=
      :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

where

• FIRST_ROWS(N) tells the optimizer, "Hey, I'm interested in getting the first rows, and I'll get N of them as fast as possible."
• :MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.
• :MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.

The concept behind this scenario is that an end user with a Web browser has done a search and is waiting for the results. It is imperative to return the first result page (and second page, and so on) as fast as possible. If you look at that query closely, you'll notice that it incorporates a top-N query (get the first :MAX_ROW_TO_FETCH rows from your query) and hence benefits from the top-N query optimization I just described. Further, it returns over the network to the client only the specific rows of interest—it removes any leading rows from the result set that are not of interest.

One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so. If you sort 100 records by SALARY, for example, and they all have the same SALARY value, then specifying rows 20 to 25 does not really have any meaning. In order to see this, use a small table with lots of duplicated ID values:

Kod

SQL> create table t
  2  as
  3  select mod(level,5) id,
     trunc(dbms_random.value(1,100)) data
  4    from dual
  5  connect by level <= 10000;

Table created.
And then query rows 148 to 150 and 151 after sorting by the ID column:

Kod

SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id) a
  8   where rownum <= 150
  9  )
10   where rnum >= 148;

Kod

ID           DATA           RNUM
-------       ----------     -----------
0             38             148
0             64             149
0             53             150

Ten post edytował bigZbig 17.10.2006, 13:48:41

[Blackhole]

A dlaczeto takie zapytanie

[SQL] pobierz, plaintext 
SELECT * FROM scott.emp WHERE ROWNUM <= 10 AND ROWNUM > 3
[SQL] pobierz, plaintext 

nie zwraca nic?

[KILIUSZKIN]

A dlaczeto takie zapytanie

[SQL] pobierz, plaintext 
SELECT * FROM scott.emp WHERE ROWNUM <= 10 AND ROWNUM > 3
[SQL] pobierz, plaintext 

nie zwraca nic?

Rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum. After issuing a select statement, one of the last steps that oracle does is to assign an increasing (starting with 1, increased by 1) number to each row returned. The value of this row number can always be queried with rownum in a select statement:

[SQL] pobierz, plaintext 
--------------
SELECT
  rownum, column_1, column_2 
FROM table_1, table_2
WHERE field_3 = 'some value'
[SQL] pobierz, plaintext 

It is important to realize that the first row's rownum is always 1. This implies that the following query won't return a single row:

[SQL] pobierz, plaintext 
SELECT 
  column_1, column_2 
FROM table_1, table_2
WHERE field_3 = 'some value' AND rownum > 5
[SQL] pobierz, plaintext 

This is so because the first row would have to meet the following two mutually excluding criterias:
rownum is 1
rownum is 6 (rownum > 5)
In order to do this query in the (probably) intended spirit, a sub-query must be executed:

[SQL] pobierz, plaintext 
SELECT 
  column_1, column_2 
FROM ( SELECT 
    rownum r_, column_1, column_2
  FROM table_1, table_2
  WHERE field_3 = 'some value'
)
WHERE r_ > 5
[SQL] pobierz, plaintext 

[Blackhole]

Dzięki.
A skąd wziąłeś to wyjaśnienie?

[KILIUSZKIN]

Dzięki.
A skąd wziąłeś to wyjaśnienie?

stąd