I think using SELECT in all cases, enforces the idea of just requiring what you need which just gives the performance benefit in other cases even if they occur less.

Even without the optimisation bit, personally I prefer when code is explicit about what it is doing, like the data it is operating on/with. While omitting select makes the code succinct, having it there gives an idea at a glance about what it is operating with.

The real landmine underneath all this is: what is going to be updated when.

In all probability (we did say Oracle, which means enterprise), this application code is going to live forever, and never receive dev attention once it reaches functional status (unless something breaks).

Consequently, encoding something like "SELECT *" on the app side means that will never be updated, or even noticed, which is the same as saying it will be impossible to subsequently optimize.

Because you can't optimize specifics (these columns) from the DB side, when the only information an application gives you is generic (requests all columns).

Multiple this by 100 apps hitting the same core databases, and you start to see why it's a big deal, from an organization-lifecycle perspective (over 20 years+).

There is no optimization to which you cannot apply that logic. You're claiming "always optimize everything, because we can never change code". Even in enterprise, this is nonsense.

This is classic premature optimization. You don't know that overselecting will have any significant performance impact, and barring a few obvious cases (LOBs) it's quite unlikely to matter. There's a material cost in complexity to add situation-specific SQL vs the aforementioned:

    models = Model.where(column_name: column_val)

Most databases make it pretty easy to find out what queries are consuming resources. Spend your time optimizing those.

There are tons of optimizations that logic can't be applied to!

For starters, any software developed in any company with the ability to deliver updates in a timely manner.