G-3145: Avoid using SELECT * directly from a table or view.

Major

Efficiency, Maintainability, Reliability, Testability

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 (bad)

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;
/

Example (good)

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;
/