fastest sampling of rows

[Distraction]

How can I emulate the 'rownum < n' functionality from Oracle?

I have several million records in a table. I just want about 5-10 rows.

'Sample' still does a full table scan.

Mod-ing the numeric key still does a full table scan.

Any thoughts?

 

[CapsuleCorpJX]

Well if you don't care if the 10 rows you get are random, then you can use "RANK". Basically sort a result and take the top or last x rows.

For example : top 10 sales
select CUST_ID, CUST_SALE
from SALES
qualify rank() over (order by CUST_SALE desc) <= 10

 

[Distraction]

Doesn't qualify rank() over still do a full-table scan? I'd have to sort my result. The order by means I have to sort several hundred million records. I don't think that's going to speed things up.

 

[RogerCruse]

select *
from ...
sample 10

 

[teradev]

If you are using v2R6 and above then you can use the 'top' function.
ex: select top 10 from tableA;

 

[Distraction]

Thanks, teradev! I think the other options all still do a full table scan.

Now a real bonehead question...how do I check my version?

 

[Distraction]

OK...figured out the version. We're still using v2r5.xxx

 

[RogerCruse]

select * from dbc.dbcinfo

 

[dnoeth]

SAMPLE is *not* doing a FTS, even if the explain tells about an "all-rows scan". Simply try it...

But SAMPLE is getting slower if the number of AMPs in a system increases, that's why the TOP syntax was implemented in V2R6 (if you just want to see some data, not a ststistical sample)

Dieter

 

[Distraction]

We have like 75 nodes and many hundred amps. And still don't have the version with the 'Top' command.