Blinded by his already impressive results, the programmer reworking the query had totally overlooked all this. Yet this scenario is certainly not the worst you can encounter. Tuning for the Future Frequently, most people trying to optimize queries initially look for full scans. Keep your algorithms simple. Cleverly used, pin-pointed queries and intelligent programming usually achieve much better results.
Many critical processes can have most of the work done by the client rather than server side.
Oh, and I'm still waiting for integrity constraints on the Oracle data dictionary. Get rid of useless indexes. Ten years ago, most Oracle performance tuning was about adding indexes at the right places. Part of the blame probably belongs with foreign keys and with the well-known fact that when you don't index a foreign key, the referenced table is locked in exclusive mode when you update the referencing table. I've met a number of cases where referencing and referenced tables had to be updated concurrently, but this was certainly not the majority of cases.