| restrict no. of rows in a block [message #59916] |
Sat, 03 January 2004 22:17  |
ashokmote
Messages: 56
Registered: December 2003
|
Member |
|
|
how can i make only allow these many rows to be inserted into a block.how can i restrict these many no. of rows to be inserted into a single block
example:- only 10 rows should be inserted into a block not more than 10.
|
|
|
|
| Re: restrict no. of rows in a block [message #59921 is a reply to message #59916] |
Sun, 04 January 2004 05:15  |
croca
Messages: 50
Registered: January 2004
|
Member |
|
|
mmm....i dont remember any formula for doing this.
The number of row you can store into a single db block depends on the db block size when you created your db and the OS block size.
Or maybe you can implement CLUSTERS with your tables, that way you can instruct oracle to store a group of rows into single blocks. you should check the Administrators guide to learn a bit more about clusters, i never implemented it.
Regards.
|
|
|
|
| Re: restrict no. of rows in a block [message #59923 is a reply to message #59916] |
Sun, 04 January 2004 06:34 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
You can try this :
records_per_block_clause of the ALTER TABLE statement. I havent used this,so just quoting from the manual:
Quote from manual :
"records_per_block_clause
The records_per_block_clause lets you specify whether Oracle restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as small (compressed) as possible.
Restrictions on Restricting Records in a Block
You cannot specify either MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. You must first drop the bitmap index.
You cannot specify this clause for an index-organized table or nested table.
MINIMIZE
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.
Oracle Corporation recommends that a representative set of data already exist in the table before you specify MINIMIZE. If you are using data segment compression (see data_segment_compression), then a representative set of compressed data should already exist in the table.
Restriction on MINIMIZE
You cannot specify MINIMIZE for an empty table.
NOMINIMIZE
Specify NOMINIMIZE to disable the MINIMIZE feature. This is the default.
"
In earlier releases(prior to 8i),following is possible.
For eg,if your block size is 4k and your row size is 100 bytes, then 10 rows occupy roughly 1k, which means the remaining 3k-block header space has to be reserved for updates(PCTFREE). Assuming this free space is 2.8K which is 2.8/4*100=70 and so your pctfree has to be approximately 70% ! to allow only 10 rows per block.
Again,I never had the need to try this,just writing what came to my mind!
Thiru
|
|
|
|
|
|
| Re: restrict no. of rows in a block [message #59930 is a reply to message #59925] |
Sun, 04 January 2004 12:39 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Not necessarily. Lets try to limit the records per block using this specification with no bitmap indexes.
Case 1: default . No specification.
thiru@9.2.0:SQL>drop table t;
thiru@9.2.0:SQL>create table t(x varchar2(2000));
thiru@9.2.0:SQL>set feedback off
thiru@9.2.0:SQL>insert into t values('1');
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>select count(*) from t;
COUNT(*)
----------
10
thiru@9.2.0:SQL>insert into t select rownum from all_objects;
thiru@9.2.0:SQL>select avg(cnt),min(cnt),max(cnt) from
2 (select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) cnt from t
3 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
4 );
AVG(CNT) MIN(CNT) MAX(CNT)
---------- ---------- ----------
323.913043 225 325
-- So without doing anything a max of 325 rows went into one block
thiru@9.2.0:SQL>select spare1-32767 max_rows_per_block from sys.tab$ where
2 obj#=(select object_id from user_objects where object_name='T');
MAX_ROWS_PER_BLOCK
------------------
-32403
Case 2: USe Minimize records_per_block. Again no bitmap indexes
thiru@9.2.0:SQL>drop table t;
Table dropped.
thiru@9.2.0:SQL>create table t(x varchar2(2000));
Table created.
thiru@9.2.0:SQL>set feedback off
thiru@9.2.0:SQL>insert into t values('1');
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>/
thiru@9.2.0:SQL>select count(*) from t;
COUNT(*)
----------
10
<B>thiru@9.2.0:SQL>alter table t minimize records_per_block;</B>
thiru@9.2.0:SQL>insert into t select rownum from all_objects;
thiru@9.2.0:SQL>select avg(cnt),min(cnt),max(cnt) from
2 (select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) cnt from t
3 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
4 );
AVG(CNT) MIN(CNT) MAX(CNT)
---------- ---------- ----------
10 10 10
-- A maximum of 10 rows went into one block
thiru@9.2.0:SQL>select spare1-32767 max_rows_per_block from sys.tab$ where
2 obj#=(select object_id from user_objects where object_name='T');
MAX_ROWS_PER_BLOCK
------------------
10
Note: this demo was done with help from Tom Kyte.
I am sure Oracle had bitmap indexes in mind when designing this.
-Thiru
|
|
|
|
| Re: restrict no. of rows in a block [message #59932 is a reply to message #59930] |
Sun, 04 January 2004 14:56 |
croca
Messages: 50
Registered: January 2004
|
Member |
|
|
I think this is great. I was convinced that minimize rows per block was related only to bitmap indexes.
Just curious: what's the big deal by doing this?. A time ago, DBA's wanted to store lots of rows into a single block for performance gain when executing queries, you set db_block_read_count (or something like that), just to perform a few disk accesses but getting lot of rows stored into a single block....and now we want the opposite?....what we get minimizing rows per block?...
just courious..
Thank you.........
|
|
|
|
| Re: restrict no. of rows in a block [message #59946 is a reply to message #59932] |
Mon, 05 January 2004 05:46 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
No,No I am not suggesting that we should minimize the number of rows per block. That would be a new myth !
Basically,by altering the table to minimize the number of records per block,we are instructing Oracle to compact the bitmap Indexes when they are created, looking at the 'current' max_records_per_block from the table blocks. This way the bitmap Indexes do not allocate unnecessary space(which 'might' go as a waste or incur additional cost for compression) becos now it knows that there can only be 'so much' records per block.
Prior to this feature,when the bitmap indexes are created, it reads the approximate max_records_per_block from sys.tab$.spare1 (as shown in the demo) and the space may not be efficiently allocated.
This is the reason why the documentation kept talking about bitmap indexes when talking about minimize_records_per_block clause .
I was just showing the original poster how one might limit the number of rows per block using this feature.
Hope it clarifies
-Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
