CODEWITHSUNDEEP
performanceoracle-databasetemp-tables
Vijay
Vijay

Reputation: 111

oracle pl/sql loading large data into temp table

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

Upvotes: 1

Views: 1903

Answers (1)

APC
APC

Reputation: 146239

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.

Upvotes: 4

Related Questions