Hi, we (and our customers) have been using versioned databases, mainly on Oracle, for a long time now, and we used to employ and recommend a pattern of analyze - compress - analyze, with a heavy emphasis on the benefits of a full compress. The pattern of version usage varies, some sites use work order versions that are created and edited by one person, and then checked and posted by another, so there's typically a backlog of versions (e.g. 100-200) around to prevent a full compress. Other sites use "personal" versions that are frequently reconciled, posted and reused, and then deleted on a Friday afternoon to achieve a "clean" database for the weekend. In many cases, SDE administrators check the number of states after compress, and if it is significantly greater than 1, they get rather nervous. Lately, replication has become more common, and the hidden replica versions make admins anxious because achieving a full compress gets increasingly difficult (there's that very helpful white paper "Compressing ArcSDE Geodatabases That Contain Replicas" that describes the situation and what to do about it).
Now, I find that a full compress, followed by updating statistics, may be harmful rather than helpful: Oracle believes that the delta tables are empty, and while they are indeed empty, this is fine, but in the course of an editing day, they start to accumulate data, and while statistics don't reflect this, access to delta tables may well be suboptimal. A typical example is the SDE_STATE_ID index being used rather than the SDE_STATE_ID, OBJECTID one, even though both values are available for use. Yes, Oracle will detect bad execution plans eventually and realize that the tables aren't empty any more, but that may take some time.
So, my recommendation would be to go one of two ways:
Any thoughts or experiences about this?
Thanks a lot, Matin