Retrieve N Record.
-
Hi Guys! I am trying to retrieve middle records. The below query is working perfectly. The problem is my table is too huge (more then 10 lak reocrd) it is taking more time. Is there any other solution? [This is sample query]
select *
from (select t.*, rownum rn
from (SELECT *
FROM (select * from Productlist)) t
Where rownum <= 10000)
Where rn > 9990Regards, Nanda
-
Assuming you are using SQL Server, have some Google Foo[^]
Never underestimate the power of human stupidity RAH
-
Try...
select * from (
select top 10000 *, row_number() over (order by rownum) as rn
from productlist
) t
where rn between 9990 and 10000modified on Monday, February 21, 2011 1:26 AM
-
Hi Guys! I am trying to retrieve middle records. The below query is working perfectly. The problem is my table is too huge (more then 10 lak reocrd) it is taking more time. Is there any other solution? [This is sample query]
select *
from (select t.*, rownum rn
from (SELECT *
FROM (select * from Productlist)) t
Where rownum <= 10000)
Where rn > 9990Regards, Nanda
SELECT *
FROM (
SELECT *
FROM Productlist pl
ORDER BY somefield
)
WHERE ROWNUM BETWEEN 9991 AND 10000Note that you can never trust the DB to give you the records in the same order every time, so you need the order by clause. The field you're ordering by should be indexed if you want to get any speed to your query
-
SELECT *
FROM (
SELECT *
FROM Productlist pl
ORDER BY somefield
)
WHERE ROWNUM BETWEEN 9991 AND 10000Note that you can never trust the DB to give you the records in the same order every time, so you need the order by clause. The field you're ordering by should be indexed if you want to get any speed to your query
Thanks. Problem is the table is too big it contains 10 lak to 90 lak data. while access perticular data (ex: 1500000 to 1501000) then it is taking so much time. The Inner Query execute all records (90 lak) and the outer query filter 9991 to 10000. This is my problem. Regards, Nanda
-
Hey Thanks, Thanks for your valuable reply. I am using Oracle. The rownum is dynamical assign to each row. so SQL server method is not working. and I tried view it is working but when order by used the result is coming wrong. Thanks and regards, Nanda
Nanda_MR wrote:
I am using Oracle
That would have been useful info, also what is a
lak, presumably lots. So you know how to get the paged set, you real question is how to optimise the query (using rownum) or if there is a better method to use in orable. And I'm afraid there I cannot help you.Never underestimate the power of human stupidity RAH
-
Thanks. Problem is the table is too big it contains 10 lak to 90 lak data. while access perticular data (ex: 1500000 to 1501000) then it is taking so much time. The Inner Query execute all records (90 lak) and the outer query filter 9991 to 10000. This is my problem. Regards, Nanda
That's why you need a index on the column you order by, it's already sorted. The optimizer will take care of the inner query. Do you have an index on the column you're sorting by?
-
Hi Guys! I am trying to retrieve middle records. The below query is working perfectly. The problem is my table is too huge (more then 10 lak reocrd) it is taking more time. Is there any other solution? [This is sample query]
select *
from (select t.*, rownum rn
from (SELECT *
FROM (select * from Productlist)) t
Where rownum <= 10000)
Where rn > 9990Regards, Nanda
Hi, If you do have an ascending surrogate key, one efficient way would be to use that. For example if you have a field called Id and the values are coming from ordered sequence your query could be something like:
select *
from ProductList a,
(select b.Id,
count(*) over (order by b.Id) as runcount
from productlist b
where b.Id > :lastFetchedId) subq
where a.Id = subq.Id
and subq.runcount <= 10The need to optimize rises from a bad design.My articles[^]
-
Nanda_MR wrote:
I am using Oracle
That would have been useful info, also what is a
lak, presumably lots. So you know how to get the paged set, you real question is how to optimise the query (using rownum) or if there is a better method to use in orable. And I'm afraid there I cannot help you.Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
what is a lak, presumably lots
Indians have a different style of counting big numbers. A lak is one hundred thousand (Indian style: 1,00,000), a crore is ten million (Indian style: 1,00,00,000). But that's still easy. Stay a few days in India to get confused with more puzzling Indian style information....
-
Hi, If you do have an ascending surrogate key, one efficient way would be to use that. For example if you have a field called Id and the values are coming from ordered sequence your query could be something like:
select *
from ProductList a,
(select b.Id,
count(*) over (order by b.Id) as runcount
from productlist b
where b.Id > :lastFetchedId) subq
where a.Id = subq.Id
and subq.runcount <= 10The need to optimize rises from a bad design.My articles[^]
Hi Mika, I done similarly in View. Add a new column unique ID and I am adding rownum. In this case I am executing query without any delay. But one problem is there. In case if order by condition added the output of query is coming wrong(Bug) or a block of rows is order. Regards, Nanda
-
Nanda_MR wrote:
I am using Oracle
That would have been useful info, also what is a
lak, presumably lots. So you know how to get the paged set, you real question is how to optimise the query (using rownum) or if there is a better method to use in orable. And I'm afraid there I cannot help you.Never underestimate the power of human stupidity RAH
-
Nanda_MR wrote:
I am using Oracle
That would have been useful info, also what is a
lak, presumably lots. So you know how to get the paged set, you real question is how to optimise the query (using rownum) or if there is a better method to use in orable. And I'm afraid there I cannot help you.Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
also what is a lak
Google would happily have told you ... :-D
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hi Mika, I done similarly in View. Add a new column unique ID and I am adding rownum. In this case I am executing query without any delay. But one problem is there. In case if order by condition added the output of query is coming wrong(Bug) or a block of rows is order. Regards, Nanda
Hi,
Nanda_MR wrote:
I am adding rownum
Just to check: Did you notice that I didn't use
rownumat all because it's unreliable in this context (for many different reasons). The example I created shouldn't encounter any of the problems rownum has since the example is based on a running total count on an unique and ascending field.The need to optimize rises from a bad design.My articles[^]
-
Mycroft Holmes wrote:
also what is a lak
Google would happily have told you ... :-D
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
I naturally did the google thing, the response was nowhere as succinct as the answer I got here :laugh:
Never underestimate the power of human stupidity RAH
-
I naturally did the google thing, the response was nowhere as succinct as the answer I got here :laugh:
Never underestimate the power of human stupidity RAH
Unfortunately conciseness isn't Google's primary strength. :((
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.