| Why is this sql so slow? [message #19640] |
Tue, 02 April 2002 05:10  |
Emil
Messages: 4
Registered: April 2002
|
Junior Member |
|
|
I am trying to select from a table where rownum < 20 and rownum > 10 with order by. I know this isn't possible since rownum sets when a post is inserted into the table.
I found you could use two inner selects to make it possible, like this:
SELECT * FROM
(
SELECT INNER_SELECT.*, ROWNUM AS RNUM FROM
(
select * from table1, table2 where col1 = col2
) INNER_SELECT )
WHERE RNUM >= 10 AND RNUM <=20
This works fine, but takes extremely long time!
I understand that it should take a little longer, since you make three selects instead of one, but the differences are incredibly big.
When I run the innermost select only, select * from table1, table2 where col1 = col2, it takes about 50 millisec. But the whole sql string takes nearly 20 seconds!
It only takes this much time when the innermost select is joined between two different tables, if I alter it to select * from table1 where col = value it doesn't take much time.
Does anybody know why this happens, and more important, is there any other ways to do this?
|
|
|
|
| Re: Why is this sql so slow? [message #19709 is a reply to message #19640] |
Thu, 04 April 2002 08:43  |
Rasmeek
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
select * from table1, table2
where col1 = col2 and
ROWNUM >= 10 AND
ROWNUM <=20
If this does not work, then just try putting the RNUM condition inside and the join outside. As there will be fewer records to join, the query will run faster.
|
|
|
|
| Re: Why is this sql so slow? [message #19711 is a reply to message #19640] |
Thu, 04 April 2002 10:02 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Note that conditions testing for ROWNUM values greater than a positive integer are always false.
For example, this query returns no rows:
SELECT * FROM emp WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row
to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition
false. All rows subsequently fail to satisfy the condition, so no rows are returned.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select * from dept where rownum > 3;
no rows selected
SQL> select * from dept where rownum > 2;
no rows selected
SQL> select * from dept where rownum < 3;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
|
|
|
|
| Re: Why is this sql so slow? [message #19725 is a reply to message #19640] |
Thu, 04 April 2002 20:44 |
Emil
Messages: 4
Registered: April 2002
|
Junior Member |
|
|
I do know that you can't use
"SELECT * FROM emp WHERE ROWNUM > 1", that is why I use an inner select. That creates a new rownum for the select.
If you try my sql string you should see it's working, the problem is that it's so slow.
/Emil
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
