Coming to the point directly, I have to upload a huge data file (record number up to 100 thousand) to the Oracle8i database. The file structure corresponds to a table in the database, so all the data from the file goes to a table in the database. But one more table needs to be populated from this file. Data from only one column (Password column) is taken and after applying an algorithm to encode the data it is stored in the second table (used only for login). The problem is that it is taking too much time, and we have to improve the performace. Please suggest me what way/method I can use to upload the data in the main/first table (in the second table we have to insert records one by one because they have to be encoded).
It sounds like the issue here is simply figuring out how to improve the performance of the inserts into the table that require encoding. So....what you want to do is to cut down on how many times you have to pass an insert statement to the server for execution. From the way it sounds like you have it coded now, you are doing a single insert for each encoded password row. You could store the rows of data into a PL/SQL collection (INDEX BY table, VARRAY or NESTED TABLE) and then use the FORALL statement to "bulk bind" a whole bunch of inserts together and send the "batch" to the server. This can be a huge performance benefit as it will send a whole set of rows to be inserted to the server instead of sending each insert one at a time.
Here's a very simple example that perhaps you can use to base your own code on:
DECLARE TYPE PassTab IS TABLE OF number(5) INDEX BY BINARY_INTEGER; TYPE NameTab IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER; p_passwords PassTab; p_names NameTab; BEGIN FOR i in 1..1000 LOOP -- load index-by tables p_passwords(i) := i ; p_names(i) := 'Name ' || i ; END LOOP ; FORALL i in 1..1000 -- use FORALL statement to INSERT all 1000 rows at once INSERT INTO pswd_table (pswd_key, pswd_encoded) VALUES (p_names(i), p_passwords(i)); END; /For your application, you'd need to create the index by table to hold the correct datatype that your encoded password will be (e.g. VARCHAR2(30)). Then, where I simply used a loop to store the numbers 1-1000 into the index by table, you would actually create your loop to take the original password value and do whatever you need to handle the encoding of it and then store that encoded value into the index by table. Obviously, if the table where you are storing the encoded password has a key field, you'll need to create an index by table to store that value also so that when you do the insert you'll have all the values you need for the values clause (similar to how I used p_names).
Anyway, using this method, you can DRASTICALLY cut back on the time required to process the inserts. I've seen timings in my own tests that take 50000 rows and insert them 1 at a time and it takes let's say 2 minutes. Using the FORALL to do the inserts in bulk, it reduces the time to around 15 seconds! Quite a performance boost. Try it out for yourself and see if it doesn't help immensely.
For more info on bulk binding and the use of the FORALL statement, see the Oracle PL/SQL Users Guide and Reference document.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.