Re: Calculating Average Row Length in Oracle 10g
Date: Tue, 9 Jun 2009 20:14:40 +0100
"Nag" <naga.challa_at_gmail.com> wrote in message
> We can get this by analyzing the tables in a schema and looking at the
> column avg_row_len.
> But, is there a sql so that I can calculate manually. In out
> environment, we gather statistics once and use them all the time.
Code like the following would give you roughly the correct answer for simple tables. Not sure how this should be altered to suit tables with LOB or LONG columns, or tables with more than 255 columns.
nvl(c1,0) + nvl(c2,0) + nvl(c3,0) + nvl(c4,0)
) / ct
sum(sys_op_opnsize(ID)) c1, sum(sys_op_opnsize(GRP)) c2, sum(sys_op_opnsize(SMALL_VC)) c3, sum(sys_op_opnsize(PADDING)) c4
The value 3 is "column count - 1";
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Tue Jun 09 2009 - 14:14:40 CDT