/* 
   ora_tpcb_pop.sql
   v.1.0.0

   TPC-B Benchmarck table population
   Scale=1 --> Tellers= 10 Accounts=100.000

   by mail@meo.bogliolo.name

*/
set timing on
set serverout on

TRUNCATE TABLE BRANCHES;
TRUNCATE TABLE TELLERS;
TRUNCATE TABLE ACCOUNTS;
TRUNCATE TABLE HISTORY;

drop index PI_BRANCHES_BID;
drop index PI_TELLERS_TID;
drop index PI_ACCOUNTS_AID;

declare
        a number;
        b number;
        t number;
        scale number;
begin
scale:=1;
for b in 0..(scale-1) loop
        INSERT INTO branches (Bid, Bbalance, filler)
                VALUES (b, 0, 'BThe quick brown fox runs over the lazy dog0123456789');
        for t in 0..9 loop
                INSERT INTO tellers (Tid, Bid, Tbalance, filler)
                        VALUES (t+b*10, b, 0, 'TThe quick brown fox runs over the lazy dog0123456789');
        end loop;
        for a in 0..99999 loop
                INSERT INTO accounts (Aid, Bid, Abalance, filler)
                        VALUES (a+b*100000, b, 0, 'AThe quick brown fox runs over the lazy dog0123456789');
        end loop;

        DBMS_OUTPUT.PUT_LINE( 'Branch #'||b||' created');
end loop;
end;
/

create unique index PI_BRANCHES_BID on BRANCHES(BID);
create unique index PI_TELLERS_TID on TELLERS(TID);
create unique index PI_ACCOUNTS_AID on ACCOUNTS(AID);

analyze table BRANCHES compute statistics;
analyze table TELLERS compute statistics;
analyze table ACCOUNTS compute statistics;