See https://www.triton.co.uk/time-travel-gotchas-part-1/ for Part 1 of this blog
Access paths
You will have noticed that my previous example also built the history table as a Multi-Dimensional Cluster (MDC). That doesn’t give any benefits in terms of space management but does address a problem with the access path. It’s not the only way; using insert time clustering (ITC), or just some well-built indexes and altering the table to be APPEND ON might work. But you will need to spend some time on the access strategy.
When you have defined the table as a SYSTEM Temporal table and you issue a query with a FOR SYSTEM_TIME clause in it, you’ll find the optimizer has done something like this to your SQL
You’ll see that what you now have is two queries UNIONed together to interrogate the contents of the Base and History tables. In my example the Total Cost went from 20.33 timerons to 52.50. That’s a fairly insignificant overhead but my test tables have minimal data in them (less than 20 rows) and the history table is only twice the size of the base table. I would anticipate the history table being far larger than the base table eventually. And look what happens when you enable one of the other tables in the same query as a System temporal table. It will go from an original statement (note the extra FOR SYSTEM TIME against the Customer table now) of
To this
You now have 4 queries joined by UNION ALL, the estimated Total Cost is 105 and the access path is becoming pretty complex. The optimizer has determined that it now has to have a query for each possible combination (ignoring CONTRACT_TYPE as this is not enabled as a System temporal table)
| CUSTOMER | CONTRACT |
| CUSTOMER | CONTRACT_HISTORY |
| CUSTOMER_HISTORY | CONTRACT |
| CUSTOMER_HISTORY | CONTRACT_HISTORY |
Adding another System temporal table, or converting CONTRACT_TYPE in this example, would invoke 8 sub-queries and a jump in estimated Total Cost to 672
| CUSTOMER | CONTRACT_TYPE | CONTRACT_HISTORY_MDC |
| CUSTOMER | CONTRACT_TYPE | CONTRACT |
| CUSTOMER_HISTORY | CONTRACT_TYPE | CONTRACT |
| CUSTOMER_HISTORY | CONTRACT_TYPE | CONTRACT_HISTORY_MDC |
| CUSTOMER_HISTORY | CONTRACT_TYPE_HISTORY | CONTRACT |
| CUSTOMER_HISTORY | CONTRACT_TYPE_HISTORY | CONTRACT_HISTORY_MDC |
| CUSTOMER | CONTRACT_TYPE_HISTORY | CONTRACT_HISTORY_MDC |
| CUSTOMER | CONTRACT_TYPE_HISTORY | CONTRACT |
This has the potential to get out of hand very quickly. There’s no hard and fast way of solving this, as for any performance issue, and I’m not going to presume to lecture experienced DBAs on how to design an efficient table and index strategy. I’m merely attempting to illustrate that there might be a problem with your queries once you enable System Time versioning, even if they ran like wildfire before you did the conversion.