This quiz is easier than Index Quiz 1 because you can just run the code, dump blocks, and find the answer. But see if you can work out what’s going to happen before you do the test. Note that I’ve set initrans to 4 on both the table and index.
create table t1 ( v1 varchar2(20) ) initrans 4 ; create index t1_i1 on t1(v1) initrans 4; insert into t1 values(lpad('0',20)); commit;
The following output is the ITL section I get from dumping the first block of the table – notice that we have four entries (itc = 4) obeying my initrans 4. The question is: if you dump the first block of the index how many ITL entries will there be (from 9i onwards) ?
Block header dump: 0x01400c8a Object id on Block? Y seg/obj: 0x183e2 csc: 0x00.5528efc itc: 4 flg: O typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000b.014.00004c37 0x008010b7.0259.1e --U- 1 fsc 0x0000.05528efe 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
I now execute the pl/sql loop below to insert 300 consecutive rows in the table. Using the standard 8KB block size this is enough data to cause a leaf node split, which will be a 90/10 leaf node split leaving one leaf block full and the other with 52 rows:
begin for i in 1..300 loop insert into t1 values(lpad(i,20)); commit; end loop; end; /
If you dump the three resulting index blocks (the root block and two leaf blocks) how many entries will each ITL have ?
Drop and recreate the table and its index but don’t insert any data into it straight away. Start up 10 new sessions and from each session insert one row into the table – but don’t commit. Use the values lpad(1,20) to lpad(10,20) in turn. Dump the single index block – how many entries should you see in the ITL.
Now commit each session, then execute the following pl/sql loop from one of them (it starts from 11, not 1, but that’s not particularly significant, just my tidy mind):
begin for i in 11..300 loop insert into t1 values(lpad(i,20)); commit; end loop; end; /
Again, if you dump the resulting three index blocks what will the ITLs look like ?