>Which, regrettably, has not been my company's experience with Oracle 11g --- we haven't resorted to explicit hints, but we've restructured queries in other ways for order-of-magnitude improvements in performance.
He's not claiming that query planners are so good that you'll never have to restructure your queries. I've written many queries that seem perfectly reasonable, but once I start optimizing I can see I'm doing things completely backwards. It's just like almost everything else programmers do. If I dash off quick program, I don't expect the VM figure out how to make it perform perfectly. Why would I expect database software to be different?
> It's just like almost everything else programmers do. If I dash off quick program, I don't expect the VM figure out how to make it perform perfectly. Why would I expect database software to be different?
Except that SQL wasn't designed as a programming language. It was designed to be a declarative language that normals could use.
In a normal language, you have the freedom to express an algorithm in a billion different ways. You're expected to find the right way to write it. That SQL falls down here is a fundamental flaw in what was supposed to be it's greatest strength.
In what was supposed to be its greatest strength a long, long time ago. I don't know that anyone considers SQL to be anything other than a technical detail any more; certainly not to the extent of expecting normals to be able to get what they need out of a database by writing raw queries.
Yes, but that feature still hinders SQL today. If "usability by normals" was dropped as a design requirement, SQL would look a lot more like a normal programming language, and suck a lot less.
> It was designed to be a declarative language that normals could use.
We may be getting off the query hint topic, but normals can use SQL Server as you've described. They can write queries and get data out, they just can't expect those queries to perform optimally without expertise.
In normal language, you and I have the freedom to convey our thoughts a billion different ways. But only one guy wrote The Sun Also Rises (for example.) Hemingway found the right way to write it.
Oracle's preferred way to solve this kind of problem is to do a stored program outline. Oracle even has tools to help you identify a poorly performing query, have it do an extra thorough analysis (basically spend a minute or so looking for the best plan) and then store the outline for you.
He's not claiming that query planners are so good that you'll never have to restructure your queries. I've written many queries that seem perfectly reasonable, but once I start optimizing I can see I'm doing things completely backwards. It's just like almost everything else programmers do. If I dash off quick program, I don't expect the VM figure out how to make it perform perfectly. Why would I expect database software to be different?