CODEWITHSUNDEEP
databaseoracle-databaseperformance
Termi
Termi

Reputation:

Impact for increasing the column length of an existing table

The datatype of a column in a existing table is of type Char(4). Now, is there any impact while selecting that row if I increase the column length to say 10.

Upvotes: 1

Views: 6629

Answers (4)

Erwin Smout
Erwin Smout

Reputation:

"While selecting that row"

In those cases, the only impact you might be suffering is unexpected truncation. I recall once asking to the DB2 specialist in my company what the indicator value "-2" meant. He didn't know.

But if you got any query anywhere that involves "LENGTH(yourcolumnname)", and it turns out that your code relies on that LENGTH() invocation never to return something > 4, then of course you're in trouble.

Upvotes: 0

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

In a vacuum there should be no real impact of expanding the the VARCHAR2 datatype on a column.

Unlike CHAR which are right-padded with blanks, a larger VARCHAR2 size will not noticeably change any existing records.

However, as others have mentioned, what we can not predict is what happens in any layer you have on top of the database which may rely on the column being a particular size but I'm sure you could answer that better than us.

Be careful though, it's not so easy making a VARCHAR2 column smaller again. Even if your data is within the new limitations Oracle will insist on the column being empty. Your best bet is to export the rows and rebuild the table or shuffle columns (create new column, migrate values, drop old column).

Upvotes: 0

Laz
Laz

Reputation: 3538

I believe when you increase a char field, extra spaces are added to fill in those not already filled, in some databases if not all.

This may impact some programs that were not expecting this behaviour, and may cause some output issues if extra whitespace is not being trimmed.

I assume substring-like functions (and those depending on string length) may also be affected as the length of your string has now changed.

Upvotes: 1

scott
scott

Reputation:

Why not varchar2(10)? Are you really using the entire string for every record?

Upvotes: 0

Related Questions