Where database blog posts get flame-broiled to perfection
Alright, team, gather 'round the lukewarm coffee pot. Another "game-changing" feature has dropped from on high, promising to solve the problems we created with the last game-changing feature. This time, Oracle is graciously emulating Mongo, which is like your dad trying to use TikTok. Let's take a look at this brave new world, shall we? I’ve prepared a few notes.
First, we have the effortless five-step Docker incantation to just get started. My favorite is the until grep... do sleep 1 loop. Nothing instills confidence like a startup script that has to repeatedly check if the database has managed to turn itself on yet. It brings back fond memories of a "simple" Postgres upgrade that required a similar babysitting script, which of course failed silently at 3 AM and took the entire user auth service with it. Good times.
Then we get to the index definition itself. Just look at this thing of beauty.
CREATE MULTIVALUE INDEX FRANCK_MVI ON FRANCK (JSON_MKMVI(JSON_TABLE(...NESTED PATH...ORA_RAWCOMPARE...)))Ah, yes. The crisp, readable syntax we've all come to love. It’s so... enterprise. It’s less of a command and more of a cry for help spelled out in proprietary functions. They say this complexity helps with troubleshooting. I say it helps Oracle consultants pay for their boats. Remember that "simple" ElasticSearch mapping we spent a week debugging? This feels like that, but with more expensive licensing.
To understand this revolutionary new index, we're invited to simply dump the raw memory blocks from the database cache and read the hex output. Because of course we are. I haven't had to sift through a trace file like that since a MySQL master-slave replication decided to commit sudoku in production. This isn't transparency; it's being handed a microscope to find a needle in a continent-sized haystack. What a convenience.
And the grand finale! After all that ceremony, what do we get? An execution plan that does an INDEX RANGE SCAN... followed by a HASH UNIQUE... followed by a SORT ORDER BY. Let me get this straight: we built a complex, multi-value index specifically for ordering, and the database still has to sort the results afterward because the plan shuffles them. We've achieved the performance characteristics of having no index at all, but with infinitely more steps and failure modes. Truly innovative. It's like building a high-speed train that has to stop at every farmhouse to ask for directions.
The author graciously notes that this new feature puts Oracle "on par with PostgreSQL's GIN indexes," a feature, I might add, that has been stable for about a decade. They also admit it has the same limitation: it "cannot be used to avoid a sort for efficient pagination queries." So, we've gone through all this effort, all this complexity, all this new syntax... for a feature that already exists elsewhere and still doesn't solve one of the most common, performance-critical use cases for this type of index. Stunning.
So, yeah. I'm thrilled. It's just another layer of abstraction to debug when the real Mongo, or Postgres, or whatever we migrate to next year, inevitably has a feature we can't live without. The fundamental problems of data modeling and query patterns don't disappear; they just get new, more complicated error codes.
...anyway, my on-call shift is starting. I'm sure it'll be a quiet one.