-- This script performs a little benchmark on your database
set feedback off
prompt creating temporary tables
create table benchmark_1 (
  id		integer 	NOT NULL,
  text	 	varchar2(200) 	NOT NULL,
  date_field	date		NULL,
  qty		number		NOT NULL
)
storage (initial 1M next 1M pctincrease 0)
/

ALTER TABLE benchmark_1 ADD (
      CONSTRAINT BM1_PK
      PRIMARY KEY (ID)
USING INDEX 
)
/

create table benchmark_2 (
  id		integer 	NOT NULL,
  bm1_id	integer		NOT NULL,
  text	 	varchar2(200) 	NOT NULL,
  date_field	date		NULL
)
storage (initial 1M next 1M pctincrease 0)
/

ALTER TABLE benchmark_2 ADD (
      CONSTRAINT BM2_PK
      PRIMARY KEY (ID,bm1_id)
USING INDEX 
)
/

ALTER TABLE benchmark_2 ADD (
      CONSTRAINT bm2_bm1_FK
      FOREIGN KEY (bm1_ID)
      REFERENCES  benchmark_1 (
                   ID)
)
/

create index bench2 on benchmark_2 (bm1_id)
/

prompt Inserting records
set timing on
declare
  nrecords number:=100000;
begin
  for x in 0..nrecords/10+1 loop
    insert into benchmark_1 values (x,rpad('Record with primary key '||to_char(x),200),sysdate,x/2);
    if mod(x,1000)=0 then
      commit;
    end if;
  end loop;
  for x in 1..nrecords loop
    insert into benchmark_2 values (x,x/10,rpad('Record with primary key '||to_char(x),200),sysdate);
    if mod(x,1000)=0 then
      commit;
    end if;
  end loop;
  commit;
end;
/

prompt Selecting records
declare
  res number;
  cnt number;
begin
  select count(*) into cnt from benchmark_2;
  select avg(bm1.qty)
  into res
  from benchmark_1 bm1,benchmark_2 bm2
  where bm1.id=bm2.bm1_id
  and (bm2.id<cnt/3 or bm2.id>cnt*2/3);
  select max(bm1.id)
  into res
  from benchmark_1 bm1
  where not exists (select * from benchmark_2 bm2
    where bm1_id=bm1.id);
end;
/

prompt Updating records
declare
  cnt number;
  x number:=0;
begin
  select count(*) into cnt from benchmark_2;
  while x<cnt loop
    update benchmark_2 set date_field=sysdate where id>=x and id<x+1000;
    commit;
    x:=x+1000;
  end loop;
  commit;
end;
/

prompt Deleting records
begin
  loop
    delete from benchmark_2 where rownum<1000;
    if sql%rowcount=0 then
      exit;
    end if;
    commit;
  end loop;
  commit;
  loop
    delete from benchmark_1 where rownum<1000;
    if sql%rowcount=0 then
      exit;
    end if;
    commit;
  end loop;
  commit;
end;
/

set timing off

prompt dropping temporary tables
drop table benchmark_2;
drop table benchmark_1;
set feedback on
