Databases
StackOverflow2013
Postsdbo
PORefreshable on commit materialized view using MAX()
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORefreshable on commit materialized view using MAX()
    text
    copied!<p>I'm being hit hard by the <a href="http://karwin.blogspot.com/2009/05/eav-fail.html" rel="nofollow">entity-attribute-value antipattern</a>. Some day, years ago, a guy decided that DDL wasn't sexy, and wanted to develop something "flexible enough" <em>to keep information about people</em>. He ignored the fact that people uses to have at least some basic attributes, as name, date of birth, and the like. Not only that, he put a bunch of (side-effects-ridden) PL/SQL packages on top of that schema. The thing managed to be a key subsystem in which other applications relied on.</p> <p>Fast forward some years and 20 million rows. The guy isn't at the company anymore, and I have to deal with this. I need to implement some basic searches that right now would require multiple inner joins and just take forever for some cases. Rewriting the whole thing it's not possible, so I want to "pivot" the most important attributes.</p> <p>I thought that materialized views could be a viable alternative, but I need some guidance, since I have never used them. I would like to get a table like this:</p> <pre><code> select uid, max(case when att = 'NAME' then UPPER(value) end) name, max(case when att = 'SURNAME' then UPPER(value) end) surname, max(case when att = 'BIRTH' then DATEORNULL(value) end) birth, ...., count(*) cnt from t group by uid </code></pre> <p>as I understand reading Oracle docs, I should be able to create a "REFRESHABLE ON COMMIT" materialized view with MAX() <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14223/basicmv.htm" rel="nofollow">if the query has no where clause</a>.</p> <p>But can't get it to work. I've tried:</p> <pre><code>create materialized view log on t WITH SEQUENCE,ROWID,(value) INCLUDING NEW VALUES; create materialized view t_view refresh fast on commit as select uid, max(case when att = 'NAME' then UPPER(value) end) name, max(case when att = 'SURNAME' then UPPER(value) end) surname, max(case when att = 'BIRTH' then DATEORNULL(value) end) birth, count(*) cnt from t group by uid </code></pre> <p>It works for inserts, but not for updates. I see that is capable of these things:</p> <pre><code> REFRESH_COMPLETE REFRESH_FAST REFRESH_FAST_AFTER_INSERT </code></pre> <p>but I think I should see also REFRESH_FAST_AFTER_ONETAB_DML. Any ideas?</p> <p>Update: Output of dbms_mview.explain_mview</p> <pre><code>REFRESH_COMPLETE |Y| REFRESH_FAST |Y| REFRESH_FAST_AFTER_INSERT |Y| REFRESH_FAST_AFTER_ONETAB_DML|N|mv uses the MIN or MAX aggregate functions REFRESH_FAST_AFTER_ANY_DML |N|see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled REFRESH_FAST_PCT |N|PCT is not possible on any of the detail tables in the mater </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload