I have a requirement to generate sequence of numbers for a given range (like below) and i'm using recursive query and it works for small volume. As the volume keeps increasing query is spooling out. Please help me with a solution.
If I have the range as given below for 1 record and I will have ~ 1 million records each day
I need to generate as below
SEQID STARTID ENDID
1192014848 1192014848 1192014975
1192014849 1192014848 1192014975
1192014850 1192014848 1192014975
1192014851 1192014848 1192014975
1192014973 1192014848 1192014975
1192014974 1192014848 1192014975
1192014975 1192014848 1192014975
Currently I'm using recursive query and works for small volume of records.
RECURSIVE nums(n,startipnum,endipnum) AS (
SELECT startipnum AS n,startipnum,endipnum FROM DB.TBL
SELECT n +1,startipnum,endipnum FROM nums
n,startipnum,endipnum FROM nums;
seems like you want to expand blocks of ip-addresses.
If your query runs out of spool it's probably not because of the recursion but due to the number of rows returned.
Also recursion will be quite slow as the biggest networks in IPv4 got 16.000.000 addresses.
Of course you could abuse EXPAND ON (this will work up to a 60 million range):
CAST(ip_block_start + (EXTRACT(SECOND FROM BEGIN(pd)) * 1000000) AS DECIMAL(10,0)),
SELECT ip_block_start, ip_block_end, pd
WHERE ip_block_start IN ( 3177586048, 1192014848)
EXPAND ON PERIOD (TIMESTAMP '2000-01-01 00:00:00.000000', TIMESTAMP '2000-01-01 00:00:00.000000' + (ip_block_end - ip_block_start + 1) * INTERVAL '0000.000001' SECOND) AS pd
) AS dt
But why do you need to do this, if it's IPv4 you'll create up to 4 billion numbers?
I'm not sure about the usage. The project team has asked me to provide a solution on teradata which is equivalent to "generate_sequence" function in Green plum.
I am facing the same issue, I only throught of generating this IP number sequence because my query is taking too long.
Data that I have is username, IP address (plus IP number converted) , login date and time
What I want is to map IP to country, and show number of logins on a map. I already have a mapping table but joining with it [ on IP_Number between Range_Start and Range_End ] takes forever.
To me, generating a sequence for every IP (thought takes extra space) will improve the performance.
I've thought about it but have not been able to imagine how a sequence number helps. Instead, I would create a table that correlates the first 16 bits of an IP address with a country code, or the first 32 bits with a location code (corresponding to some range of coordinates), depending on how detailed the map is: (IP_Prefix, Loc_Code). If the mapping table doesn't have a location code then add one. Then you can join the three tables.