Tim Hall

It's worth noting that other databases have features designed specifically to address this use case. Oracle has lock free reservations on numeric columns: https://oracle-base.com/articles/23/lock-free-reservations-23

Just looking at the database release (not the company behind it), I think this is a great release. JSON Duality Views [1] are a phenomenal feature to bridge the gap between relational and document data without having any downsides. In the same fashion, Property Graphs [2] allow traversing relational data in graph fashion. With these and the new vector features, their multimodel approach is paying off, in my opinion. Their JSON storage format (OSON [3]) is also very fast and efficient. [1] https://oracle-base.com/articles/23c/json-relational-duality-views-23c [2] https://oracle-base.com/articles/23c/sql-property-graphs-and-sql-pgq-2… [3] https://blogs.oracle.com/database/post/autonomous-json-database-under-…

Unfortunately every index takes time to access; it's like dereferencing a pointer. You also usually want the hottest indexes to reside in RAM. Making more indexes compete for RAM pages may and will have implications for other queries, possibly more important. I agree that often machine resources are plentiful, and human resources are scarce, so managing indexes automatically makes sense. It's even implemented, e.g. in Oracle as a complete management tool [1], or in Postgres as a discovery and recommendation tool [2]. [1]: https://oracle-base.com/articles/19c/automatic-indexing-19c [2]: https://www.percona.com/blog/2019/07/22/automatic-index-recommendation…

this is a recommended setting even on Oracle [1] [2] and Couchbase [3] the doc recommends adding "transparent_hugepage=never" to the kernel boot line in the "/etc/grub.conf" file. [1] http://oracle-base.com/articles/linux/configuring-huge-pages-for-oracl… [2] https://support.oracle.com/epmos/faces/DocContentDisplay?id=1557478.1 [3] http://blog.couchbase.com/often-overlooked-linux-os-tweaks

It does, though are some restrictions in regards on which versions it can run on, unless you want to enable features that are not officially supported: https://oracle-base.com/articles/19c/automatic-indexing-19c My issue is that you cannot (easily) delete the automatically generated indices, should you want to do that for whatever reason. Of course, Oracle will try to only enable the indices that will lead to actual performance gains, but when it comes to removing the automatically created ones altogether, the best that you can do is: DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_RETENTION_FOR_AUTO', '1'); Consider that you have a few different environments, all of the same database schema with a few hundred tables in it. You might want to take one of those environments (a development environment) and enable the feature there, have a bunch of indices be automatically created, take the DDL for those and turn them into migrations that can be run against other environments (any number of testing environments, eventually production) either manually or through some automated SQL migration solution. The problem is that you can't easily get rid of all the automatically generated ones so that you could test this migration against the same environment (basically create those same indices through your own SQL).

This was a trend in 2002. PSP (PLSQL Server Pages), just like ASP and PHP. https://oracle-base.com/articles/9i/plsql-server-pages-9i Many large Banks (including Bank of New York where I worked in) and ERPs have stored procedures as their life and blood, have invested and built huge tooling over it – namespacing, dependency tracing of who's using what, monitoring, etc. It's excellent at having a tightly integrated, closed system where the Database is the source for authorization, module ownership, etc. But there were reasons why it was not successful: * Severe vendor restrictions. e.g. For a long time, Oracle could not do distributed compile-time PL/SQL checks. If you changed a function definition, other procedures that depended on it would break. But if you change a procedure's definition, it wouldn't tell you anything until you deploy it at runtime and something somewhere will fail, or recompile every other procedure. I don't know if it's fixed yet. * Testing, or the severe lack of tooling that we have taken for granted – code coverage, easy local machine unit tests, etc * Lack of modules (availability of an ecosystem or community of modules to solve recurring problems), and the subsequent dependency management and versioning overhead that comes with it * Debugging, i.e. putting breakpoints, remote debugging, logging, good exception handling, etc * Change management, graceful rollouts, rollbacks, etc. There is no so-called "Router" for stored procedures, they're all…

