Tim Hall

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 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-oracle-on-linux-64#disabling-transparent-hugepages

> 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:…

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…

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).

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#QUERIES-GROUPING-SETS

[2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16

[3]: https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets

[4]: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

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-23c [3] https://blogs.oracle.com/database/post/autonomous-json-database-under-the-covers-oson-format