Solved

Question about Inserting Records into a table in Oracle

Posted on 2008-10-20
18
560 Views
Last Modified: 2013-12-18
I have csv file, which I am loading through SQL Loader for a table.

my_table data is as follows:
emp_no      line          some other columns
100              1
100              5
100              3
101              1
101              4
101              3

emp_no & line both constitute the primary key.

as you can see, some of the lines for the emp_no are missing. So,  I am asked to sequence the line numbers by ordering them according to emp_no & line.

So, after inserting through SQL Loader I am calling a function which updates the rows & returns the number of rows updated. Please find the Function attached.

With this function(which I got from e-e), I will get the result as follows:

emp_no      line          some other columns
100              1
100              2
100              3
101              1
101              2
101              3

But the problem here is

The update statement is updating all the rows inside the table. If there are 10 million rows, it is updating all the rows. It is taking a lot of time.

I only want to update the newly inserted rows. So, if i insert two rows, it should update only those two rows. Is there any way to do that?
create or replace FUNCTION Proc(schema_name in VARCHAR)
 

RETURN NUMBER
 

IS
 

SQL_TXT VARCHAR(700);
 

BEGIN
 

  SQL_TXT:= 'UPDATE '||schema_name||'.my_table x set ( line ) = (  select y.val from (select emp_no, line, row_number() over (partition by emp_no order by line ) as val from '||schema_name||'.my_table ) y where x.emp_no = y.emp_no and x.line = y.line)  
 

  EXECUTE IMMEDIATE SQL_TXT;

  

  RETURN SQL%ROWCOUNT;
 

  EXCEPTION
 

  WHEN OTHERS THEN
 

      RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

  

  RETURN -1;
 

END;

Open in new window

0
Comment
Question by:srikanthradix
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22764025
if you've already have 10 M records in the table, and want to insert some new records, i'll probably create a trigger to get the current maximum line_no of the current emp_no, and plus 1 as current line no.

or if you want to insert 10 million records, you can insert the whole 10 M records into a table without a line_no, and use create table table_B as select * from table_a statement to compute line_no on the fly. after this drop table_A and keep table_B
0
 

Author Comment

by:srikanthradix
ID: 22764097
There is also one more problem,

Suppose there are 1 million records in the table. And if I insert some records into table_B using the SQL Loader without the line no.

If some records violate the primary key, How would SQL Loader while inserting into table_B know that some records are violating the primary key?

Creating a trigger:

First I have to order the lines and then i have to assign the line numbers.

like if there are records like:

101              1
101              4
101              3

then first it needs to be order according to the line

101              1
101              3
101              4

then I have to assign the row_number to it.

101              1
101              2
101              3

How is this possible with the trigger?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22764325
I have not used SQL Loader in years and do not have documentation available, but I am highly confident about the concept I am about to propose.  The idea is to insert the rows with the correct line number to begin with.   I do not know the origin of the line number with gaps, but certainly you can derive a sequential next line number within emp_no during the SQL Loader insert.

For the input specification, use  something like this:

line EXPRESSION "Select max(line) + 1 from YourTable where emp_no = :emp_no_field"


0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 22764349
it's not possible in a trigger.  You'll have to use external code to do it.

I recommend inserting new rows into a temporary table.  Then query the original table to find the maximum line for each emp_no and then insert from the temp table with the max line something like this...

select max(line) into v_max_line from old_table
where emp_no = v_some_emp_no_you_want_to_update;


insert into old_table (emp_no, line, other_columns)
(select emp_no, v_line + row_number() over(partition by emp_no order by line), other_columns
 from temporary_table
where emp_no = v_some_emp_no_you_want_to_update
)

you could loop by emp_no and report status for completing each one if this is a long running process.

0
 

Author Comment

by:srikanthradix
ID: 22764381
sdstuber:

I like your idea, I so badly want to do this way. But, what if some of the records inserted in the temporary table are duplicates? insert statement would end abruptly in the middle causing other records not to be inserted right?
0
 

Author Comment

by:srikanthradix
ID: 22764426
@dgmg:

It's not sequential for line numbers. I have to order them first according to that emp_no & then insert them into the table. I got that idea too first but they told me that is not the case. That's why i had to suffer a lot.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 22764477
if the temp table has duplicates, what do you want to happen?

only insert one?
   --  if so, delete the duplicates before you do the insert

 insert both but increment the line to prevent duplicate in the final table?
  --  if so, the code above will do that for you
