How to load into oracle when timestamp > 12345678900

Question

I have a rather huge file (50mb) where the first field is a unix timestamp. However, I don't want to load the whole file, I need only half of it where timestamp > some_parameterized_timestamp.

It would seem sqlldr only allows = and <> conditions in the when clause. It takes too long to load the file and then delete records that are not needed.

Is there perhaps an alternative to using sqlldr that still has comparable performance? (I'm already using direct=true.)

I am thinking of using awk to create a copy of the file with the data I need, and awk is fairly efficient at it, but before I go down that road, I thought I'd ask here for better suggestions.

Answer 1

This note on asktom (search for "magic")seems to indicate that using INFILE '-' in the control file would allow a redirection, like:

awk [various switches] sourcefile | sqlldr [sqlldr options]

But I've not tested it.

Assuming you have access to a file system on the database server, you could use an external table specification of your data file, then use SQL to "load" the data from the external table. Similarly, you could load your data into a staging table; then run a SQL script to

INSERT INTO TARGET_TABLE 
SELECT * FROM STAGING_TABLE 
 WHERE TIMESTAMP > :PARAMETER;
TRUNCATE TABLE STAGING_TABLE;