Test script for https://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/58f7ef75b06b941a

test.sh

#!/bin/bash -fe

# arguments:
# 1: connection specifier user/db
# 2: number of rows (optional, defaults to 1,000,000)
# 3: anything, meany use slow procedure if present

conn="$1"
shift || { echo 'ERROR: need a connection like "foo/bar".' >&2; exit 1; }

file="${TMP:-/tmp}/` basename "$0" `.$$"
trap "rm -f '$file'" 0

# total number of reports
row_max=${1:-1000000}
shift || :
echo "Working with $row_max rows."
fraction=$((row_max / 5))

# if there is another argument we use the slow proc
test $# -ge 1 && blow_up='REPORT_CLEANUP_PURGE'

# get rid of crud
sqlplus "$conn" <<SQL >/dev/null 2>&1 || echo "all OK"
drop table report_agency_sessions;
drop table reports;
exit
SQL


for colType in NUMBER 'NUMBER(20)'; do
  for delta in "+ $fraction - $row_max" "+ $fraction"; do
    # for mode in 'fk' 'REPORT_CLEANUP_PURGE_NAIVE' 'REPORT_CLEANUP_PURGE'; do
    for mode in 'fk' 'REPORT_CLEANUP_PURGE_NAIVE' $blow_up; do

cat <<TXT
--------------------------------------------------------------------------------
Configuration

column type: $colType
delta      : $delta
mode       : $mode
--------------------------------------------------------------------------------
TXT

cat <<SQL >|"$file"
set echo on
timing start main
SQL


cat <<SQL >>"$file"
create table reports (
  report# $colType primary key,
  status varchar2(10),
  created date not null,
  fill_data varchar2(2000)
);

create index reports_delete_idx on reports ( status, created ) compress 1;

create table report_agency_sessions (
  report# $colType,
  session# $colType,
  constraint REPORT_AGENCY_SESSION_PK primary key (report#, session#)
) organization index;


CREATE OR REPLACE PROCEDURE REPORT_CLEANUP_PURGE(p_rowlimit NUMBER DEFAULT 1000)
AS
TYPE report_tab
IS
  TABLE OF reports.report#%TYPE;
  rep# report_tab;
  CURSOR del_cur
  IS
     SELECT report#
       FROM reports
      WHERE status = 'RECENT'
    AND created < TRUNC(sysdate);
BEGIN
  LOOP
    OPEN del_cur;
    LOOP
      FETCH del_cur BULK COLLECT INTO rep# LIMIT p_rowlimit;

      IF rep#.count = 0 THEN
        CLOSE del_cur;
        EXIT;
      END IF;

      FORALL i IN rep#.FIRST..rep#.LAST
        DELETE FROM reports WHERE report# = rep#(i);

      FORALL i IN rep#.FIRST..rep#.LAST
        DELETE FROM report_agency_Sessions WHERE report# = rep#(i);

      COMMIT;
    END LOOP;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    CLOSE del_cur;
    RAISE;
END;
/
show errors

CREATE OR REPLACE PROCEDURE REPORT_CLEANUP_PURGE_NAIVE(p_rowlimit NUMBER DEFAULT 1000)
AS
  C NUMBER;
BEGIN
  C := 0;

  FOR r IN (
     SELECT report#
       FROM reports
      WHERE status = 'RECENT'
       AND created < TRUNC(sysdate)
    )
  LOOP
    DELETE FROM reports WHERE report# = r.report#;
    DELETE FROM report_agency_Sessions WHERE report# = r.report#;
    C := C + 1;

    IF C >= p_rowlimit THEN
      COMMIT;
      C := 0;
    END IF;
  END LOOP;
END;
/
show errors
SQL

if [ "$mode" = 'fk' ]; then
cat <<SQL >>"$file"
alter table report_agency_sessions
  add constraint report_fk
    foreign key ( report# )
      references reports ( report# )
      on delete cascade;
SQL
fi

cat <<SQL >>"$file"
timing start insert data
insert into reports
select level - 1
  , case mod(level, 2)
      when 0 then 'RECENT'
      else 'OTHER'
    end
  , sysdate + ((level $delta) / 1000.0)
  , rpad('x', 150, 'y')
from dual
connect by level <= $row_max;

insert into report_agency_sessions
select floor((level - 1) / 4)
  , mod(level, 4)
from dual
connect by level <= (4 * $row_max);

commit;
timing stop

timing start gather stats
call dbms_stats.gather_table_stats(user, 'REPORTS');
call dbms_stats.gather_table_stats(user, 'REPORT_AGENCY_SESSIONS');
timing stop

REM a bit of statistic
with v as (
  select case
    when created < trunc(sysdate) and status = 'RECENT' then
      'DELETE'
    else
      'KEEP'
    end as oper
  from reports
)
select oper, count(*) count
from v
group by oper
order by oper;

timing start delete
SQL

case "$mode" in
fk)
cat <<SQL >>"$file"
set pagesize 100 linesize 1000
set autotrace on
DELETE
  FROM reports
 WHERE status = 'RECENT'
  AND created < TRUNC(sysdate);
set autotrace off
SQL
  ;;

*)
cat <<SQL >>"$file"
call $mode();
SQL
  ;;
esac


cat <<SQL >>"$file"
commit;
-- column type: $colType
-- delta      : $delta
-- mode       : $mode
timing stop

drop procedure report_cleanup_purge;
drop procedure report_cleanup_purge_naive;
drop table report_agency_sessions;
drop table reports;
timing stop
exit
SQL

      sqlplus "$conn" "@$file"

    done
  done
done