current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 1 down vote favorite

I've created a sample table in Oracle 11.2.0.1.0:

create table my_table ( id number , val varchar2(5));    
insert into my_table 
select level , dbms_random.string('a', 1) from dual 
connect by level <= 10000;
commit;

select * from my_table where LOWER(VAL) = 'h'; --returns 350 rows

Now, I want to update several tables which have the id of having lower(val) = 'h'

So, I've used:

begin
for rec in(select id from my_table where lower(val) = 'h')
loop
update my_table1 set val1 = 'XX' where id1  = rec.id;
update my_table2 set val2 = 'XX' where id2  = rec.id;
update my_table3 set val3 = 'XX' where id3 = rec.id;
update my_table4 set val4 = 'XX' where id4  = rec.id;
... --some more tables to update with the same rec.id
end loop;
end;

My colleague asked me to do the same with using TYPE, and I've tried with:

CREATE OR REPLACE TYPE my_number_type IS TABLE OF NUMBER;

DECLARE
my_number_obj my_number_type;
BEGIN
SELECT ID 
BULK COLLECT INTO my_number_obj 
FROM my_table
WHERE lower(val) = 'h';
UPDATE my_table1 
SET val1 = 'XX' 
WHERE ID1 IN (SELECT COLUMN_VALUE FROM TABLE(CAST(my_number_obj AS my_number_type)));
UPDATE my_table2 
SET val2 = 'XX' 
WHERE ID2 IN (SELECT COLUMN_VALUE FROM TABLE(CAST(my_number_obj AS my_number_type)));
... --some more tables to update with the id in my_number_obj

END;

Which of the above is better to use (in any aspect)? Or is there any other better way to update for my requirement?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

The second example COULD be better, but this is one of those cases where you need to test. In general, FORCING a SQL environment to "loop" is a Bad Thing. The query engine can "loop" much faster than your "forced" loop - but in some queries it doesn't. So, you have to test. The looping example is much more readable though, because the second example uses non-ANSI, Oracle-only structures. If raw speed is not important, then the first example is probably better due to readability - but if you're working in an environment with lots of Oracle experts, that may not be true either.

This is one of those cases where "better" is not absolute - it depends a lot on your environment and your goals for the project. Academically or theoretically, the second example is "better" but only because of the "no forced loops" rule.

share|improve this answer
    
Thanks for the reply. Readability is not important, I need a faster process.(eg: here for some updating 1000 id's to severeal tables). So I moved to second. Any other solutions are welcomes, thanks. –  ajmalmhd04 Nov 6 '13 at 3:54
    
Make sure to test then - the first one might perform better, you can't know without testing. Generally the second option will be better, but it depends on a lot of things. –  Jasmine Nov 7 '13 at 16:29
up vote 0 down vote

I'd use forall statement, a feature of bulk SQL. forall guarantees a single PL/SQL - SQL context switch so this should be the same than your second example. However I think forall is most readable - YMMV. See e.g. Bulk Processing with BULK COLLECT and FORALL for more details.

declare
  type id_list_t is table of number;
  v_ids id_list_t;
begin
  select id bulk collect into v_ids
    from my_table where lower(val) = 'h';

  -- one forall statement for each updatable table
  forall indx in 1 .. v_ids.count
    update my_table set val = 'XX' where id = v_ids(indx);
end;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.