0
 

Author Comment

by:srikanthradix
ID: 22764581
@sdstuber

I want to insert only one.

How can i do that inside this loop? How can I discard the duplicates? Please suggest.

declare
emp_no temp.emp_no%type;
cursor temp_cur is select distinct(emp_no) from temp;
begin
open temp_cur;
  loop
    fetch temp_cur into emp_no;
    exit when temp_cur%notfound;

    select max(line) into v_line from my_table
    where emp_no = emp_no;


    insert into my_table (emp_no, line, other_columns)
    (select emp_no, v_line + row_number() over(partition by emp_no order by line), other_columns
     from temp where emp_no = emp_no)

  end loop;
close temp_cur;
end;
/

0
 

Author Comment

by:srikanthradix
ID: 22764672
@sdstuber

I am sorry, I was hasty.

delete from temp t1 where (t1.no,t1.name) in (select no, name from my_table);


Should i delete as above before inserting like above?

I get it now. How can i display the status then if it is taking long time?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 73

Expert Comment

by:sdstuber
ID: 22766241
sure that should work


as for display  you have several options.

you can use dbms_output, but that will only be visible when the process completes.

You can write to a log file or write to a log table (use autonomous transactions for the log table)

you can use dbms_application_info to fill in the action/module/client_info portions of v$session.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22766245
utl_tcp or dbms_pipe could be used to send messages to a separate process also
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 100 total points
ID: 22768644
My answer gives sequential lines within an emp_no, not across the entire file.  Use an SQL Loader expression to invoke the SQL that derives the next line number for the emp_no being loaded.  

The  temp table approach is ideal for duplicates.  You eliminate the dups using group by on the temp table:

insert into old_table (emp_no, line, other_columns)
(select emp_no,  row_number() over(partition by emp_no order by line), other_columns
group by emp_no, line
 from temporary_table
)
0
 

Author Comment

by:srikanthradix
ID: 22770398

Sorry i didn't see that. I thought it is for the entire file.
line EXPRESSION "Select max(line) + 1 from YourTable where emp_no = :emp_no_field "

However, Since sequencing is not an option for me. I have to order first which I think it is not possible with SQL Loader.

First I insert into the temp table with SQLLoader. Then, Can I do like this?

Instead of looping over each emp_no can I use like this inside a function which takes schema_name as parameter?

INSERT_QUERY:='insert into '||schema_name||'.my_table (select t.emp_no,(select max(v.line) from '||schema_name||'.my_table v where v.emp_no = t.emp_no) + row_number() over (partition by t.emp_no order by t.line),other_columns from '||schema_name||'.TEMP t';

EXECUTE IMMEDIATE INSERT_QUERY;
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 22770806
you "can", and it is more efficient to do it all at once.
Breaking it apart and looping by emp_no gave convenient commit and logging points.
but those do add to the time.

Why would you want to use execute immediate and dynamic sql for this?
Is the schema really going to be changing?

If not, then just put the statement directly into your pl/sql block.

INSERT INTO mytable(emp_no, line, other_columns)
    (SELECT   emp_no, (SELECT   MAX(line)
                         FROM   mytable
                        WHERE   mytable.emp_no = mytemp.emp_no)
                      + ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY line), other_columns
       FROM   mytemp)
0
 

Author Comment

by:srikanthradix
ID: 22772077
@sdstuber

Thank you so much. One last question,

Do i have to use like:

 insert into mytable(all the columns) select columns from mytemp;

or can i use like (Can i ignore saying the columns of mytable? because mytemp is the replica of the mytable and will have same number of columns as the mytable)

insert into mytable select columns from mytemp;

Because, there are around 100 columns in the mytable besides emp_no & line.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 22772150
you don't have to specify them in the insert into mytable(a,b,c)  part
but you do have to in the subquery part because one column (line) is derived, otherwise you could use *.

However, that is considered bad practice.

You shouldn't have to type them all in though,  any of the popular pl/sql editors should let you extract all the columns and paste them into your sql statement.
If you don't have one,  you can query them from dba_tab_cols yourself.

If you're concerned with having a big sql statement, don't be.

The syntax requires it, and good practices encourage it, so it's not a problem at all.


0
 

Author Closing Comment

by:srikanthradix
ID: 31508123
Thank you so much for the answer and your valuable time
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22772256
glad I could help
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now