I want to get the max length of data from all columns in a table. Is that
something Toad provides?
Chris
Attention:
This e-mail and any files transmitted with it from Hatch Mott MacDonald are
confidential and intended solely for use of the individual or entity to whom
they are addressed. If you have received this e-mail in error please immediately
notify the sender.
SB-Tables-Columns tab shows length for varchar2 columns. Is that what you mean?
No. I want to know that in a varchar2(50) field that the max data is really 27
characters.
Chris
I don’t know that Toad offers any way that’s easier than what Mr.
McAllister gave us.
(And now someone is going to tell us all how to define an action to do something
slick, and put it on your menu, and then we’ll all learn something new. Right?)
Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. B2SB - Saint Louis, MO - 63167
314-694-2592
No. I want to know that in a varchar2(50) field that the max data is really
27 characters
I’m not sure Toad has a built in function to do what you ask.
With a little up-front effort, you could build a small procedure that would do
that though. Make sure you consider the datatype as well. Blob columns and such
are somewhat different to find out the max size as compared with the standard
datatypes.
However – you probably want to avoid running such a procedure against a
production database. There could be a substantial hit to performance by querying
a lot of large tables due to the fact there won’t be indexes built for
this kind of purpose so you’re likely not to even make use of a single
index and they’ll all be full-table scans.
Additionally – you will want to be very careful using such a procedure
against tables with a high number of columns (test well). By its very nature,
such a process will likely be dynamic: execute immediate. Processing a dynamic
statement inside a loop has – in my experience – a pretty high hit
in performance due to the overhead involved in just handling the dynamic
aspects.
More experienced individuals have indicated the older dynamic code is more
flexible in that regard then “execute immediate” but I haven’t
the opportunity to really make use of that and find out. I prefer to find
another solution if I know the dynamic code will be looped more than a few
times.
Roger S.
Thanks for the advice. I was thinking about doing that. Typically this happens
very infrequently. Mostly when analyzing data for interfaces between systems.
Some DBA’s create large fields so they don’t mess with them in the
future.
Chris
You also can look at the stats table in the data dictionary – I know it
has average column size – it might also have max one ….
You can, of course, build a script to figure them all out. For example
select ‘select ‘’’||table_name||’.’||column_name||’’’,
max(length(’||column_name||’ )) from ‘||table_name||’;’
from user_tab_columns where data_type = ‘VARCHAR2’;
will build a bunch of statements like
select ‘WT_SWIMS_FIELDWORK.FIELD_DESC’, max(length(FIELD_DESC)) from
WT_SWIMS_FIELDWORK;
;
Run them as a script and you’ll get dozens or hundreds of grids like:
WT_SWIMS_FIELDWORK.DYN_FORM_CODE 18
But it’ll be a big performance hit. For LOBs, use
max(DBMS_LOB.getlength(lob_column_name)).
– jim
As one minor detail, you can get one grid with hundreds of rows instead of
hundreds of grids by making the queries
select ‘WT_SWIMS_FIELDWORK.FIELD_DESC’ , max(length( FIELD_DESC )) from
WT_SWIMS_FIELDWORK UNION ALL
…
making it a single query with hundreds of unions.
Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. B2SB - Saint Louis, MO - 63167
314-694-2592
'select ''' || table_name || '.' || column_name || ''', max(length(' ||
column_name || ')) from ' || table_name || ';'
Good Idea… combined with my suggestion, you’d only be dynamically
looping for each table instead of each table/column. That would certainly
decrease that part of the overhead.
Additionally, since it’d only be a single full table scan that would
decrease the number of full table scans. However, the trade off is increased
memory usage per query as every column would be needed rather than just the
specific column you need so there’s a hit to consider there.
Roger
'select ''' || table_name || '.' || column_name || ''', max(length(' ||
column_name || '))
Limitation on the combined dynamic idea: there is a maximum size text you can
pass through to execute immediate and a lot of columns combined could easily
exceed that size.
Roger S.
Evening all,
On 17/04/12 17:31, MCALLISTER, MICHAEL CTR AU Contractor AETC AETC/A3IS
wrote:
Select max(length(your_col_name)) as max_length
From your_table_name;
Not quite! The above only works for varchars. Char columns will always
return the defined length and not the length of the data.
SQL> create table fred(c char(100), v varchar2(100));
Table created.
SQL> insert into fred(c,v) values ('one','one');
1 row created.
SQL> insert into fred(c,v)
2 values ('four hundred and seven','four hundred and seven');
1 row created.
SQL> commit;
Commit complete.
SQL> select trim(c) as C, length(c) as len_c, length(v) as len_v
2 from fred;
C LEN_C LEN_V
Once you get a good query to achieve this, you may want to add it to the “Custom
Queries” feature in the SB Tables tab (last selection in R-menu).
Norm
Thanks for that clarification. To be honest, we don’t really use CHAR here
anymore, which is why I’ve either forgotten that important piece of
information, or (worse) never knew it. Out of interest, is there any reason
why one would want to use CHAR rather than VARCHAR?
Regards,
Mike McAllister
Principal Systems Engineer
Decypher
DSN: 487-3751
Commercial: (210) 652-3751
Cell: (512) 423-7447
Email: michael.mcallister.2.ctr.au@us.af.mil
smime.p7s (5.53 KB)
Morning Mike,
Thanks for that clarification. To be honest, we don't really use CHAR here
anymore, which is why I've either forgotten that important piece of
information, or (worse) never knew it.
Years ago, I wrote a "data obfuscation" routine with simply replaced
certain columns with random data generated by DBMS_RANDOM. All my CHAR
columns ended up being exactly the defined length! That's when I noticed
it. I ended up using VSIZE() to get the length of the TRIM()med CHAR
columns. Sort of stuck in my "cache" since then.
Out of interest, is there any reason
why one would want to use CHAR rather than VARCHAR?
I'm not sure. I use them occasionally myself. Just for internal space
saving. A VARCHAR2 has a hidden size setting. If the column is 100 long
and you store 40 in it, there's a counter internally that says "this is
how long the data is". Not a great reason, but if you have flag columns
There are probably other good reasons for using CHAR, but I can't think
of any off hand. Maybe Bert knows - he knows lots of good stuff!
--
Cheers,
Norm. [TeamT]
Morning All-
I thought VARCHAR2 was varying size based on a Run Length Encoding for the
column
where CHAR was always fixed length
to acquire column length the path of least resistance is to take the length from
USER_TAB_COLUMNS e.g.
SQL> select DATA_LENGTH from USER_TAB_COLUMNS where table_name=‘USER$’ AND
COLUMN_NAME=‘PASSWORD’;
Norm or Bert does this conform to your understanding?
Cheers,
Martin Gainty
GMT+5 (this week)
Morning Martin,
(Sorry, afternoon already!)
I thought VARCHAR2 was varying size based on a Run Length Encoding for
the column where CHAR was always fixed length
Varchar2 stores only the data it was given, so, inserting '1234H' and
'123 ' into a varchar results in 5 and 4 used characters respectively,
regardless of the column's definition.
Inserting the above two values into a CHAR(10) column would result in
exactly 10 characters being stored, each value iw space filled on the
right to the column width.
to acquire column length the path of least resistance is to take the
length from USER_TAB_COLUMNS e.g.
SQL> select DATA_LENGTH from USER_TAB_COLUMNS where table_name='USER$'
AND COLUMN_NAME='PASSWORD';
Nope, won't worl. DATA_LENGTH is how the column was defined,
VARCHAR2(10) and CHAR(15) will always give you 10 and 15. The OP was
after the size of the data in the columns, not the maximum size it can be.
Norm or Bert does this conform to your understanding?
See above.
--
Cheers,
Norm. [TeamT]
Hi
One of the big reasons that I know of to use CHAR instead of VARCHAR2 is row chaining. This is when a row gets moved from one block to another because there isd no longer room in the block for the row. How does this happen? If you use CHAR but when you insert the row the CHAR field does not have a value then that row is stored as a null (ie takes up no space). Later when you update the field, the length expands from null to whatever the value is for CHAR. This makes the row length increase and if there is not enough room in the block, the row gets moved. However when moving the row Oracle does not update indexes, etc and so a pointer is left in the orginal block. This means that a rerad for this row now consumes two blocks. So, as an example, if you are having to store addresses, then the zip code would be a good candidate for a CHAR field as most US addresses would inlude the zip code, the catch here would be on the initial insert put something into the field (a space of a 0) so that the room is already taken.
Cheers
Peter
CHAR is always stored fixed length – with padding. So variable X as
CHAR(10) with value ‘123’ would be stored as ‘123 ‘
whereas variable Y as VARCHAR2(10) would be stored as ‘123’.
Since an Oracle data block is either 4K or 8K having lots of CHAR columns can
quickly fill up blocks (potentially with empty space) – so fewer rows per
block mean lower buffer cache hit ratio and other things.
My personal “best practice” is to only use CHAR for relatively short
needs such as phone number or SSN where I need columns to have exactly N
positions. But others have different beliefs ……
Bert