Updating Cursor records 1

[jhammer98]

Hi,

I have a strange situation here. I'm trying to find the most efficient and least 'costly' way to insert records into a table.

Currently I am doing an INSERT...SELECT...

The new data I want to add will need to be updated before it is inserted ONLY IF the incoming data is null. For example

INSERT INTO <table> A
    (SELECT B.col1
            B.col2
        nvl(C.col3, (SELECT DISTINCT col FROM <table> D
                     WHERE D.col = B.col))
       FROM <table> B, <table> C
      WHERE B.ID  = C.ID  (+)
        AND B.NUM = C.NUM (+));

The problem with this is table C is HUGE and I'm doing 'NVL' conversions on several columns.

My question is...Can I create a cursor to retrieve all the records I want, then update the cursor records and insert them into the final table?

Thanks in advance!

 

[BJCooperIT]

Yes you can.

OR - you can INSERT...SELECT... and then UPDATE only the rows where col3 is null.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My web site: Emu Products Plus

 

[jhammer98]

The problem updating the records in table A is all the 'join information' I need to update the records is in table C. Once I insert data into A I have nothing to join on for the update.

 

[BJCooperIT]

Are you certain the select distinct will return only one row?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My web site: Emu Products Plus

 

[jhammer98]

Yes.

 

[BJCooperIT]

Something like:

declare
    cursor GET_ROWS as
        select B.COL1, B.COL2, C.COL3, B.KEY_COL
        from   TABLEB B,
               TABLEC C
        where  B.ID = C.ID(+)
        and    B.NUM = C.NUM(+);
    V_KEY   TABLED%KEY_COL;
begin
    for GR_REC in GET_ROWS
    loop
        V_KEY := GR_REC.COL3;
        if  GR_REC.COL3 is null then
            begin
                select distinct KEY_COL
                into   V_KEY
                from   TABLED D
                where  D.KEY_COL = GR_REC.KEY_COL) 
                exception
                    when others then null;
            end;
        end if;
        insert into TABLEA
            (col1, col2, col3)
        values
            (GR_REC.COL1, GR_REC.COL2, V_KEY);
    end loop;
end;
/

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My web site: Emu Products Plus

 

[jhammer98]

Perfect!!! I knew I was on the right track, I just coudn't get it into code like that.

Thanks a lot!!!

 

[sem]

I don't understand your trouble. Your C table most probably is accessed by index, isn't it? Thus if all other tables are small, your command should work quite fast. But you must make sure that the index is used first (run explain plan).

Regards, Dima

http://seminihin.narod.ru

 

[jhammer98]

Both solutions work, the NVL conversions as well as the cursor method. Now my question is which is better? At first I thought the cursors would be a better implementation because of Oracle's bulk binding capabilities. Then I got to thinking about how declaring an explicit cursor on top of an implicit cursor might inhibit performance.

Any opinions???

 

[ChrisHunt]

Just to throw a third possibility into the ring, would this work?:

INSERT INTO <table> A
    (SELECT B.col1
            B.col2
        nvl(C.col3, D.col)
       FROM <table> B, <table> C, <table> D
      WHERE B.col = D.col
        AND B.ID  = C.ID  (+)
        AND B.NUM = C.NUM (+));

NVLs aren't all that expensive, but subqueries usually are.

As to which is better, run all the options with some real data and see which comes out fastest. We can't tell for sure without knowing more. PL/SQL approaches are often slower, but can also be easier to maintain (especially if the equivalent SQL statement has mountains of DECODE statements to unpick). Bear that in mind before trying to shave off every possible millisecond.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jhammer98]

The third option will not work because by doing it that way I would have to outer join table B to 3 different tables. The first way that I posted (with the NVL conversions) takes about 1.25 minutes to insert 110,000 records. The cursor method from BJCooper takes a little over 3 minutes for the same amount of records. For some reason I originally thought cursors would be faster but for this situation I was wrong.

Thanks everyone for you insight!!