Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, M..

Asked: September 16, 2010 - 3:43 pm UTC

Last updated: November 11, 2010 - 2:38 pm UTC

Version: 11g R2

Viewed 1000+ times

You Asked

Hello,

I am new to Oracle but have a few years of SQL Server experience. Can you please recommend the best way to design an Oracle database based on the following model:

The database consists of several tables; let's specify for example Customers and SalesReps tables. They are joined by an ID and a query that joins them can be easily written.
So far it seems very simple but every month (or week) new data is imported into these tables (can be 500,000 records at a time) from CSV files and this can go on for years.
So I have a bunch of records from Jan 1, another bunch from Feb. 10, etc in Customers and similar records in SalesReps table (but not necessarily with the same date, as new data is imported into SalesReps every three months but in Customers we load new data every two weeks or even more frequently...)
I can specify a StartDate and EndDate for every import and store these dates in two additional columns in each table.

I need a way to join these tables by the date range as well, so that when I query Customers I need only the records in SalesReps that were imported within the same date interval or the closest date.
For example I want to view and join the data in both tables as of Sep 10, 1999.

What is the best way to achieve this functionality? I mean joining two tables with historical data based on date.Is there any Oracle built in functionality for this?
I am using Oracle 11g R2.

Regards,
M. R.

and Tom said...

total recall - a long term flashback query - would do that.

http://www.oracle.com/us/products/database/options/total-recall/index.htm


You would just maintain the current data (eg: you would update records with new values, delete records that no long exist today, insert new records) and then you can simply query the data "as of" some prior point in time.

I should mention that is an option to the database - something that would need be licensed.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

M. R., September 16, 2010 - 8:17 pm UTC

Thanks Tom! I shall definitly try this solution.

Best regards,
M. R.

Florian Reiser, November 09, 2010 - 4:25 am UTC

Hello Tom,

I am evaluation Total Recall under Oracle 11g R2.
I am trying to join two tables together to get a time consistent view of them in a history display screen.

The SQL is as follows:
CREATE TABLE parent (ID NUMBER, DATA VARCHAR2(30 CHAR));
CREATE TABLE child (ID NUMBER, parent_id NUMBER, DATA VARCHAR2(30 CHAR));
ALTER TABLE parent FLASHBACK ARCHIVE test_archive;
ALTER TABLE child FLASHBACK ARCHIVE test_archive;
INSERT INTO parent (ID, DATA) VALUES (1, 'TEST');
INSERT INTO child (ID, parent_id, DATA) VALUES(1, 1, 'TEST_CHILD');
COMMIT;
UPDATE parent set data='TEST2';
UPDATE child set data='TEST_CHILD2');
COMMIT;

<Select-Statement>

Expected Result:
ID|p_data|c_data |scn
--------------------------
1 |TEST |TEST_CHILD |1
1 |TEST2 |TEST_CHILD2 |2

What Select-Statement is necessary to retrieve this information? If I use the following:
SELECT C.ID,
(SELECT DATA FROM parent AS OF SCN c.versions_startscn)
p_data, c.data c_data FROM CHILD VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE C;

Oracle issues an ORA-00984 error. Variations of this statement also result in an ORA-00984. Can you give me a hint, how to join this two tables?

Regards

F.R.
Tom Kyte
November 10, 2010 - 12:28 pm UTC 

ops$tkyte%ORA11GR2> select * From v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

ops$tkyte%ORA11GR2> CREATE TABLE child (ID NUMBER, parent_id NUMBER, DATA VARCHAR2(30 CHAR));

Table created.

ops$tkyte%ORA11GR2> ALTER TABLE parent FLASHBACK ARCHIVE test_archive;

Table altered.

ops$tkyte%ORA11GR2> ALTER TABLE child FLASHBACK ARCHIVE test_archive;

Table altered.

ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> INSERT INTO parent (ID, DATA) VALUES (1, 'TEST');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO child (ID, parent_id, DATA) VALUES(1, 1, 'TEST_CHILD');

1 row created.

ops$tkyte%ORA11GR2> COMMIT;

Commit complete.

ops$tkyte%ORA11GR2> UPDATE parent set data='TEST2';

1 row updated.

ops$tkyte%ORA11GR2> UPDATE child set data='TEST_CHILD2';

1 row updated.

ops$tkyte%ORA11GR2> COMMIT;

Commit complete.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT C.ID, (SELECT DATA FROM parent AS OF SCN c.versions_startscn) p_data, c.data c_data
  2    FROM CHILD VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE C;

        ID P_DATA                         C_DATA
---------- ------------------------------ ------------------------------
         1 TEST2                          TEST_CHILD2
         1 TEST2                          TEST_CHILD



can you post the same?

Florian Reiser, November 11, 2010 - 1:08 am UTC 

Hello Tom,

I've tried your sql. Nonetheless the following error is issued:

ORA-00904: "C"."VERSIONS_STARTSCN": ungültiger Bezeichner

sql> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

What am I doing wrong?

Tom Kyte
November 11, 2010 - 2:38 pm UTC

please contact support for this - I don't have an 11.2.0.2 instance right now (no time to install before my trip).

something changed between 11.2.0.1 and 11.2.0.2
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.