Yeah I get it Frank, it's like DECODE (now implemented CASE WHEN ... END), NVL and its friends, the (+) for outer joins and so on, and all remain in the implementation as Oracle incorporated some features/functions in-part early on and the ANSI/ISO standards have since "caught up". Oracle hasn't for the most part had language feature breaking changes either, so all this junk DNA remains in their flagship product (well the removal of WM_CONCAT was a breaking change, but it was an undocumented feature as an interim approach to LISTAGG so doesn't really count).
Perhaps another way of looking at all of this is that Oracle were leading the way winning the awards at the beginning, but now a bit of a dinosaur and not winning the awards now. Younger spunky database implementations don't carry the baggage of these early RDBMS implementations. I include Postgres in this category, and its forks (eg. Netezza), that implement CTE's in a more standardised way.
Even under the bonnet Oracle can be a little sad too, as you know. I think it was Oracle 9 (maybe 9.2 before it worked) that Oracle implemented ANSI joins. When you look at a 10046 trace file however, you can see the ANSI SQL is rewritten back to the proprietary format, some sort of internal transpiler shoehorned in Oracle 9, and two semantically equivalent queries (one using ANSI joins and one using (+)), both hinted ..... well the ANSI code often loses the hints in the rewrite and the execution plan doesn't adhere to the "rule" (yeah yeah, hints are rules, we both understand this too).
On the subject of execution hints, and Postgres, well it's just not there and the (non) implementation defended wiki.postgresql.org/wiki/Optimizer... It will be interesting to see how this changes in the future, if it changes. If it changes, someone will be writing a nice blog article on it too. I will keep my eyes peeled :-)
Yes, ANSI joins introduced 20 years ago and still many bugs 🤷🏼♂️
About pg_hint_plan, at in YugabyteDB we have it by default. I see no reason not to allow easy troubleshooting / workaround.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Yeah I get it Frank, it's like DECODE (now implemented CASE WHEN ... END), NVL and its friends, the (+) for outer joins and so on, and all remain in the implementation as Oracle incorporated some features/functions in-part early on and the ANSI/ISO standards have since "caught up". Oracle hasn't for the most part had language feature breaking changes either, so all this junk DNA remains in their flagship product (well the removal of WM_CONCAT was a breaking change, but it was an undocumented feature as an interim approach to LISTAGG so doesn't really count).
Perhaps another way of looking at all of this is that Oracle were leading the way winning the awards at the beginning, but now a bit of a dinosaur and not winning the awards now. Younger spunky database implementations don't carry the baggage of these early RDBMS implementations. I include Postgres in this category, and its forks (eg. Netezza), that implement CTE's in a more standardised way.
Even under the bonnet Oracle can be a little sad too, as you know. I think it was Oracle 9 (maybe 9.2 before it worked) that Oracle implemented ANSI joins. When you look at a 10046 trace file however, you can see the ANSI SQL is rewritten back to the proprietary format, some sort of internal transpiler shoehorned in Oracle 9, and two semantically equivalent queries (one using ANSI joins and one using (+)), both hinted ..... well the ANSI code often loses the hints in the rewrite and the execution plan doesn't adhere to the "rule" (yeah yeah, hints are rules, we both understand this too).
On the subject of execution hints, and Postgres, well it's just not there and the (non) implementation defended wiki.postgresql.org/wiki/Optimizer... It will be interesting to see how this changes in the future, if it changes. If it changes, someone will be writing a nice blog article on it too. I will keep my eyes peeled :-)
Yes, ANSI joins introduced 20 years ago and still many bugs 🤷🏼♂️
About pg_hint_plan, at in YugabyteDB we have it by default. I see no reason not to allow easy troubleshooting / workaround.