Wednesday, July 9, 2008

Pseudo Column ROWDATE?

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

9 comments:

  1. AnonymousJuly 9, 2008 at 10:28 PM

    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

    ReplyDelete
  2. AnonymousJuly 9, 2008 at 10:40 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.

    ReplyDelete
  3. oraclenerdJuly 9, 2008 at 11:16 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?

    ReplyDelete
  4. oraclenerdJuly 9, 2008 at 11:17 PM

    @rhodry

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

    chet

    ReplyDelete
  5. DomBrooksJuly 10, 2008 at 3:58 AM

    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.

    ReplyDelete
  6. DomBrooksJuly 10, 2008 at 3:59 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.

    ReplyDelete
  7. AnonymousJuly 10, 2008 at 6:17 PM

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

    ReplyDelete
  8. AnonymousJuly 10, 2008 at 8:45 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

    ReplyDelete
  9. oraclenerdJuly 10, 2008 at 9:48 PM

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

    chet

    ReplyDelete