Hi guys,
I'm doing JSP paging. I have this SQL statement running on Oracle.
"SELECT * FROM (SELECT * FROM USER_TABLE WHERE NAME = 'test1') WHERE ROWNUM > 0 AND ROWNUM <= 10"
For the ROWNUM >0, the Oracle able to return the 10 records to be displayed in the page.
For the ROWNUM >10, I'm unable to locate any records although I have more than 10 rows of records in the table. This amaze me...any thought?
0..10
"SELECT * FROM (SELECT * FROM USER_TABLE WHERE NAME = 'test1') WHERE ROWNUM > 0 AND ROWNUM <= 10"
10..20
"SELECT * FROM (SELECT * FROM USER_TABLE WHERE NAME = 'test1') WHERE ROWNUM > 10 AND ROWNUM <= 20"
20..30
"SELECT * FROM (SELECT * FROM USER_TABLE WHERE NAME = 'test1') WHERE ROWNUM > 20 AND ROWNUM <= 30"
...
Hope That Helps
If I were you I'd leave the query as:
SELECT * FROM USER_TABLE WHERE NAME = 'test1'
ignoring the first n results and only reading the following ten where n is the start of the paging. (0, 10, 20 ...)
There are three reasons why:
1) The SQL would be ANSI SQL and not DB specific.
2) Clarity. I think it's much simpler.
3) Performance:
It may seem efficient to only return the rows from the database that you need particularly if this is a big table but you're gaining very little by doing this other than perhaps a more consistent running time.
However it is not necessarily efficient. You inner query will get all results before returning. My example will stop getting results the moment the 10 are found. You can argue that all the results will be cached so the first page will be the slowest to retrieve and the remaining pages might be from the cache. I guess 3 isn't a strong point but it's a consideration.
> 0..10
> "SELECT * FROM (SELECT * FROM USER_TABLE WHERE
> NAME = 'test1') WHERE ROWNUM > 0 AND ROWNUM <= 10"
>
>
> 10..20
> "SELECT * FROM (SELECT * FROM USER_TABLE WHERE
> NAME = 'test1') WHERE ROWNUM > 10 AND ROWNUM <= 20"
>
>
> 20..30
> "SELECT * FROM (SELECT * FROM USER_TABLE WHERE
> NAME = 'test1') WHERE ROWNUM > 20 AND ROWNUM <= 30"
>
>
> ...
>
> Hope That Helps
How exactly would this help? How exactly does this answer the OP's question in any way?
I forget to mention:
AS I remember ROWNUM is a funny thing. It is the number of the row that is being returned. In this case its the row number of your whole query not the inner query.
So rownum < 3
1.true
2.true
3.false
makes perfect sense but consider >= 3
1.false;
1.false;
1.false;
You never satisfy the condition as your first result has not been returned so every row is potentially the first row to be returned and therefore fails the >= 3 test.
Does that make any sense? I might be wrong it's been a while. :)
