ORACLENERD: Pseudo Column ROWDATE?

Pseudo Column ROWDATE?

Wednesday, July 9, 2008

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...

Labels: datawarehouse, oracle

¶ 7/09/2008 09:56:00 PM

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

# posted by

prodlife : July 9, 2008 at 10:28 PM

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.

# posted by

rhodry : July 9, 2008 at 10:40 PM

@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?

# posted by

oraclenerd : July 9, 2008 at 11:16 PM

@rhodry

I'm looking for those articles now...thanks for the tip.

chet

# posted by

oraclenerd : July 9, 2008 at 11:17 PM

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.

# posted by

DomBrooks : July 10, 2008 at 3:58 AM

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.

# posted by

DomBrooks : July 10, 2008 at 3:59 AM

Maybe flashback archive on 11g:
http://laurentschneider.com/wordpress/2007/08/flashback-archive-table.html

# posted by

prodlife : July 10, 2008 at 6:17 PM

@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

# posted by

rhodry : July 10, 2008 at 8:45 PM

Thanks everyone for the pointers. The more information, the better the decision!

chet

# posted by

oraclenerd : July 10, 2008 at 9:48 PM