-
Is it possible to find out how many rows are
stored inside a block.
Regards
sonia
-
Some time ago, I think was Jurij who post this formula:
For an object:
header_block~=120 bytes
space_capacity_for_a_block=(block_size-header_block)(1-pctfree/100)
rows_in_a_block=space_capacity_for_a_block/estimation_of_the_row_lengh
Hope that helps
Angel
-
Determine which table this block belongs to by querying dba_extents. Then isue:
select count(*) from that_table where rowid = my_rowid;
my_rowid is the rowid of the block you are interested in.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
In 9i, you can use :
ROWID_BLOCK_NUMBER Function
This function returns the database block number for the input ROWID.
Syntax
DBMS_ROWID.ROWID_BLOCK_NUMBER (
row_id IN ROWID)
RETURN NUMBER;
Pragmas
pragma RESTRICT_REFERENCES(rowid_block_number,WNDS,RNDS,WNPS,RNPS);
Parameters
Table 62-7 ROWID_BLOCK_NUMBER Function Parameters
Parameter Description
row_id
ROWID to be interpreted.
Example
The example SQL statement selects the block number from a ROWID and inserts it into another table:
INSERT INTO T2 (SELECT dbms_rowid.rowid_block_number(ROWID)
FROM some_table
WHERE key_value = 42);
-
jmodik
will ya be kind enough to tell me how 2 find rowid of a block????
-
Originally posted by sm8728
jmodik
will ya be kind enough to tell me how 2 find rowid of a block????
Ignore my previous post, my brains were not functioning at that time - it was before my first coup of coffe.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I have till now not understood how to get no of rows in ablock can anybody give a soln;
-
Something like this ?
select dbms_rowid.ROWID_BLOCK_NUMBER(ROWID), count(*) NumberOfRowsInThisBlock
from Mytable
group by dbms_rowid.ROWID_BLOCK_NUMBER(ROWID)
But, this will not take into account migrated/chained rows.
Gert
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|