Partition Change Tracking (PCT) API?

Hi All,

    I have a requirement to create an aggregation based on a 600 million row date-partitioned table. Given the size of the table, a complete refresh isn't really viable and as analytic functions are involved, normal fast-refresh is also out of the question.

    That would normally leave PCT as the remaining refresh mechanism. Whilst PCT can work with analytic functions, by including PMARKER in the PARTITION BY clause, I need to use the value of the analytic function as a predicate. To do this, I need to put the analytic function in an inline view and use an outer WHERE clause to filter the values. This use of an inline view then rules out PCT, despite the fact that there are no inter-partition dependencies and PCT should theoretically be possible.

    The plan now is to use a form of manual PCT refresh. The first DML to a non-partitioned base table following a snapshot refresh records its SCN in the TAB$.SPARE3 column (credit to Steve Adams) and the equivalent for partitioned tables appears to be TABPART$.SPARE1 (based on a trace of a PCT refresh). An existing snapshot replication process will ensure that these columns are populated in my database.

    This is all good and appears to achieve what I need. The only problem is that's a bit of a hack and thus unsupported.

    Finally the question for the list is, does anybody know if there is a documented API for determining if a partition has been modified since the last refresh of an MV?

Cheers,
Tim

--
http://www.freelists.org/webpage/oracle-l