5 ms·
For those still on Oracle, why? Is it regulations or executives demanding certified solutions? And if so then with so many bugs at what point does certificatio
by paulryanrogers 6y ago
For those still on Oracle, why? Is it regulations or executives demanding certified solutions?
And if so then with so many bugs at what point does certification become a negative signal?
- jdm2212 6y agoQuery hints are one reason. I'm not a fan of Oracle as a company, but for certain usage patterns you need query hints and Postgres doesn't support them.
- zozbot234 6y agoPostgreSQL provides a variety of options for tuning both the query planner's strategy and the per-table statistical analyses that are used as input to the query planner itself. These tuning methods are actually far more robust than the query hints that are commonly used in other databases, including Oracle.
- jdm2212 6y agoI'm not a DB expert by any means, but the DB experts at my job are pretty insistent that (given the way we deploy our software) it's not practical to do in Postgres what we can do with Oracle query hints.
- aeyes 6y agoYou usually have one query that you need to optimize but hundreds more that query the same data, therefore messing with the statistics of a table is hardly ever optimal. Usually it's some three-way join with n:n:n relations which breaks Postgres' neck. I know exactly where the query should start from, statistics can't possibly know because they have no knowledge about the data distribution in adjacent tables. I use CTE to "fix" this but the query would be faster without it. Another issue is that plans can just fall over and you have absolutely no tools available to see what the old plan was, unless you ran explain before and saved the plan. You also have no way to force the old plan while you analyze the issue. This has caused more production outages than I'm willing to admit. With Oracle it would take me one minute to see what the issue is, and I could make the database use the old plan without changing a single line of code.
- devit 6y agoI don't think it's likely that you ever really need query hints. Even if updating analyses, creating indices, changing the query, etc. fails, you can always either use CTEs or temporary tables, or use a stored procedure that manually iterates over results to implement whatever strategy is desired. It might be more time consuming than having query hints, although this is compensated by the fact that almost always queries just work after creating appropriate indices.
- jdm2212 6y ago> It might be more time consuming than having query hints The queries in question can't be allowed to get any slower than they already are. They bottleneck certain critical uses.
- paulryanrogers 6y agoDo query hints lock in the implementation though? I imagine that DB upgrades could have different performance characteristics both with and without hints.
- necovek 6y agoI believe the GP post was referring to restructuring your query so you make Postgres hit better indices (eg. when you know the distribution of data but can't easily apply a partial index). A common way to do that in Postgres is to use subselects for a criteria, and the join in the outer query. Coming up with such queries is "more time consuming". And yes, this approach is fragile even in Postgres (version or data changes might affect the performance, or you might be stuck with a worse query when query planner becomes smarter), so I imagine query hints in Oracle have the same problem.
- devit 6y agoI meant time consuming in developer time, sorry.
- gurjeet 6y agoEnterpriseDB's Advanced Server (based on Postgres) has the query optimizer hints feature [1]. Disclosure: I implemented the Optimizer Query Hints feature at EnterpriseDB. [1]: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/11/Database_Compatibility_for_Oracle_Developers_Guide.1.038.html https://www.enterprisedb.com/edb-docs/d/edb-postgres-advance...
- zer 6y agoMy clients — oncological institutions — license Oracle and so they prefer my solutions to use it as well. Really simple, and sad. As for why they use it... as far as I know it‘s the old tale: historical reasons. Classical lock-in.
- aeyes 6y ago- query hints - planner hints - better statistics (including ways to manually create statistics) - no vacuum - automatic planner adjustment when true cost was off - better planning for prepared statements - active-active clustering with RAC - vendor support for running on SAN including snapshots - usable query analysis tools provided by the vendor (pg_stat_statements alone doesn't cut it) - datapump much faster than pg_dump - better connection management - out of the box backup tooling edit: I haven't used Oracle in 4 years, all of that was available to me back then.
- Someone1234 6y agoSeveral of these are "features" exist to fix broken parts of Oracle (like query/planner hints, planner adjustment, etc). Officially (per Oracle) the planner should be good enough so you never need to use a hint, but realistically all of these are "features" because the built-in systems aren't good. So, granted, other databases don't have Oracle's hacks to fix their broken stuff but I'm not sure why that is an argument FOR using Oracle rather than AGAINST. Hints are fragile hacks.
- vbezhenar 6y agoSo what do you do when you encounter bug in Postgres planner? File a bug and wait for months or years until fix is released? Hack postgres source and fork it? Using hints sounds like a more sane solution. Nobody's perfect. Being able to work around is better than not being able to.
- leandrod 6y agoRAC and hints are antifeatures.
- modoc 6y agoWhat DB has a better active-active HA cluster option than RAC? I love me some Postgres and MariaDB but I've never seen anything come close to RAC for making high volume queries against active-active just work.
- 6y ago
- F_J_H 6y agoOracle Application Express (APEX). Comes as a no-cost option with the database, making Oracle the only complete, end-to-end, data management system I know of, where you can build and deploy rich web apps, "out-of-the-box", without installing or integrating anything else. A friend of mine (experienced developer) built a web app with React, (CRUD and Dashboard) and it took him the better part of a day. Built the same thing with Apex in about 15 minutes. That's a big reason we use it. Simply can't stand-up data centric, business focused web apps with as quickly with anything else. YRMV