I am a data architect in my day job. Within the realm of data management, I'd say "metadata management" [1] is the general category this fits within. I would say, yes this idea is known/very common, as data architecture is as much about the descriptive language we use as anything. I mean, "business glossaries", taxonomy, even just naming conventions [2] in coding, these are all related. If you build enough databases/tables or even code yourself, you inevitably come across the "how to name things" problem [3]. If all you have to sort on for the known meaning of a thing (column, table, file, etc.) is a single string value, then encoding meaning into it is quite common. This way, a sort creates a kind of "grouping". Many database vendors follow standard naming conventions - such as Oracle, for example [4]. It is considered a best practice when designing/building the metadata for a large system, to establish a naming convention. Among other things, it makes finding things easier, as well as all the potential for automation. You get all kinds of variations on this, such as, should the "ID_" come as a prefix or a suffix (i.e. "_ID"). One's initial thought is to use it as a prefix so all the related types group together, but then that becomes much more difficult if you want to sort items by their functional area (e.g. DRIVER_ID, DRIVER_IND, etc.). One other place you see something similar is in "smart numbers" which is an eternal argument - should I use a "dumb…

I don't think THP is a good example, because its not considered very stable actually. https://oracle-base.com/articles/linux/configuring-huge-pages-for-orac… > That sounds like a good idea, but unfortunately Transparent HugePages don't play well with Oracle databases and are associated with node reboots in RAC installations and performance problems on both single instance and RAC installations. As a result Oracle recommends disabling Transparent HugePages on all servers running Oracle databases, as described in this MOS note. ------------- Anyway, the very point of huge-pages is to gain ~3% to 5% faster program speed by keeping more of the memory-management units of the OS inside of the TLB-cache of the CPU. Running a background thread to defragment the physical memory system of your computer WHILE your critical tasks are running... very most likely kills the performance benefit you were trying to get. Linux's raw Hugepages work fine, but have their own set of drawbacks as I described in the previous post. For the moment, it seems like a better idea to use "normal" Linux Huge Pages than to use the THP. Indeed, I'm seeing reports that some people get 10s of millisecond pauses when their application does a 32-byte malloc with THP enabled (Linux Kernel decides to garbage collect + defragment + turn your pages into a Huge Page). A lot of programs don't expect a tiny malloc to cause such a major performance hit randomly (ie:…

Also, latest Oracle version (23ai) has added "concurrent on-commit fast refresh" functionality, where concurrent transactions' changes are rolled up to the MV concurrently (previously these refreshes were serialized). https://oracle-base.com/articles/23/materialized-view-concurrent-refre… From the article: "Concurrent refreshes will only happen if there are no overlaps in the materialized view rows updated by each session."

Functions in WITH clause really are a blessing. Oracle DB supported these for a decade now and that was a gamechanger for me. https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#fu…

Last time I touched Oracle is going on a couple of decades ago. But I did discover at the time a readline wrapper which provided at least a modicum of functionality. Looks as if that's Still A Thing: <https://oracle-base.com/articles/linux/rlwrap>

https://oracle-base.com/articles/12c/multithreaded-model-using-threade… Probably still requires the parameter to be set.

Or using an Oracle style AS OF query: https://oracle-base.com/articles/10g/flashback-query-10g

I always found it ironic that you get most of this for free if you design your sql updates and save/query the transaction log and/or history. A lot of relational dbs have functionality for that Being able to elegantly and efficiently do "AS OF" queries is still a hard problem, I don't see that KTables really solve it. Hate to say it but Oracle's solution to this is still unmatched. https://oracle-base.com/articles/10g/flashback-query-10g#flashback-que…

DB2 supports clustering, and Oracle has a similar concept: http://www.oracle-base.com/articles/8i/IndexOrganizedTables.php You are definitely right about each RDBMS having their own quirks though! Solid advice: know what you are working with and its nuances.

I had never heard of GROUP BY CUBE either! It looks like it's part of a family of special GROUP BY operators—GROUPING SETS, CUBE, and ROLLUP—that basically issue the same query multiple times with different GROUP BY expressions and UNION the results together. Using GROUP BY CUBE(a, b, c, ...) creates GROUP BY expressions for every element in the power set of {a, b, c, ...}, so GROUP BY CUBE(a, b) does separate GROUP BYs for (a, b), (a), (b) and (). It's like SQL's version of a pivot table, returning aggregations of data filtered along multiple dimensions, and then also the aggregations of those aggregations. It seems like it's well supported by Postgres [1], SQL Server [2] and Oracle [3], but MySQL only has partial support for ROLLUP with a different syntax [4]. [1]: https://www.postgresql.org/docs/current/queries-table-expressions.html… [2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-tra… [3]: https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-a… [4]: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html