New Rule: no SELECT * about plsql-and-sql-coding-guidelines HOT 13 CLOSED

A year ago I would have agreed. Last year I attended Sven Weller's talk named "Ketzerische Gedanken
zu SQL und PLSQL - glaub nicht alles was die Experten sagen". He showed that there are cases where select * is a good thing. Now I'm not convinced anymore that it is always a bad thing.

I tend to vote in favor of this rule, since a -- NOSONAR comment with some explanation why select * is used intentionally might be really a good thing.

from plsql-and-sql-coding-guidelines.

Would it be possible to check whether the SELECT * query is fetchet in a %ROWTYPE variable?

from plsql-and-sql-coding-guidelines.

This is possible as long as the record is defined in the same file. The scope of analysis in PL/SQL Cop is a file. It will lead to false positives when e.g. cursors are defined in the package spec and they are stored in a dedicated file. Hence, I would not make the rule too complicated.

from plsql-and-sql-coding-guidelines.

I would also agree that a select * is not a problem if you are using rowtypes.
Regarding the checking of rowtypes: If that doesnt work across files I'd rather leave that rule out.

from plsql-and-sql-coding-guidelines.

@S-Koell Thanks. If it would be possible to check could consider rowtypes, would that change your opinion?

from plsql-and-sql-coding-guidelines.

If it would be possible to check for a select * into other_pkg.g_rowtype_var ...
Sure.

So the rule would be:

• Always use rowtypes when using select *

But there a still edge cases which could slip through and would be bad in my opinion:

declare
  cursor cur is select a, b, c from table;
  row cur%rowtype;
begin
  select * 
  into row 
  from table;
end;
/

from plsql-and-sql-coding-guidelines.

The point is there are cases where select * is bad, e.g. when the code breaks when changing the underlying data structure or more data is processed then necessary. To formulate this as a rule has some value.

I'd like to handle the rules (to some degree) independent of the validators. The restrictions regarding the capabilities of the validator might change over time (e.g. when the validator can optionally access the data dictionary or can consider multiple files for a check which is mainly a memory/performance driven limitation) to minimize false positives or negatives.

from plsql-and-sql-coding-guidelines.

If I understand you correctly you'd do a rule like:

• Avoid Select * ... Note: Select * into rowtype is fine
  ?

from plsql-and-sql-coding-guidelines.

I'm for a rule named something like "Avoid using SELECT *". This emphasizes that the action should be prevented, but some exceptions may exist (see Avoid). Formulating the exceptions needs some thinking.

from plsql-and-sql-coding-guidelines.

I'm still unsure because I don't want to make a blanket statement like that. If you are reasonable and use it correctly then Select * can be the best solution.

What's with the following case, which I consider "good":

FOR loop_emp IN
  (SELECT *
    FROM
      employees
    WHERE salary>15000)

from plsql-and-sql-coding-guidelines.

This can be good or bad. If your code processes automatically a newly created column in employees (and you really want that) then I would consider it good. Otherwise I question if it is really necessary to read all columns. If not I'd consider the SELECT * as bad because you process unnecessary data and you rob the optimizer of the capability to find a better plan (e.g. scan of a suited index instead of a full scan or index access).

from plsql-and-sql-coding-guidelines.

I was not aware that the columns can change the plan. My arguments were mostly based on readable code / tableAPIs for table access.

I will retract from this discussion because I have limited knowledge.

from plsql-and-sql-coding-guidelines.

@thanks @S-Koell. Your contribution helps. There are some use cases where SELECT * is good. But the bad cases exist as well. It's not only performance and resource optimization. It's also about impact analysis, e.g. before changing the data model. You might want to choose an option with minimal impact on the existing code base (removal of a column, splitting content of a column in two columns, etc., etc.). It's much easier when you avoid all-columns-wildcards in your code. And since you mentioned TAPIs. This is a downside of TAPIs. They often process whole records only. Something to be aware of.

from plsql-and-sql-coding-guidelines.