-- File        : doRbExpr.sql - Run the root block verification experiemnts
-- Author      : Craig Shallahamer, craig@orapub.com
-- Date        : 4-Nov-2011 in Perth, Australia
-- Warrenty    : There is no warrenty, that is, use at your own risk. 
-- Purpose     : The hypothesis is an index root block is ALWAYS the next block
--               following the index segment header block; regardless of index change
--               such as index splits, growth, inserts, deletes, truncates, 
--               updates, etc. The following
--               is a series of tests to see if it is possible to somehow create
--               a situation where the index root block does not follow the
--               header block. Both the data dictionary and tracing will be used
--               determine the index root block and the index depth (blevel).
--               
-- Usage       : SQL> start doRbExpr.sql <prefix>
-- 
-- Dependency  : The sister script is, getIdxRtBlk.sql. This script calculates 
-- the root block as the index segment block header block plus one, calculates the
-- data block address (DBA), and prints it. It also, dumps the index into a trace 
-- file, parses it pulling out both the index root block's datablock 
-- address (DBA) and the index level (blevel).

-- Prefix. An object prefex is used to ensure dropping and re-creating an object is
-- not a factor. The objects created are not dropped/removed... it's up to you.
--
def prefix=&1

-- To pull from the index dump, we need to know the trace file directory 
def tracePath=/home/oracle/admin/diag/rdbms/prod18/prod18/trace/prod18_ora

-- Enter the Oracle username and password to be used during the experiment
def uid=system
def pw=manager

set serveroutput on
set verify off
set echo off 
set feedback off

!echo "---------------------------------------------------------------------------------------------------"
!echo "Index root block verification experiments..."
!date
!echo "---------------------------------------------------------------------------------------------------"
!echo "---------------------------------------------------------------------------------------------------"
!echo "-- RbExpr: Experiment 1: create table as select, create index, test"
!echo "---------------------------------------------------------------------------------------------------"
connect &uid/&pw
!echo "---  create table and load 10000 rows..."
create table &prefix._1_irb as select * from dba_objects where rownum<10000;
!echo "---  create index..."
create index &prefix._1_irb_i on &prefix._1_irb (object_name);
!echo "---  get index stats..."
start ./getIdxRtBlk &uid &prefix._1_irb_i &tracePath

!echo "---------------------------------------------------------------------------------------------------"
!echo "-- RbExpr: Experiment 2: create table, insert rows, create index, test"
!echo "---------------------------------------------------------------------------------------------------"
connect &uid/&pw
!echo "---  create table and load NO rows..."
create table &prefix._2_irb as select * from dba_objects where 1=0;
!echo "---  insert 10000 rows and commit..."
insert into &prefix._2_irb select * from dba_objects where rownum<10000;
commit;
!echo "---  create index..."
create index &prefix._2_irb_i on &prefix._2_irb (object_name);
!echo "---  get index stats..."
start ./getIdxRtBlk &uid &prefix._2_irb_i &tracePath

!echo "---------------------------------------------------------------------------------------------------"
!echo "-- RbExpr: Experiment 3: create table, insert rows, create index, test, insert rows, test"
!echo "---------------------------------------------------------------------------------------------------"
connect &uid/&pw
!echo "---  create table and load NO rows..."
create table &prefix._3_irb as select * from dba_objects where 1=0;
!echo "---  insert 10000 rows and commit..."
insert into &prefix._3_irb select * from dba_objects  where rownum<10000;
commit;
!echo "---  create index..."
create index &prefix._3_irb_i on &prefix._3_irb (object_name);
!echo "---  get index stats..."
start ./getIdxRtBlk &uid &prefix._3_irb_i &tracePath
!echo "---  insert 1000 rows and commit..."
insert into &prefix._3_irb select * from dba_objects where rownum<10000;
commit;
!echo "---  get index stats..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._3_irb_i &tracePath

