Re: Select only updated rows from a table

From: Steve Howard <>
Date: Mon, 3 Mar 2008 16:11:17 -0800 (PST)
Message-ID: <>

On Mar 3, 4:25 pm, Robert Scheer <> wrote:
> Hi.
> The materialized views in Oracle have a log mechanism (the FAST
> clause) that can retrieve only the changed rows from a table. Is it
> possible to read this data in any way? I need to write a code that
> from time to time retrieves only the inserted/deleted/updated rows
> from a table and insert these rows on another table. I know that
> timestamp columns can be used for this, but is there any better
> solution to this?
> Regards,
> Robert Scheer

Hi Robert,

If the table has a primary key, it will be one of the columns in the materialized view log. They can be retrieved by querying the source table using this PK. If the source table doesn't have a PK, you can query the source table by rowid (will be a column in the MV log).

However, the materialized view log will grow until a MV somewhere refreshes from it. You can manually truncate/delete from the MV log, but I doubt it would be supported.

HTH, Steve Received on Mon Mar 03 2008 - 18:11:17 CST

Original text of this message