Q

In Oracle, does the number of columns in a table affect the performance during SELECT/INSERT/UPDATE?

An Oracle user asks if the number of columns in a table affect the performance during SELECT/INSERT/UPDATE.

Does the number of columns in a table affect the performance during SELECT/INSERT/UPDATE? If so, is there any reason?
For SELECT statements, the number of columns will not affect the performance. The reason is that Oracle has to read data at the block level. The entire row, or multiple rows if the block contains more than one, will be read into the buffer cache. Oracle will first determine which rows satisfy your query. At this point, Oracle removes the unwanted rows from the result set before it passes the results back to the user. So whether you have one column or all columns from a table in a SELECT statement, it will not affect the statement's performance.

Similarly for an UPDATE statement. Oracle must read the entire block into memory and then modify the row's contents. If the row increases in length due to the UPDATE statement and the row has to be migrated or chained, the statement will take longer to process. But this is true no matter how many columns are modified. Finally, it will be quicker to update all columns at once than to break the statement into multiple UPDATE statemen...

ts.

The INSERT statement can take longer to complete the more data you insert. This has to do with the entire row length rather than the number of columns involved. But you shouldn't see any noticeable difference involved. If your INSERT statement is taking a long time, there are probably other factors at work.

This was first published in May 2008

Dig deeper on Oracle database administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close