oracle pl/sql loading large data into temp table

Question

I have a use case where a large amount of data sometimes a million rows added to a temporary table (session global temp table) and the table to be joined to another table to produce results/graph/heatmap. The data is text files or could come from a named pipe/stream/socket. SQLLDR will not be an option as it is a temporary table.

Do you have suggestions as to how this can be done efficiently. Currently SQL statement looks like below:

insert into tempipdata (IP)
select gutils.ip2long('100.0.59.165') FROM DUAL UNION
select gutils.ip2long('100.1.117.161') FROM DUAL UNION
select gutils.ip2long('100.23.117.161') FROM DUAL;

Any suggestions. I could get the IP Address data from a socket or stdin of output of a program.

Thanks Vijay

Answer 1

For accessing data from an OS file your best solution would be external tables. These are just like normal tables, only the data comes from CSV (or whatever) files. So we can use SQL against them. It's a better solution than temporary tables because you won't have to load the data first: you can select it straight away. Find out more.