Performance of select * vs select colname in oracle

Question

Why performance of select * from table is not as good as select col_1,col_2 from table? So far as I understand, it is the locating of the row that takes up time, not how many columns are returned.

Answer 1

Selecting unnecessary columns can cause query plan changes that have a massive impact on performance. For example, if there is an index on col_1, col2 but there are other columns in the table, the select * query has to do a full table scan while the select col_1, col_2 query can simply scan the index which is likely to be much smaller and, thus, much less expensive to query. If you start dealing with queries that involve more than one table or that involve queries against views, selecting subsets of columns can also sometimes change query plans by allowing Oracle to eliminate unnecessary joins or function evaluations. Now, to be fair, it's not particularly common that the query plan will change based on what columns are selected, but when it does, the change is often significant.

If you are issuing the SQL statement from an application outside the database, selecting additional columns forces Oracle to send additional data over the network so your application will spend more time waiting on network I/O to send over data that it is not interested in. That can be very inefficient particularly if your application ever gets deployed on a WAN.

Selecting unnecessary columns can also force Oracle to do additional I/O without changing the plan. If one of the columns in the table that you don't need is a LOB, for example, Oracle would have to do additional work to fetch that LOB data. If the data is stored in a chained block on disk but the columns you are interested in happen to be in the first row piece, Oracle doesn't have to fetch the additional row pieces for the query that specifies a subset of columns. The query that does a select *, on the other hand, has to fetch every row piece.

Of course, that is before considering the maintenance aspects. If you are writing an application outside of PL/SQL, doing a SELECT * means that either your code will break when someone adds a new column to the table in the future or that your application has to dynamically determine at runtime the set of columns that are being returned in order to accommodate the new column automatically. While that is certainly possible, it is likely to lead to code that is more complex and thus more difficult to debug and maintain. If you are writing PL/SQL and fetching the data into a %ROWTYPE variable, it can be perfectly reasonable to do a SELECT * in production code; in other languages, you're generally setting yourself up for a maintenance nightmare if you do a SELECT *.