DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: How to find the largest row in a table

  1. #1
    Join Date
    Sep 2001
    Posts
    112
    How can I find the largest row in a table.

    I'm going to try analyzing it right now and checking whats in the table. If you know this doesnt work do you have any ideas how i can find the largest row in a table in Kilobytes.

    Thanks

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    You should write a PL/SQL script for that. Just compute the (approximate) size of each row. This is a bit tricky :-)



  3. #3
    Join Date
    Sep 2001
    Posts
    112
    Is there an SQL Plus function that returns the size of a columns data in bytes?

  4. #4
    Join Date
    Oct 2001
    Posts
    83
    Hello,

    Try the function VSIZE.

    Hope this helps

    Regards

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I am trying to figure out why you may need the 'largest' row for?




  6. #6
    Join Date
    Sep 2001
    Posts
    112
    so if i say

    select max(vsize(column1) + vsize(column2)) from table.

    I'll get the size of the largest row .


    Thanks again julian for responding to my posts as normal .

    Your a good bloke, glad I never started that procedure.

  7. #7
    Join Date
    Sep 2001
    Posts
    112
    I'm going t orebuild the database and to eliminate chaining (the current block size is 2K).

    I am deciding wether to use 4 K or 8 K blocks.

    We got lots of disk space so I don't mind wasting a bit.

    Just wanna get the largest row in my DB and make sure that it fits in one block .

    Thx for your help.

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    for initial info for you:
    spool count_column_size.sql
    select 'select vsize('||column_name||') from '||table_name||';'
    from dba_tab_columns
    where table_name='YOURTABLE';
    spool off
    @@count_column_size.sql

    from here you can create a procedure that will
    fetch first all the column name,
    will create a sqlstatement like:
    sqlstatment:=
    'select vsize(column_name1)+vsize(column_name2)+vsize(column_name3) from YOURTABLE;';
    and execute that sqlstatement dynamicallly.
    execute immediate sqlstatment;

  9. #9
    Join Date
    Sep 2001
    Posts
    112
    HAHAHA, and of course if any of the columns are null Iget a null returned, so i'm going to convert nulls to zero

    Thx again guys

  10. #10
    Join Date
    Sep 2001
    Posts
    112
    Largest row appears to be 7K.

    8K blocks it is then .


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