!echo "---------------------------------------------------------------------------------------------------"
!echo "-- RbExpr: Experiment 4: create table, insert rows, create index, "
!echo "-- test, delete all rows, test, insert rows, test"
!echo "---------------------------------------------------------------------------------------------------"
connect &uid/&pw
!echo "---  create table and load NO rows..."
create table &prefix._4_irb as select * from dba_objects where 1=0;
!echo "---  insert 10000 rows and commit..."
insert into &prefix._4_irb select * from dba_objects where rownum<10000;
commit;
!echo "---  create index..."
create index &prefix._4_irb_i on &prefix._4_irb (object_name);
!echo "---  get index stats..."
start ./getIdxRtBlk &uid &prefix._4_irb_i &tracePath
!echo "---  delete all table rows and commit..."
delete from &prefix._4_irb;
commit;
!echo "---  get index stats..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._4_irb_i &tracePath
!echo "---  insert 10000 rows and commit..."
insert into &prefix._4_irb select * from dba_objects where rownum<10000;
commit;
!echo "---  get index stats..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._4_irb_i &tracePath

!echo "---------------------------------------------------------------------------------------------------"
!echo "-- RbExpr: Experiment 5: create table, insert rows, create index, "
!echo "-- test, truncate, test, insert rows, test"
!echo "---------------------------------------------------------------------------------------------------"
connect &uid/&pw
!echo "---  create table and load NO rows..."
create table &prefix._5_irb as select * from dba_objects where 1=0;
!echo "---  insert 10000 rows and commit..."
insert into &prefix._5_irb select * from dba_objects where rownum<10000;
commit;
!echo "---  create index..."
create index &prefix._5_irb_i on &prefix._5_irb (object_name);
!echo "---  get index stats..."
start ./getIdxRtBlk &uid &prefix._5_irb_i &tracePath
!echo "---  truncate table..."
truncate table &prefix._5_irb;
!echo "---  get index stats..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._5_irb_i &tracePath
!echo "---  insert 10000 rows into table and commit..."
insert into &prefix._5_irb select * from dba_objects where rownum<10000;
commit;
!echo "---  get index stats..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._5_irb_i &tracePath

!echo "---------------------------------------------------------------------------------------------------"
!echo "-- RbExpr: Experiment 6: Many things... (THE big test)"
!echo "---------------------------------------------------------------------------------------------------"
connect &uid/&pw
!echo "---  create table..."
create table &prefix._6_irb as select * from dba_objects where 1=0;
!echo "---  insert 1000 rows and commit..."
insert into &prefix._6_irb select * from dba_objects where rownum<1000;
commit;
!echo "---  create index..."
create index &prefix._6_irb_i on &prefix._6_irb (owner,object_name,timestamp,edition_name,object_type,created);
!echo "---  get index details..."
start ./getIdxRtBlk &uid &prefix._6_irb_i &tracePath
!echo "---  insert more rows (causing new blevel) and commit..."
insert into &prefix._6_irb select * from dba_objects;
commit;
!echo "---  get index details..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._6_irb_i &tracePath
!echo "---  delete all rows and commit..."
delete from &prefix._6_irb;
commit;
!echo "---  get index details..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._6_irb_i &tracePath
!echo "---  insert more rows and commit..."
insert into &prefix._6_irb select * from dba_objects;
commit;
!echo "---  get index details..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._6_irb_i &tracePath
!echo "---  drop and create index..."
drop index &prefix._6_irb_i;
create index &prefix._6_irb_i on &prefix._6_irb (owner,object_name,timestamp,edition_name,object_type,created);
!echo "---  get index details..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._6_irb_i &tracePath
!echo "---  insert rows and commit..."
insert into &prefix._6_irb select * from dba_objects;
commit;
!echo "---  get index details..."
connect &uid/&pw
start ./getIdxRtBlk &uid &prefix._6_irb_i &tracePath

!echo "---------------------------------------------------------------------------------------------------"
!echo "-- END"
!echo "---------------------------------------------------------------------------------------------------"