Oracle SQL Tutorial 
Tip #7 - Name the Required Columns In SQL Queries

Tip #7 in this Oracle sql tutorial - don't write "select * from ..." in your application to indicate that all the columns should be selected, even if all the columns are needed, instead always name them.

There are (at least) three good reasons for this :-

  1. Network traffic is reduced. Most of the time you don't need all the columns, so why retrieve them all? This can have a significant impact on performance if the table has a large number of columns, or the table has a long/long raw column (both of which can be up to 2 Gigabytes in length) or LOBs (which can be up to 3 gigabytes in size). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.

  2. The code is easier to understand, so you can spend less time writing comments (which might be ignored) and more time writing code and maintenance will be faster and therefore cheaper as you won't have to read copious comments to uinderstand what is going on.

  3. It could save the need for changes in the future. Not only might columns be added to or removed from the table or view, but the order of the columns could well change (unlikely in a table but not impossible) - in which case using "SELECT *" at best would fetch the wrong data and at worst would fail with an Oracle error which might take a long while to understand.

Need help with your Oracle systems? Contact Smartsoft Computing for expert Oracle training and consultancy in the UK 

Return to Oracle SQL Tutorial part 1