Is using "select *" for a cursor in PL/SQL considered bad programming?

Question

Often I use cursors in this way:

for rec in (select * from MY_TABLE where MY_COND = ITION) loop
    if rec.FIELD1 = 'something' then
        do_something();
    end if;

    if rec.FIELD2 <> 'somethingelse' then
        blabla();
    end if;
end loop;

My team leader told me not to use select * because it is bad programming, but I don't understand why (in this context).

Answer 1

Using select * in your code is what I would call lazy programming, with several nasty side effects. How much you experience those side effects, will differ, but it's never positive.

I'll use some of the points already mentioned in other answers, but feel free to edit my answer and add some more negative points about using select *.

1. You are shipping more data from the SQL engine to your code than necessary, which has a negative effect on performance.

2. The information you get back needs to be placed in variables (a record variable for example). This will take more PGA memory than necessary.

3. By using select * you will never use an index alone to retrieve the wanted information, you'll always have to visit the table as well (provided no index exists which holds all columns of the table). Again, with a negative effect on performance.

4. Less clear for people maintaining your code what your intention is. They need to delve into the code to spot all occurrences of your record variable to know what is being retrieved.

5. You will not use SQL functions to perform calculations, but always rely on PL/SQL or Java calculations. You are possibly missing out on some great SQL improvements like analytic functions, model clause, recursive subquery factoring and the like.

6. From Oracle11 onwards, dependencies are being tracked on column level, meaning that when you use select *, your code is being marked in the data dictionary as "dependent on all columns" of that table. Your procedure will be invalidated when something happens to one of those columns. So using select * means your code will be invalidated more often than necessary.

Again, feel free to add your own points.

Answer 2

Selecting more fields than you need has several drawbacks:

• Less clear - select c1, c2 shows at a glance which columns are pulled, without the need to pore over the code.
• ...also less clear for the people responsible for administration/tuning of the DB - they might only see the queries in logs, better not force them to analyze the code that generated the queries.
• prevents some query optimizations - select c2 from t where c2<=5 when you have index on c2 has a chance to pull the c2 value from the index itself, without fetching the records. The select * ... makes this impossible.