Skip to Main Content
  • Questions

  • Modifying a column from char(2) to char(5) takes hours. Possible reasons?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jason.

Asked: July 07, 2004 - 8:23 pm UTC

Last updated: July 08, 2004 - 11:55 am UTC

Version: 8.1.7.

Viewed 1000+ times

You Asked

I'm modifying a column from char(2) to char(5) and it is taking several hours before completing for a particular table. There are also other tables in the database with state columns that have >=amount of data(some with 7 times the amount of data), that are altered in a few seconds.

-- This table includes about 25 columns with about 6,000 rows of data
-- (column state is a list is a list of states that can be null.)
create table t ( a number, STATE char(2), c varchar(30), etc... );

insert into t values ( 1, CA, test1);
insert into t values ( 2, null, test2);
insert into t values ( 3, WA, test3);


alter table t
modify STATE CHAR(5)
-- OK. [21465.9840 sec]

Moving data to a temp table, then truncating t, modify the state column and moving the data back, all takes < 1 minute.

Can you point me toward a few things to check or explain why?

Thanks in advance,
Jason




and Tom said...

that is because a char(5) *must* be blank padded.

that is the same as:

alter table to increase the size to 5
update table set state = rpad(state,5)


so.... if state is indexed -- slow.
if lots of rows -- slow.
if increasing the size of state causes the blocks to overflow with data -- lots of migrated rows -- slow.


I *hate* the char type, I suggest *never* consider using it for anything, not even a char(1).

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent Information

Muhammad Riaz Shahid, July 08, 2004 - 10:59 am UTC

<Quote>
I *hate* the char type, I suggest *never* consider using it for anything, not even a char(1).
</Quote>

So if i have to define a column which , i know, will take exactly one character (for example, Sex) and is not null, then would you still suggest to use varchar2(1) over char(1) ??? (or even number(1) i.e; 1=male, 2=female).

Also i heard that strings searching is faster than number searching (non-indexed)...whats your opinion about that ?
If you are given a choice to select the datatype of a column of length 1, among char(1),varchar2(1) and number(1), which one you would select ???

Regards

Tom Kyte
July 08, 2004 - 11:55 am UTC

yes, that is what I suggest.

why? because it just lets you pretend that CHAR doesn't even exist. gets you in the habit of never typing "char", but always typing "varchar2"

a char(1) and a varchar2(1) that is not null are *identical*


the bit about numbers is non sensible. first of all, it would be the worst thing in the world to store a number in a string. (well, on the list of the worst things in the world). If you are interested in the long winded "why" -- i wrote about it in "Effective Oracle by Design" -- it mucks up the data, mucks up the optimizer, no good ever comes from it.

A number is a varying string 'string'. It will take less bytes to store a number in a number than to store the number in a string. It will take more cpu cycles to search for the string '100010001001' than it would for the same number. In fact, with character sets and such - searching strings could be much more expensive, whereas a number is always done "binary".


as for your last question -- i would limit it to varchar2(1) and number(1) and the domain of values allowed would dictate (DICTATE, not suggest) the correct datatype.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.