Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I don't know how helpful this is for you but Oracle requires all columns that the materialzied view grouped on to be included in the statement to be rewritten. (<em>edit</em> at least in conjunction with virtual columns. This is probably "not by design"...)</p> <p>If you try to <code>explain_rewrite</code> on</p> <pre><code> select demo_dim_one, sum(s) from ( select demo_dim_one, sum(demo_measure_from_virtual) s from demo_fact group by demo_dim_one, demo_dim_two ) group by demo_dim_one </code></pre> <p>it should tell you that it has rewritten the query. </p> <p>This can be demonstrated like so:</p> <p>A table to on which the virtual column will be defined:</p> <pre><code>create table tq84_virt_col ( a varchar2(2), b varchar2(2), c number, d number ); insert into tq84_virt_col values ('A', 'X', 1, 1); insert into tq84_virt_col values ('A', 'X', 2, 1); insert into tq84_virt_col values ('A', 'Y', 3, 0); insert into tq84_virt_col values ('A', 'Y', 4, 1); insert into tq84_virt_col values ('B', 'Y', 11, 1); insert into tq84_virt_col values ('B', 'X', 12, 0); insert into tq84_virt_col values ('B', 'X', 13, 1); </code></pre> <p>The definition of the virtual column:</p> <pre><code>alter table tq84_virt_col add ( virt_col number generated always as ( case when d = 1 then c else 0 end ) virtual visible ); </code></pre> <p>The materialized view. Note: it groups on columns <code>a</code> and <code>b</code>:</p> <pre><code>create materialized view tq84_mat_view refresh force on demand enable query rewrite as select a, b, sum(virt_col) sum_virt_col from tq84_virt_col group by a,b </code></pre> <p>The materialized view will not be used, as you have observed:</p> <pre><code>begin dbms_mview.explain_rewrite( 'select a, sum(virt_col) from tq84_virt_col group by a' ); end; / select message from rewrite_table; QSM-01150: query did not rewrite QSM-01102: materialized view, TQ84_MAT_VIEW, requires join back to table, TQ84_VIRT_COL, on column, VIRT_COL QSM-01082: Joining materialized view, TQ84_MAT_VIEW, with table, TQ84_VIRT_COL, not possible QSM-01102: materialized view, TQ84_MAT_VIEW, requires join back to table, TQ84_VIRT_COL, on column, C </code></pre> <p>Now, both columns <code>a</code> and <code>b</code> are selected and grouped on (with an outer query to ensure the same result set):</p> <pre><code>truncate table rewrite_table; begin dbms_mview.explain_rewrite( 'select a, sum(s) from (select a, sum(virt_col) s from tq84_virt_col group by a, b) group by a' ); end; / select message from rewrite_table; QSM-01151: query was rewritten QSM-01209: query rewritten with materialized view, TQ84_MAT_VIEW, using text match algorithm QSM-01219: no suitable materialized view found to rewrite this query </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POOracle query rewrite with virtual columns in the source table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRené Nyffenegger
    1. USRené Nyffenegger
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOracle query rewrite with virtual columns in the source table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COGreat explanation! Thanks for taking the time. Unlucky for me that I'm working with SAP BusinessObjects so I was hoping to use a non-nested query. I'll mark this as an accepted answer for explaining it so well and promptly.
      singulars
      1. PO
      1. USGudmundur Orn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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