Rows in a block ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Rows in a block ?

  1. #1
    Join Date
    Feb 2002
    Posts
    267
    Is it possible to find out how many rows are
    stored inside a block.

    Regards
    sonia

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    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

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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);


  5. #5
    Join Date
    Aug 2002
    Posts
    21
    jmodik
    will ya be kind enough to tell me how 2 find rowid of a block????

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Aug 2002
    Posts
    21
    I have till now not understood how to get no of rows in ablock can anybody give a soln;

  8. #8
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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
  •  



Click Here to Expand Forum to Full Width