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.
A:
Why not varchar2(10)? Are you really using the entire string for every record?
Okay.. Even if i change it to Varchar(10), its fine. I was just wondering will there be any effect on the use of Oracle in-built fns like substr(), instr(), etc.. I dont see any impact. But wanted to confirm before giving a go-ahead.
2009-08-07 04:56:49
I'd be shocked if you could measure any impact at all. Just one call-out... The roll-forward is easy, the roll-back, not as much.
2009-08-07 05:03:49
Totally agree. ;-)Thanks a lot man. :-)
2009-08-07 05:11:57
+1
A:
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.
Laz
2009-08-07 05:22:55
A:
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).
ChrisCM
2009-08-07 09:05:27
A:
"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.