Yes. I have Jonathan Lewis's book
Logan Palanisamy, March 31, 2003 - 8:37 pm UTC
Tom,
Thanks. I will go through Jonathan Lewis' book.
March 31, 2003 - 9:00 pm UTC
page 113
Don't understand what Jonathan says
Logan Palanisamy, April 01, 2003 - 5:20 pm UTC
Tom,
I read pages 113 and 114 of 'Practical Oracle 8i' book by Jonathan Lewis. Understood only a part of it. Couldn't understand the following two items, even after reading about bitmap indexes on chapter 6 of the same book.
1. his conjecture about block_size/12 on page 113
2. the maths in the paragraph "The following sample ..." on page 114
So, I still don't know how 'minimize records_per_block' produces compact bitmap indexes.
Thanks
April 01, 2003 - 8:29 pm UTC
1) is not conjecture. He is stating the fact that "Oracle assumes there will be block_size/12 rows per block"
2) isn't really math -- but the result of a block dump he performed.
Here is a concrete example -- I have put 5-7 rows per block and have 1.8 million rows ...
ops$tkyte@ORA920> drop table t;
Table dropped.
ops$tkyte@ORA920> create table t ( x int, y char(1) ) pctfree 99 pctused 1;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t
2 select mod(rownum,3), 'x'
3 from big_table.big_table;
1833792 rows created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create bitmap index t_idx on t(x);
Index created.
ops$tkyte@ORA920> exec show_space( 'T_IDX', USER, 'INDEX' )
Free Blocks.............................
Total Blocks............................384
Total Bytes.............................3145728
Total MBytes............................3
Unused Blocks...........................33
Unused Bytes............................270336
Last Used Ext FileId....................9
Last Used Ext BlockId...................70792
Last Used Block.........................95
PL/SQL procedure successfully completed.
<b>so, that is 351 blocks... now we do the alter</b>
ops$tkyte@ORA920> drop index t_idx;
Index dropped.
ops$tkyte@ORA920> alter table t minimize records_per_block;
Table altered.
ops$tkyte@ORA920> create bitmap index t_idx on t(x);
Index created.
ops$tkyte@ORA920> exec show_space( 'T_IDX', USER, 'INDEX' )
Free Blocks.............................
Total Blocks............................256
Total Bytes.............................2097152
Total MBytes............................2
Unused Blocks...........................117
Unused Bytes............................958464
Last Used Ext FileId....................9
Last Used Ext BlockId...................70664
Last Used Block.........................11
PL/SQL procedure successfully completed.
<b>and that is 139 blocks (256 - 117). The second index is 39% the size of the first -- same data, same order -- just let Oracle in on the fact that there were only a couple of rows per block</b>
But..
Ganesh Raja, April 01, 2003 - 11:01 pm UTC
How does oracle reduce the size of a bitmap index when we say that we are not storing (Blocks/12) rows in the Block but only 10 Rows or 12 Rows per Block.
If i am not wrong then Alter Table Minimize Records_Per_Block just says that This is the maximum number of rows that this Table can have per block.
Jonathan gives a good example on how to Rebuild a Table that has rows at diffrent stages in the application workflow .. Like Just Inserted, 50%Updated and 100% Updated using the alter Table Minimize Records_per_block.
Thanks.
Regards,
Ganesh R
April 02, 2003 - 7:36 am UTC
it can better manage the space in the bitmaps. Before 8.1.5, Oracle looks at the table definition to compute the maximum possible number of records which can be stored in a block of that table. This value can be found in the column TAB$.SPARE1 but the value can be much larger than what will be really stored in the table.
Therefore, bitmap indexes using this value have lots of unnecessary artificial 0 bits on the end of every block, which have to be compressed away at some cost.
It is that "at some cost" you are saving on here.
maximum number of records per block
A reader, April 02, 2003 - 7:43 pm UTC
SQL> create table t0 (c1 varchar2(10)) pctfree 0;
Table created.
SQL> create table t1 (c1 varchar2(10)) pctfree 0;
Table created.
SQL> create table t2 (c1 varchar2(10)) pctfree 0;
Table created.
SQL> create table t3 (c1 varchar2(10)) pctfree 0;
Table created.
SQL>
SQL> insert into t0 select null from all_objects where rownum < 6001;
6000 rows created.
SQL> insert into t1 select 'a' from all_objects where rownum < 6001;
6000 rows created.
SQL> insert into t2 select 'aaaaa' from all_objects where rownum < 6001;
6000 rows created.
SQL> insert into t3 select 'aaaaaa' from all_objects where rownum < 6001;
6000 rows created.
SQL>
SQL> analyze table t0 compute statistics;
Table analyzed.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> analyze table t2 compute statistics;
Table analyzed.
SQL> analyze table t3 compute statistics;
Table analyzed.
SQL>
SQL> select table_name tbl_name, avg_row_len, blocks blks,
2 empty_blocks empty_blks, NUM_FREELIST_BLOCKS freelist_blks,
3 AVG_SPACE_FREELIST_BLOCKS avg_space_freelist_blks
4 from user_tables
5 where table_name in ('T0', 'T1', 'T2', 'T3');
TBL_NAME AVG_ROW_LEN BLKS EMPTY_BLKS FREELIST_BLKS AVG_SPACE_FREELIST_BLKS
---------- ----------- ---- ---------- ------------- -----------------------
T0 3 9 0 1 6870
T1 5 9 0 1 6870
T2 9 9 0 1 6870
T3 10 9 0 1 902
SQL>
SQL> select 'T0' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
2 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block fr0
3 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
4 group by 'T0', rows_per_block
5 union all
6 select 'T1' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
7 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block fr1
8 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
9 group by 'T1', rows_per_block
10 union all
11 select 'T2' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
12 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block fr2
13 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
14 group by 'T2', rows_per_block
15 union all
16 select 'T3' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
17 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block fr3
18 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
19 group by 'T3', rows_per_block
20 order by 1, 2;
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
---------- -------------- ---------------------
T0 112 1
T0 736 8
T1 112 1
T1 736 8
T2 112 1
T2 736 8
T3 600 1
T3 675 8
8 rows selected.
Maximum number of records per block
Logan Palanisamy, April 02, 2003 - 8:28 pm UTC
Tom,
I am trying to find out why the maximum number of records per block is approximately (block_size/12). My block size is 8192.
I create 4 identical tables. I purposely set pctfree to 0 so that I can stuff the maximum possible number of records. I insert 6000 rows each, but with different column values for each table to get different avg_len for the records.
Here is my test results:
SQL> create table t0 (c1 varchar2(10)) pctfree 0;
Table created.
SQL> create table t1 (c1 varchar2(10)) pctfree 0;
Table created.
SQL> create table t2 (c1 varchar2(10)) pctfree 0;
Table created.
SQL> create table t3 (c1 varchar2(10)) pctfree 0;
Table created.
SQL>
SQL> insert into t0 select null from all_objects where rownum < 6001;
6000 rows created.
SQL> insert into t1 select 'a' from all_objects where rownum < 6001;
6000 rows created.
SQL> insert into t2 select 'aaaaa' from all_objects where rownum < 6001;
6000 rows created.
SQL> insert into t3 select 'aaaaaa' from all_objects where rownum < 6001;
6000 rows created.
SQL>
SQL> analyze table t0 compute statistics;
Table analyzed.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> analyze table t2 compute statistics;
Table analyzed.
SQL> analyze table t3 compute statistics;
Table analyzed.
SQL>
SQL> select table_name tbl_name, avg_row_len, blocks blks,
2 empty_blocks empty_blks, NUM_FREELIST_BLOCKS freelist_blks,
3 AVG_SPACE_FREELIST_BLOCKS avg_space_freelist_blks
4 from user_tables
5 where table_name in ('T0', 'T1', 'T2', 'T3');
TBL_NAME AVG_ROW_LEN BLKS EMPTY_BLKS FREELIST_BLKS AVG_SPACE_FREELIST_BLKS
---------- ----------- ---- ---------- ------------- -----------------------
T0 3 9 0 1 6870
T1 5 9 0 1 6870
T2 9 9 0 1 6870
T3 10 9 0 1 902
SQL>
SQL> select 'T0' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
2 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block
3 from t0
4 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
5 group by 'T0', rows_per_block
6 union all
7 select 'T1' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
8 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block
9 from t1
10 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
11 group by 'T1', rows_per_block
12 union all
13 select 'T2' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
14 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block
15 from t2
16 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
17 group by 'T2', rows_per_block
18 union all
19 select 'T3' tbl_name, rows_per_block, count(*) number_of_such_blocks from (
20 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block
21 from t3
22 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid ))
23 group by 'T3', rows_per_block
24 order by 1, 2;
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
---------- -------------- ---------------------
T0 112 1
T0 736 8
T1 112 1
T1 736 8
T2 112 1
T2 736 8
T3 600 1
T3 675 8
8 rows selected.
SQL>
SQL> spool off
My questions are:
1. For tables t0, t1 and t2, the avg_len of the row is 3, 5 and 9 respectively with a maximum of only 736 rows per block. For t2, I can understand that (736 * 9) comes closer to 8192 with some block over head. But why does it stuff the same 736 rows per block when the avg_len is only 3 or 5.
It certainly could have packed more rows per block. But it couldn't because, the block header has space only for 736 entries even though there is space in the rest of the block. Am I correct?
2. T2 has one freelist block with a freespace of 6870. T3 has one free list block also with a freespace of 902 bytes. Since the avg_len of T2 is 9, and T3 is 10, T3 should have used up 6000 more bytes for 6000 rows. So for T3, I was expecting a free space of only 870 (6870 - 6000). But how come it is 902?
Thanks
Why blocksize / 12
Jonathan Lewis, April 05, 2003 - 1:08 pm UTC
In answer to just this specific question - and avoiding the detail of reworking the example and counting bytes - the approximation of blocksize/12 comes from Oracle using a worst-case scenario.
Assume that every single row in a block has migrated. What is the largest possible number of migrated row entries that could appear in a single block ? Count bytes as follows for each row:
2 bytes for the row index (offset) entry
1 byte for the row length
1 byte for the flags (which will say 'migrated')
1 byte for the lock flag
6 bytes for the "rowid" that the row has been migrated to.
Total 11 bytes. In the extreme case, therefore, the maximum number of rows you could squeeze into a block would use 11 bytes per row - but there is some overhead taken out of the block for the cache header, transaction header and so on. Hence my "approximately blocksize / 12"
It might have been more sensible if I had been more precise and said (blocksize - XXX) / 11, or even 'slightly less than blocksize / 11' - but I just happened to pick a very simple way of expressing the result.
For an 8K block with INITRANS=1, the answer happens to be 736 - so if you actually have rows that use, say, 400 bytes each (for a total of 20 rows per block) Oracle has to include a (compressed/encoded) string of 716 extra zero bits which might turn into one bits at some time in the future. And this extra 716 bits has to exist in the index for each recorded key value, for each block in the table. (Luckily the compression/encoding strategy is very good).
Where correct number of rows per block is stored after minimiza records_per_block command?
Boris, October 22, 2003 - 11:39 pm UTC
Why max number of rows per block is not correct in sys.tab$? Jonathan Lewis wrote "The value stored in SPARE1 is "maximum number of rows per block plus 32767". This is not true in my case, i got 269 instead of 166?
05:30:12 db@IASDB.DELOA.DELO_PRODAJA.SI_901> alter table db_dobavnica_gla minimize records_per_block
;
Table altered.
Elapsed: 00:00:00.09
05:30:18 db@IASDB.DELOA.DELO_PRODAJA.SI_901> select spare1 - 32767 max_rows_per_block from sys.tab$
where obj# in (select object_id from dba_objects where owner = 'DB' and object_name = 'DB_DOBAVNICA_
GLA' and object_type='TABLE');
MAX_ROWS_PER_BLOCK
------------------
269
1 row selected.
Elapsed: 00:00:00.03
05:30:24 db@IASDB.DELOA.DELO_PRODAJA.SI_901> SELECT MAX (rows_per_block)
05:30:32 2 FROM (SELECT DBMS_ROWID.rowid_block_number (ROWID),
05:30:32 3 COUNT (*) rows_per_block
05:30:32 4 FROM db_dobavnica_poz
05:30:32 5 GROUP BY DBMS_ROWID.rowid_block_number (ROWID));
MAX(ROWS_PER_BLOCK)
-------------------
166
1 row selected.
Elapsed: 00:00:20.01
05:30:52 db@IASDB.DELOA.DELO_PRODAJA.SI_901>
October 23, 2003 - 8:10 am UTC
things change.
sys.tab$ is 110% undocumented.
you cannot count rows/block using rowids!!! (chained rows, migrated rows -- you cannot use a rowid to do anything other then get a "hazy" guess)
You would have to test like this:
ops$tkyte@ORA920> create table t ( x int );
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t select rownum from all_objects where rownum <= 100;
100 rows created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t minimize records_per_block;
Table altered.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select spare1 - 32767 max_rows_per_block from sys.tab$
2 where obj# = ( select object_id from user_objects where object_name = 'T' );
MAX_ROWS_PER_BLOCK
------------------
100
Pl. clarify
A reader, November 18, 2003 - 2:39 pm UTC
Tom,
I have the following set of SQL statements
drop table tt
/
create table tt (col1 number)
/
insert into tt values(5)
/
alter table tt minimize records_per_block
/
insert into tt values(6)
/
insert into tt values(7)
/
analyze table tt compute statistics for table for all indexes
/
After running the above script, I ran the script
select a.blocks,num_rows,b.blocks,empty_blocks
from user_tables a,
user_segments b
where a.table_name = 'TT'
and b.segment_name = 'TT'
and expected that the 3 rows will be placed in 3 different blocks as a result of minimize records_per_block statement after the first INSERT. Surprisingly, I see that there are only 2 blocks that hold table data (1 record in Block 15882 and 2 records in Block 15883. However, this is the following excerpt from Oracle documentation.
"Specify MINIMIZE to instruct Oracle to calculate the largest number of records in any block in the table, and limit future inserts so that no block can contain more than that number of records".
Could you pls. clarify why the table rows are stored only in 2 blocks instead of 3 (in spite of minimize record per block clause after the first INSERT) and what does the "minimize records per block" mean.
November 21, 2003 - 10:47 am UTC
2 is the minimum number of "minimum" rows, it'll not just do one, it'll do 2 and up. minimize will minimize to the greatest( 2, MAX_ROWS_ON_BLOCK_RIGHT_NOW )
records per block
reader, March 31, 2004 - 9:54 am UTC
ops$tkyte@ORA920> select spare1 - 32767 max_rows_per_block from sys.tab$
2 where obj# = ( select object_id from user_objects where object_name = 'T'
);
In your above query, what is spare1 - 32767? Where did the value 32767 come from? Thanks.
March 31, 2004 - 10:23 am UTC
powers of two. you just have to "know" to do that to get the information out of this field.