I'm working a little bit in the datawarehouse again (by the way, is it two words or one?). A technical guy from Oracle showed up today to help us decide the best way to move forward on capturing changes. There were essentially 4 methods:
1. SQL and PL/SQL
2. Streams (CDC)
3. OWB/ODI
4. Logical Standby/Data Guard
As someone on the phone was talking, I started to wander...hmmm...what about some kind of pseudo column for that stored the last update (either INSERT or UPDATE) of a row?
Off the top of my head, I can think of rowid, rownum, and level. I'm sure there are others (feel free to comment).
Oracle, I'm sure, stores that information some where right?
I hadn't really given a thought as to the feasibility or the impact it might have, but that would make capturing changes a whole lot easier...
9 comments:
You'll surely accuse me of an over complicating, but I'm proud of my solution :)
SQL> update emp set sal=5000 where ename='FORD';
1 row updated.
SQL> commit;
Commit complete.
SQL> update emp set sal=3000 where ename='MILLER';
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT max(ename) keep (dense_rank last order by versions_startscn) ename,
2 max(sal) keep (dense_rank last order by versions_startscn) sal
3 from emp
4 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
5 where versions_endscn is not null;
ENAME SAL
---------- ----------
MILLER 3000
Try looking up enabling ROWDEPENDENCIES at the table level and then using ORA_ROWSCN.
ORA_ROWSCN is a pseudo-column (well actually its a system hidden column costing about 6 bytes per row) that gives the SCN (system change number?) of the last mutation.
Tom Kyte has written several useful articles about such.
R.
@prodlife
I would never accuse you of such things.
Actually, I quite admired your random_row trick...perhaps what you perceived was envy? ;)
My DBA mentioned that earlier to me, using the SCN. I had no idea it was at the record level.
I'm guessing Streams/CDC uses something similar via the Log Miner?
@rhodry
I'm looking for those articles now...thanks for the tip.
chet
Beware that ora_rowscn is at the block level, so if there are multiple rows per block, if one row changes, the ora_rowscn changes for all those rows.
I've got three words for you:
Asychronous hotlog/autolog CDC.
If you can get it working, it rocks.
And the CDC api hides much of the complexity, or fiddling rather, of AQ and Streams.
Maybe flashback archive on 11g:
http://laurentschneider.com/wordpress/2007/08/flashback-archive-table.html
@dombrooks (and all)
Actually ORA_ROWSCN is only at the block level if the table declared with NOROWDEPENDENCIES (the default).
With ROWDEPENDENCIES declared then row level tracking is enabled and ORA_ROWSCN is maintained at the row level.
source:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#CJAEEGDA
Rhodry
Thanks everyone for the pointers. The more information, the better the decision!
chet
Post a Comment