Re: How to tune this stored proc?

<michael_bialik_at_my-deja.com> wrote in message news:7u2thf$c5o$1_at_nnrp1.deja.com...
> Hi.
>
> Create an index on that field ASAP!
>
> You are deleting 4000 record each time and it means
> that you are making a FULL scan of 4.5M records
> 145 ( 580 / 4 ) times.
> Assuming homogeneous dispersion of your data you
> are reading 145 * 4.5M / 2 rows!!!
>
> Increase the number of records from 4000 to 10000 at least.
> I think you set it to 4000 because of long execution time, but
> after index creation it will decrease.
> Even better - pass it as parameter to your procedure.
>
> Another possibility is to create an index before the stored proc is
> executed and drop it afterward.

Many people, even experienced veterans believe that indexes are panaceas, the solution of all performance issues. Of cause, it's not.

OK, assuming the column values are homogeneous dispersion, use the following script to test deleting 1/8 of rows. I only tested for 200,000 rows. In my Oracle8i database, it took 20.15 sec without index, but 35.10 sec using a index. (I didn't test for 4,500,000 rows.)

The following is the script.

• begin script ---- set serveroutput on; set feedback off; create table test (p_date date, text varchar2(20)); prompt the primitive way truncate table test; declare t date := trunc(sysdate); begin for i in 1..200000 loop insert into test values (t+dbms_utility.get_hash_value(i, 0, 8), 'Testing'||i); if mod(i, 2000)=0 then commit; end if; end loop; commit; end; / declare t1 number; t2 number; t date := trunc(sysdate); begin t1 := dbms_utility.get_time(); loop delete from test where p_date=t and rownum<=4000; if sql%notfound then exit; else commit; end if; end loop; commit; t2 := dbms_utility.get_time(); dbms_output.put_line((t2-t1)/100); end; /

prompt using index
truncate table test;
create index ind_test on test(p_date);
declare
  t date := trunc(sysdate);
begin
  for i in 1..200000 loop
    insert into test values (t+dbms_utility.get_hash_value(i, 0, 8), 'Testing'||i);

    if mod(i, 2000)=0 then
      commit;
    end if;
  end loop;
  commit;
end;
/
declare
  t1 number;
  t2 number;
  t date := trunc(sysdate);
begin
  t1 := dbms_utility.get_time();
  loop
    delete /*+ index(test ind_test) */ from test       where p_date=t and rownum<=4000;
    if sql%notfound then
      exit;
    else
      commit;
    end if;
  end loop;
  commit;
  t2 := dbms_utility.get_time();
  dbms_output.put_line((t2-t1)/100);
end;
/
drop index ind_test; Received on Fri Oct 15 1999 - 12:50:58 CDT