Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


fastest sampling of rows

fastest sampling of rows

fastest sampling of rows

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?

RE: fastest sampling of rows

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
from SALES
qualify rank() over (order by CUST_SALE desc) <= 10

RE: fastest sampling of rows

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.

RE: fastest sampling of rows

select *
from ...
sample 10

RE: fastest sampling of rows

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

RE: fastest sampling of rows

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?  :)

RE: fastest sampling of rows

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

RE: fastest sampling of rows

select * from dbc.dbcinfo

RE: fastest sampling of rows

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)


RE: fastest sampling of rows

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close