Hi all,
Does anyone have a script to give the average column size of each column in a table. I am trying to write one but I am not successful. It gives me the average length of the column name, not the average size of the values.
Thanks.
Printable View
Hi all,
Does anyone have a script to give the average column size of each column in a table. I am trying to write one but I am not successful. It gives me the average length of the column name, not the average size of the values.
Thanks.
well, it's quite easy, just look for data_type and data_length in dba_tab_columns ...
Yeah,
But what i'm trying to do is a script where I just plug in the name of the table and the script will get all the columns for that table. Then it will get the average length of all rows for each column.
Ex: ID Name Address
1 Jim 123 Gordon
2 Alan 25 Jones
3 Jane 4 Golden St.
It should give me the following result:
ID 1
Name 3.667
Address 10
I can send you a copy of what I have now if you want to play with it.
use avg(vsize(column name))
That is what i'm (was) using.
It works fine on its own, but in my procedure,
the column name comes from a cursor and instead of giving me the avg of the data in the column, it gives the avg of the column name size.
Anyhow, I found an easier way.
I didn't notice that dba_tab_columns has an avg_col_length
column. I just have to get the column name, and the avr_col_length for the table name I give to my procedure.
Works fine now.
Denis