Sub-query, select frist, sorted 20 records from 5000 records. 1

[markco]

Anyone have a good method if I could like to do the following:

Table:
Craete table DATA
(INPUTTIME DATE,
ARTICLECODE VARCHAR2(10)
);

All data is not sort by DATA.INPUTTIME inside table.
There are total more than 50000 record, but if I could like to get the first 20 record, but require to sort by DATA.INPUTTIME first.

ie. select first 20 record from (select articlecode from data order by inputtime; ) order by inputtime;

If I have this sql, but the result speed is very slow.

select * from (
select rownum as rn articlecode
from data
order by inputtime) temp where temp.rn between 1 and 20;

but this SQL will return 20 records after processed 5000 record, due to sub-query will process 5000 records.

Anyone have a good method??? Markco Wong

 

[Wonboodoo]

I'm not aware of an alternative if you keep it as basic SQL. You'll probably have to process the 5000. Chances are you are not going to be using this as straight SQL but it will be used inside some other programming language (PL/SQL, JDBC, PRO*C, ... etc). If that is the case then you can use a cursor to get the first rows and not have to process the rest (provided you have an index). You can also give a "/* +FIRST_ROWS */" hint if this is the case.

 

[Turkbear]

The sub-query will need to process all the records to sort them by the inputtime column..an index on that column will help.

 

[carp]

Try this:

SELECT *
FROM (select articlecode from data order by inputtime)
WHERE rownum < 21;

Also, what are you considering &quot;very slow&quot;? I created a 5000 row table to test this on a PC and it runs in about
.6 seconds.

As turkbear points out - an index would help a lot for your query.

 

[markco]

Thanks for input.
Of Couse, my table is not simply as this sample table. It include more than 500,000 records and have many columns, include BLOB data. The true SQL is also include some join table query, such that I need to take about 30 sec to receive first 20 rows.

This SQL is good for receive first 20 rows.
SELECT * FROM (
select articlecode from data order by inputtime
)
WHERE rownum < 21;

But if I need to receive the 21st to 40th records, anyone have another idea? Markco Wong

 

[Turkbear]

Hi, first, thanks for not revealing your actual situation the first time..We like challenges---

for the records you want, try the MINUS operator:

SELECT * FROM (
   select articlecode from data order by inputtime
)
WHERE rownum < 41;
MINUS
SELECT * FROM (
   select articlecode from data order by inputtime
)
WHERE rownum < 21;