SELECT returns a range of records?

A

Ahmed Moustafa

Hi All,

How can a SELECT statement SQL return a certain range of the records of
the result set e.g. say the total number of records is 100 records and I
am interested only the records from 10 to 20?

DB: DB2 UDB 7.0
OS: AIX

Thanks in advance,
Ahmed
 
R

Roedy Green

say the total number of records is 100 records and I
am interested only the records from 10 to 20?

Either you figure out a way to describe your query to prune down what
you want, or you wade through result records 1 to 9, read 10 to 20
then dismiss the result set. Don't feel guilty. The database does
not actually compose the entire result set when you submit your query.
 
A

Ahmed Moustafa

Roedy said:
Either you figure out a way to describe your query to prune down what
you want, or you wade through result records 1 to 9, read 10 to 20
then dismiss the result set. Don't feel guilty. The database does
not actually compose the entire result set when you submit your query.

I remember it was possible in Oracle to qualify the query by the record
number, I am looking for something equivalent in DB2 and the only
objective is to improve the performance of the query.
 
L

Lothar Kimmeringer

How can a SELECT statement SQL return a certain range of the records of
the result set e.g. say the total number of records is 100 records and I
am interested only the records from 10 to 20?

LIMIT should be the statement of your choice. How it is used
you should be able to find in your database-manual.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!
 
A

Ahmed Moustafa

It seems DB2 does not have Oracle's "ROWNUM" not only that but JDBC does
not like DB2's "FETCH" :(
 
J

Joachim Müller

Ahmed,

this works...

get rows from 10 to 20

row number function:

SELECT * FROM
(SELECT name, rownumber() over
(order by name)
AS rn FROM address)
AS tr WHERE rn between 10 and 20

Regards, Joachim
 
J

Jason

Depends on where you want to control it and how reliable you feel
about it being in the first 10-20 records, or if what you're really
after is paging behavior in which case things change a bit.

Assumptions (Potential hazard) : You're running server side java in a
browser implementation wherein, your interface (jsp) elements are
displayed in a browser and you want the operational (behavioral) code
on the server side. Basic web implementation type of thing. If
you're doing a desktop swing app or similar your mileage will vary.

One thing I've done in similar situations was to actually bring back
all of the primary keys I might need, for example, search returns a
result of 1000 records, but I'm only getting the primary keys and
maybe a descriptor. I put ALL of the records into objects which get
stored in a wrapper class that has the behavior for paging the 1000
records in pages of say 25. The wrapper class has to be able to keep
up with the page size and the current page which yields an index range
of objects in the collection that will allow you to perform paging
behavior without having to A) go back to the database over and over
again. (You actually will go back to the database, but not to get
this list or any part of it) and B) to more efficiently manage how
much information your pulling from the database at any given time.

In my experience, RDBMS systems and networks do not like moving large
blocks of information, they'll do it, but they're happier moving lots
of little bits of information. This approach allows you to do that.
It's semantically a different way of thinking about doing data
exchange.

If you're interested I have a wealth of information available and
would be happy to share. Feel free to email me.
 
A

Ahmed Moustafa

Joachim said:
Ahmed,

this works...

get rows from 10 to 20

row number function:

SELECT * FROM
(SELECT name, rownumber() over
(order by name)
AS rn FROM address)
AS tr WHERE rn between 10 and 20

Regards, Joachim

It works perfectly fine BUT performance-wise, it does not improve the
performance, does it? (The inner SELECT still returns the whole result set.)

My main objective is to improve the performance of that query. My
understanding is that one of the parameters of the response time is the
number of records in the result set so I am looking for retrieving only
the records that I was interested in to improve the performance.
 
A

Ahmed Moustafa

Joachim said:
Ahmed,

this works...

get rows from 10 to 20

row number function:

SELECT * FROM
(SELECT name, rownumber() over
(order by name)
AS rn FROM address)
AS tr WHERE rn between 10 and 20

Regards, Joachim

It works perfectly fine BUT performance-wise, it does not improve the
performance, does it? (The inner SELECT still returns the whole result set.)

My main objective is to improve the performance of that query. My
understanding is that one of the parameters of the response time is the
number of records in the result set so I am looking for retrieving only
the records that I was interested in to improve the performance.
 
S

soft-eng

Ahmed Moustafa said:
It works perfectly fine BUT performance-wise, it does not improve the
performance, does it? (The inner SELECT still returns the whole result set.)

My main objective is to improve the performance of that query. My
understanding is that one of the parameters of the response time is the
number of records in the result set so I am looking for retrieving only
the records that I was interested in to improve the performance.

Databases don't actually execute statements the way you think; typically
such a statement would be read in its entirety, optimized, a "plan"
made, and then the data would be retrieved. So as long as you
can tell the database clearly what you want, it will do that
in some sort of optimized manner.

Of course, this can vary from vendor to vendor. But IBM
has been in the database business for a while...

The best thing to do is to run some performance tests.
 
S

Steve

What has this got to do with Java? Please stop X-posting on to
c.l.java.programmer.

Thanks.


Hi All,

How can a SELECT statement SQL return a certain range of the records of
the result set e.g. say the total number of records is 100 records and I
am interested only the records from 10 to 20?

DB: DB2 UDB 7.0
OS: AIX

Thanks in advance,
Ahmed


~ If emailing, please use: Steve_A_Haigh
~ @
~ hotmail.com
~
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top