Partition Change Tracking (PCT) API?
- From: "Tim Hopkins" <oracle-l@xxxxxxxxxxxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Mon, 4 Sep 2006 10:00:56 -0000 (GMT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Partition Change Tracking (PCT) API?
