3 ms·
If your database supports it, unit tests are an absolutely ideal use-case for temporary tables or global temporary tables. A global temporary table can be defi
by paulmd 4y ago
If your database supports it, unit tests are an absolutely ideal use-case for temporary tables or global temporary tables. A global temporary table can be defined with the same schema as the correct table and will "exist" for the purpose of view/CTE definitions, but any data inserted into the table will only ever be visible from that specific thread context. The rules depend but basically either it exists until the thread is closed, or until the session calls commit, but the semantics will (theoretically) be optimized for one thread and rapid inserts/clears.
If you build your unit tests so that a thread will not commit until it's finished, or clear it before/after, it's pretty ideal for that. You can feed in data for that test and it won't be visible anywhere else.
Potentially if you scripted your table creates you could add an additional rule that GLOBAL TEMPORARY TABLE gets added to any other tabledefs but only for unit tests. Or just update both in parallel.
The other useful use-case I've found for this is that you can do an unlimited-size "WHERE myId IN (:1, :2 ...)" by doing a GTT expressed as "INNER JOIN myId = myGtt.myid", and this has the advantage of not thrashing the query planner for every literal sqltext resulting from different lengths of myList (this will fill up your query cache!). Since it's one literal string it always hits the same query plan cache.
I am told this has problems in oracle's OCI when using PDBs, apparently GTTs will shit up the redo-log tablespace (WAL equivalent). But so do many many things, PDBs are apparently a much different and much less capable and incredibly immature implementation that seems essentially abandoned/not a focus from what I've been told. I was very surprised having originally written that code in regular Oracle and not having had problems but PDBs just aren't the same (right down to nulls being present in indexes!).
- n0n0n4t0r 4y agoOff topic incoming (sorry ) I used this trick (join temporaryFoo instead of where foo in ...) in production fifteen years ago, using MySQL. The gain was really astonishing. Several instructions can be optimized using joins on specialty craft tables (I know of LIMIT for instance). This is one of the worst drawbacks of orm everywhere: nobody even seems to think about those optimisations anymore.
- paulmd 4y agoalso, views can be defined as ORM objects/POJOs. They can be read-only, some views are "trivially-remappable" (if there is a 1:1 mapping from view columns to table columns) or even you can use INSTEAD OF INSERT/UPDATE triggers to take writes on that view and do something completely else with it. ;) I've used that to do dumb shit like lever a table schema into an ORM mapping that it wasn't really designed for, that I had to maintain fallback compatibility conditions onto the tables. views are really a db-level "interface" implementation that few people really exploit fully. Here is the definition, here is the implementation. And yes global temporary tables are such a cute cheat for unlimited-size query-specific data ;)
- MrBuddyCasino 4y agoThat is why ORMs have fallzout of fashion.