Materialized views ddl and dml stati (and the infamous NEEDS_COMPILE)

introduction

Employing materialized views (or snapshot or mview for short) for producing simple sum-up reports is really cool stuff. You just launch some statement like the following:

create materialized view some_mv
  build immediate
  refresh force next (sysdate + 1/24/12)
as
select X.ts, Y.name from (
  select to_char(max(A.ts), 'dd.mm.yyyy hh24:mi') as ts, B.blubb
    from tab1 A, tab1_1 B where A.id = B.id
    group by B.blubb
) X, tab2 Y
  where X.blubb = Y.id;

and will be served with a well performing collection of data that is more or less current (that is why i call that simple). Oracle will care for the refresh of the mview by self-registering a job on calling dbms_refresh.refresh('some_mv');, that starts immediately and takes the interval from the statement. Handing in the force keyword just chooses a fast refresh method iff possible and a complete refresh method otherwise. There are more defaults applied by leaving other keywords unset, the refresh mode for example, but this is not the point of the article. It is:

Why does that … mview always show a state of NEEDS_COMPILE although no ddl action has happend along the objects dependency chain lately?

Creating unique constraints (indexes) for multidimesional datasets

introduction

there sometimes exists requirements to enforce unique constraints on datasets that do not have a simple layout of distinct values over one or, if logically concatenated, more columns. for example, imagine the real world scenario of a 1:n, parent-child table relationship, where a parent table represents a number of document records and the child table holds a multitude of typed document attachement information. imagine futher, that only one document attachement type, say 'TYPE_0', should serve as a master or most current attachement per document. other document attachement types, say 'TYPE_1' and 'TYPE_2', may recur per document without any limitation.

the classic approach of a two-column unique constraint, comprising some doc_id and some doc_type columns, will not be appropriate in that case. the constraint will be violated as soon as any recurring of document types, 'TYPE_1' and 'TYPE_2', per document takes place.

a really impressive solution

the following problem solution is not new, nor has it been invented by me at all (thanks to m.s. for pointing in out to me) but i always go crazy about the way this very solution evolves from a merge of, on the first spot, rather different database schema design techniques. that is:

• the nature of unique constraints, compared to primary key constraints and
• the world of index definitions, specifically unique and function-based indexes.