Performance of table with 29 Oracle varchar2 columns

Odd question for an odd situation i am in.

Is there any noticable performance difference when selecting all the columns of a table which has 29 varchar(255) columns?

Would performance improve if they were varchar(50) instead?

Most data in the columns are infact less th开发者_运维百科an 30 chars if that matters.

It depends.

If you are simply querying the data in a PL/SQL block where there is no network traffic, it shouldn't matter assuming the data is actually the same size in both cases. You may use marginally more PGA memory if you fetch the data into PL/SQL variables but it's very unlikely that would make any appreciable difference in performance.

If you are fetching the data across the network, however, it could well make a difference. Many drivers would end up allocating space based on the potential size of the result set (i.e. 255 bytes or 255 characters depending on the character set, NLS_LENGTH_SEMANTICS, and other bits of globalization fun) which could mean that you'd be allocating 5 times more RAM on the client machine than necessary (where "client machine" here may refer to the middle tier of a three-tier application). That could, in turn, end up impacting performance.

Based on your username, I would tend to suspect that you're using Java and JDBC. If you are using a Type 4 JDBC driver, I'd tend to wager that you'd only be allocating space based on how big the data actually is. If you're using a JDBC-ODBC bridge, on the other hand, I'd tend to wager that the ODBC driver is allocating space based on the maximum size of the column. This would obviously depend on the driver you're using-- this is my personal educated guess not based on actually profiling the memory use of various drivers.

29 columns times 30 characters is less than 1kB per row. There are plenty of situations where you could end up with 1kB rows in a database and not run into performance problems.

Since performance is base on the amount of data stored in the varchar column, rather than the maximum size of the column, performance will not improve by shortening the column (unless you truncate data while you do so).