2

Imagine I have a temporal table for personal information:

  • UUID (varchar)
  • main_document (varchar)
  • name (varchar)
  • DoB (timestamp)
  • genre (varchar)
  • address (varchar)
  • salary (decimal)

at T1 I run a schema migration and add a new column, from now on the table has:

  • UUID (varchar)
  • main_document (varchar)
  • name (varchar)
  • DoB (timestamp)
  • genre (varchar)
  • address (varchar)
  • salary (decimal)
  • EMAIL (varchar)*

Then at T2 I run another schema migration and change the data type of main_document to NUMBER.

  • UUID (varchar)
  • main_document (NUMBER)*
  • name (varchar)
  • DoB (timestamp)
  • genre (varchar)
  • address (varchar)
  • salary (decimal)
  • email (varchar)

Then at T3 I run another schema migration and remove the genre column

  • UUID (varchar)
  • main_document (number)
  • name (varchar)
  • DoB (timestamp)
  • ---------------*
  • address (varchar)
  • salary (decimal)
  • email (varchar)

Then at T4 I run another schema migration and add the genre column, but now it has the data type NUMBER.

  • UUID (varchar)
  • main_document (number)
  • name (varchar)
  • DoB (timestamp)
  • genre (NUMBER)*
  • address (varchar)
  • salary (decimal)
  • email (varchar)

How can I query my DB (going back in time) if the schema has changed on T1, T2, T3, T4, etc?

Like, we are at (wall time) T4 and run: select * from people AS OF T3, what should be returned? Is it really time travel?

Are there any best practices or strategies to avoid all this complexity with these temporal tables + schema migration?

Any help would be much appreciated.

Thanks

jfbaro
  • 301
  • 2
  • 10

1 Answers1

2

I'd recommend you take a look at the Edition Based Redefinition (EBR) concepts paper:

https://www.oracle.com/a/tech/docs/ebr-technical-deep-dive-overview.pdf

Even if you were not planning to use EBR, you get an understanding of how schema migration whilst still allowing seeing the schema at various points in its history is done.

In a nutshell, the table represents ALL temporal states of the data, and views represent point in time moments. Thus taking a much simpler example:

Time 1: Table is ID, NAME, GENDER
Time 2: Table is ID, NAME, DATE_OF_BIRTH

Then the table contains all attributes from all timelines, ie

ID, NAME, GENDER, DATE_OF_BIRTH

and you use views to control the time-based view of the data, eg

VIEW1: select ID, NAME, GENDER from TABLE
VIEW2: select ID, NAME, DATE_OF_BIRTH from TABLE

The benefit of EBR is that it allows "view1" and "view2" to both be called (say): "MY_VIEW" and you can run multiple versions of this view at the same time in the database.

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16