SQL> start ./doRbExpr.sql ch --------------------------------------------------------------------------------------------------- Index root block verification experiments... Fri Nov 11 11:54:11 PST 2011 --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- -- RbExpr: Experiment 1: create table as select, create index, test --------------------------------------------------------------------------------------------------- Connected. --- create table and load 10000 rows... --- create index... --- get index stats... --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5000873 blevel=1 From trace file after index dump, DBA is : 5000873 blevel=1) --------------------------------------------------------------------------------------------------- -- RbExpr: Experiment 2: create table, insert rows, create index, test --------------------------------------------------------------------------------------------------- Connected. --- create table and load NO rows... --- insert 10000 rows and commit... --- create index... --- get index stats... --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5001177 blevel=1 From trace file after index dump, DBA is : 5001177 blevel=1) --------------------------------------------------------------------------------------------------- -- RbExpr: Experiment 3: create table, insert rows, create index, test, insert rows, test --------------------------------------------------------------------------------------------------- Connected. --- create table and load NO rows... --- insert 10000 rows and commit... --- create index... --- get index stats... --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5001481 blevel=1 From trace file after index dump, DBA is : 5001481 blevel=1) --- insert 1000 rows and commit... --- get index stats... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5001481 blevel=1 From trace file after index dump, DBA is : 5001481 blevel=1) --------------------------------------------------------------------------------------------------- -- RbExpr: Experiment 4: create table, insert rows, create index, -- test, delete all rows, test, insert rows, test --------------------------------------------------------------------------------------------------- Connected. --- create table and load NO rows... --- insert 10000 rows and commit... --- create index... --- get index stats... --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5001961 blevel=1 From trace file after index dump, DBA is : 5001961 blevel=1) --- delete all table rows and commit... --- get index stats... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5001961 blevel=1 From trace file after index dump, DBA is : 5001961 blevel=1) --- insert 10000 rows and commit... --- get index stats... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5001961 blevel=1 From trace file after index dump, DBA is : 5001961 blevel=1) --------------------------------------------------------------------------------------------------- -- RbExpr: Experiment 5: create table, insert rows, create index, -- test, truncate, test, insert rows, test --------------------------------------------------------------------------------------------------- Connected. --- create table and load NO rows... --- insert 10000 rows and commit... --- create index... --- get index stats... --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002265 blevel=1 From trace file after index dump, DBA is : 5002265 blevel=1) --- truncate table... --- get index stats... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002265 blevel=0 From trace file after index dump, DBA is : --- insert 10000 rows into table and commit... --- get index stats... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002265 blevel=1 From trace file after index dump, DBA is : 5002265 blevel=1) --------------------------------------------------------------------------------------------------- -- RbExpr: Experiment 6: Many things... (THE big test) --------------------------------------------------------------------------------------------------- Connected. --- create table... --- insert 1000 rows and commit... --- create index... --- get index details... --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002337 blevel=1 From trace file after index dump, DBA is : 5002337 blevel=1) --- insert more rows (causing new blevel) and commit... --- get index details... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002337 blevel=2 From trace file after index dump, DBA is : 5002337 blevel=2) --- delete all rows and commit... --- get index details... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002337 blevel=2 From trace file after index dump, DBA is : 5002337 blevel=2) --- insert more rows and commit... --- get index details... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002337 blevel=2 From trace file after index dump, DBA is : 5002337 blevel=2) --- drop and create index... --- get index details... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002337 blevel=2 From trace file after index dump, DBA is : 5002337 blevel=2) --- insert rows and commit... --- get index details... Connected. --- Generating (gathering) index stats... --- Getting index stats from data dictionary and by dumping index... . From data dictionary, DBA for header block plus one is: 5002337 blevel=2 From trace file after index dump, DBA is : 5002337 blevel=2) --------------------------------------------------------------------------------------------------- -- END --------------------------------------------------------------------------------------------------- SQL> exit