Oracle, with all the baggage attached to the namesake company, is a remarkable database.
I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example:
> I was writing Oracle queries that could make your head spin
Absolutely, and some of the things Tom Kyte (Oracle's resident DB performance guru) could do with Oracle were spectacular.
As you say, its a shame about the Oracle baggage, and in particular the steep price tag. Otherwise I'm certain it would be far more widely deployed as database.
Totally untrue...but adding hints makes your queries dependent on current performance.
Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system. Even databases are general-purpose machines. That's the whole point of adding indexes, etc.
I mean, has there ever been a database that auto-adds indexes based on profiler/optimizer feedback? (To answer my own question, apparently Azure SQL does).
> (To answer my own question, apparently Azure SQL does)
Which is fun when you add a future schema migration that drops a column, test in locally and in some test cases, and then see it bomb in production because the column has an Index that you weren't aware of. (That's an easy thing to solve though, but definitely one of those cases where "The database is messing with its schema on its own" does do funny things)
Generally this seems like a nice balance to me - watch real queries, look for expensive plans and sniff out likely missing indexes based on the data distribution, but give the DBA final say on whether it's a good idea
> adding hints makes your queries dependent on current performance
I don't understand this, can you explain.
> Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system
That's inaccurate. As a DBA I can understand the system better at higher level, but the database has statistics which gives it typically better understanding of the data distribution at a lower level. Indeed I could get that information and feed it into the query plan via hints, but that's going to be an enormous amount of my time, and I would have to do it every time the query is run whereas the database can keep an eye on the statistics as it varies and rebuild the query over time.
Equally, if an index is added you can expect the database to start using it immediately without adding hints. Etc
I am a fairly(?) skilled DBA who has a reasonable idea of what goes on underneath the hood, I do have some idea what I'm talking about.
The postgress team is generally opposed to hints. And their reasoning sort of makes sense.
When do you need a hint? why do you need a hint? If you can answer those questions wouldn't it be better to codify those answers into the query planner than a one off hint?
Which nicely sidesteps how hard it actually would be to first, understand the query planner to the degree needed to change it and second, the amount of effort it would take transform a one off hint into a general purpose optimization engine.
I did say 'typically'! Yes, agreed, but it should rarely need help. Also hints are oh so often jammed in by newbies. I strongly get the impression use of hints is inversely correlated with experience/knowledge.
I very rarely used hints in my more database'y days - but every so often, they were needed, and often to tell the database something that seemed screamingly obvious to me.
I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience.
We are in strong agreement, and the phrasing and measure of your post made it pretty obvious you are knowledgeable (another inverse correlation: absolute opinions launched with violence also seemed to be the realm of newbies; and yours was eminently not).
> I very rarely used hints in my more database'y days
evidence thereof!
> I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience.
It's been very, very much my experience. I worked in one place where nolock hints were applied everywhere, without them realising that READ UNCOMMITTED trans iso even existed, so had to show them that, and show them it could be specified at the application layer (not in the SQL). In another recent case I came across their use and I'm damn sure the person who used it didn't actually know what it did. Yeah, I've seen far too much hinting in my career.
Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?
You've never run into a query planner that suddenly changes its mind once your tables grow.
Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.
This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.
No one is saying that. Your original comment can be read like you're saying using hints is a sign of failure of the person writing the query which is probably why the downvotes. Maybe you meant a sign of failure for the optimizer, which is also debatable given they really cannot be perfect. An optimizer will quickly run into NP-hard problems like join ordering - which is just one small reason among many that they won't always find the fastest query plan.
I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example:
https://renenyffenegger.ch/notes/development/databases/Oracl...