What is the best way to extract subset of rows from a table in Teradata

Database
Enthusiast

What is the best way to extract subset of rows from a table in Teradata

I have tried using ROWNUM but it requires an order (ROW_NUMBER() OVER (ORDER BY col) )by which is not suitable in our case since the number of records is huge.Is there any other way to acheive it?

Our reuqirement:

Say we have a table with 40 million records and I want of generate a series of queries each working on a subset of rows:

Ex:

  1. Query1: Row 1 to 10 Million.
  2. Query2:Row 1000001 to 20 Millions
  3. Query3:2000001 to 30 millions.
  4. Query4:3000001 to 40 million

What is the best approach to go about it?

1 REPLY
Teradata Employee

Re: What is the best way to extract subset of rows from a table in Teradata

You don't say what defines a subset; it even sounds like it would be variable.

There are two easy ways to select subsets.  One is via the predicate: select ... where something=something, or where something between x and y.  Whatever column(s) you would sort on to generate a row number could be used in a between clause instead.

 

Another way is to get a random sample: select ... sample 10000000, or sample .25 (25%).  See http://info.teradata.com/htmlpubs/DB_TTU_16_00/SQL_Reference/B035-1146-160K/okq1472240804972.html