Hi all!

I have an "on demand refreshable" materialized view in Oracle 9.2 Database. I use a "PCT tracking" mechanism for FAST_PCT refreshing of it. This mat. view is aggregated and contains joins of four tables. In fact, only one partition of one table changes once a day, every day. This table partitioned by time, so does the mat. view (the same partition key). I've no snapshot logs at all. DML that always occurs with my partitioned "master" table is DELETE-than-INSERT combination on one partition of the table. I had to add one corresponding COUNT(expr) for every SUM(expr) in snapshot's create statement, because documentation states it firmly.

Now I have a question. Why should I do it? Why should I add these additional, abuse columns? The documentation says also that I shouldn't do such an addition in case of insert-only ops on my "master" table.
Would someone kindly explain me WHY,WHY,WHY I need to add additional aggregates in case of other DML ( delete )???
The time of creating mat.view increases dramatically. The benifit of "PCT tracking" becomes very small.

Thanks in advance, Andrew.

Andrew: My thoughts are, without knowing what you are tracking, from my experience, your additional features to the application is due to the Sarbanes-Oxley Act of 2002 (SOX). This act has impacted private companies both in the USA and International. SOX directly addresses and garauntees that there are Financial Controls in place that mitigates risks and downfalls of private companies like Ensron, Tyco, etc. However since IT supports financial operations, IT has had to "jump thru hoops" also, following COBIT and/or ITIL framework. Your application, as you indicated is PCT-tracking. Directly or Indirectly this application may or impacts financial reporting, which means it hits the general ledger, or the tracking impacts security and/or Access and/or Operations. If this is the case, then you, your application, must provide substantial evidence, that the control is in place. Unaware of what PCT's acronym, it is important that the delete operation, should not occur and/or if it does, tracking the id is significant. You may even be required to print a report where the Manager of Operations signs of on it. The more signatures on a report, the more Internal and External Auditors like it. Another reason for your additions to the application is if somewhere in an accounting process their control in place is said to be systematic, which means that the system or application is validating the control, then the control had best exist.

A List of New Policies and Procedures have been developed and implemented, For example, Password Policy, the Change Control Policy, Computer Access Policy, Backup/Recovery or Business Continuity Policy, etc.

Dont be disheartened, it could call for a total re-write, I have seen and been involved in much worse.

Hope this helps.

peace95, are you... OK?

Yes, I am fine. It's just that the Sarbanes-Oxley Act is changing the way companies do business, which trickles down into IT.

I have put "the SoapBox" away. :)