I have an Oracle SQL database containing about 200 tables, with tables containing anywhere from 5 to 700000 records. I want to figure out which one of those tables contain records we actually modified over the past two years, preferably without having to query all 200 tables for a record containing a last_modified date after January 1st 2023. Would there be a quick way to just return the table names for every table containing a record that contains one or more records matching the criteria?
Page 1 / 1
Not sure I understand. Do you absolutely not want to query all tables? Or do you not want to do it by hand? Because I don’t know how to answer this without some questions :)
If you want to do it automagically, just use the SQLExecutor to first get all table names, then query every table using a second one. You can initiate anyting in workbench using a Creator transformer.
Some will argue that this could be done in one big SQL statement, which is true, but I prefer multiple smaller steps instead of one big one, especially when it takes a while and does fail / time-out.
Pro tip, use a Sampler to test with the first 3 tables or so to iterate faster through your workbench development. I have collegues always thinking “yeah I know how to do this I need no Sampler” ending up doing way more iterations than they anticipated.
Something like this, mostly SQL:
Takes about 40 seconds for 449 tables in my Oracle database.
And I'm pretty sure the second SQL Statement can be made faster.