bulk loading

harish_s_ampeo · Post by **harish_s_ampeo** » Tue Jan 29, 2008 7:03 am

Which bulk loading is better in performance
1. Oracle 7 Load
2. Oracle OCI Load

I have to every time delete the table and insert the records. In #2 we don't have the option to delete the table in oracle.

tkbharani · Post by **tkbharani** » Tue Jan 29, 2008 7:32 am

Your question is not clear is it DELETE or TRUNCATE. if delete then are you using any key for deletion. If deletion then I suppose you can not do it through BULK loading.
If TRUNCATE/LOAD then use ORACLE 7 BULK LOAD to create Control file and load using sql loader(sqlldr). In CTL you can specify TRUNCATE or REPLACE OR APPEND OR INSERT only. This is faster for TRUNCATE/LOAD

Please correct me if I am wrong.

harish_s_ampeo · Post by **harish_s_ampeo** » Tue Jan 29, 2008 7:43 am

The strategy is DELETE. I have to daily delete the previous day's records and insert the records(bulk loading) into the table

chulett · Post by **chulett** » Tue Jan 29, 2008 7:55 am

And in your case, the "previous day's records" = everything in the table?

chulett · Post by **chulett** » Tue Jan 29, 2008 7:56 am

And in your case, the "previous day's records" = everything in the table?

tkbharani · Post by **tkbharani** » Tue Jan 29, 2008 8:14 am

Ok.On your way..
You want to delete all the records in the table , or only the records that have been loaded yesterday.
If only the records loaded yesterday
------------------------------------
1. Use DATE parameter as key and use ORACLE OCI stage only (not bulk)
2. Write user defined sql.
3. Delete * from table where
Record_Date >= Date1 and Record_Date < Date2
4. Provided your table contains Record_Date(date on which the data is loaded).

I have given both the solution(Delete full table or only selected data). Since I don't know the data format,i am writing user defined sql. In case of full table delete , use TRUNCATE.Delete will be slow and uses more system resources on data base side.