G-3145
🆓Error
Avoid using SELECT * directly from a table or view.
Reason
Use of SELECT when querying a table or view makes it impossible for the optimizer to take into account which columns will actually be used by the application, potentially leading to sub-optimal execution plans (for example full scanning the table where a full scan of an index might have sufficed.) Also SELECT possibly can break your code in the future in case of changes to the table structure (for example new or invisible columns.)
Exceptions to the rule can be when querying an inline view (where the SELECT is just to avoid repeating same columns as inside the inline view), or when fetching into records defined as MYTABLE%ROWTYPE for the purpose of processing all columns of the record.
Example
Non-Compliant Example
begin
<<raise_salary>>
for r_employee in (
select *
from employees
)
loop
employee_api.calculate_raise_by_seniority(
id_in => r_employee.id
,salary_in => r_employee.salary
,hiredate_in => r_employee.hiredate
);
end loop raise_salary;
end;
/Issues
| Line | Column | Message |
|---|---|---|
| 4 | 14 | Using SELECT * without subquery. |
Compliant Solution - ★★★★★
begin
<<raise_salary>>
for r_employee in (
select id,salary,hiredate
from employees
)
loop
employee_api.calculate_raise_by_seniority(
id_in => r_employee.id
,salary_in => r_employee.salary
,hiredate_in => r_employee.hiredate
);
end loop raise_salary;
end;
/References
- same as plsql:SelectStarCheck
- same as plsqlopen:SelectAllColumns
- same as Trivadis G-3145
- same as SQLFluff ambiguous.column_count