2

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).

molok
  • 1,491
  • 1
  • 14
  • 19
  • 8
    Did you ask your team leader why he considers it bad programming, because just saying "it's bad programming" without providing a reason is bad team leading, IMHO... – beny23 Feb 10 '12 at 09:53

5 Answers5

9

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.

user272735
  • 10,473
  • 9
  • 65
  • 96
Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
  • In PL/SQL context, why the compiler can't optimize the query by removing the unused columns ? – user272735 Feb 11 '12 at 07:08
  • 1
    @user272735 I guess it could but in some heavily optimized environments it is essential that the queries are under strict control (to the point where app developers are only allowed to use queries approved by the DB-tuning people) Adding a field access would mean a different query being sent, which is bad in this context. I guess that Oracle just assumes you know what you're doing when you type in your query. – Rafał Dowgird Feb 12 '12 at 05:57
5

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.
Rafał Dowgird
  • 43,216
  • 11
  • 77
  • 90
1

select * will pull in every single field from your table. If you need them all, then it's acceptable. However, more often than not, you won't need all of them, so why bother bringing in all that extra data? Instead select only the fields you care about.

Alexander R
  • 2,468
  • 24
  • 28
1

The select * construct is likely to have a performance hit bringing in more information than necessary. In addition the code is likely to generate maintenance problems. As the database changes, bringing in all the fields can have unlooked for effects.

EDIT Unlooked for effects are mainly those listed by Codo and Rob van Wijk -

  1. if the query depends on the order of columns;
  2. lack of clarity for later changes to the code
  3. non-use of indices.
  4. I was not aware of the column level dependency tracking mentioned by Rob, and had in mind that if a change was made to a column, it could invalidate the code (extra columns being retrieved causing overflows; or a query depending on the presence of a particular column.

These unlooked for effects are together the cause of the maintenance problems mentioned.

Chris Walton
  • 2,513
  • 3
  • 25
  • 39
  • What "unlooked for effects" are you thinking of? And what maintenance problem? Can you be more specific? – Codo Feb 10 '12 at 09:52
1

SELECT * is problematic if the query depends on the order or number of the columns, e.g.:

INSERT INTO X (A, B)
SELECT * FROM T
WHERE B.NR = 113

But it your case, it's not really problematic. It could be optimized if it's really pulling in much more data than required. But in most cases, it makes no difference.

Codo
  • 75,595
  • 17
  • 168
  • 206