fast refresh of outer-join-only materialized views – algorithm, part 2

/* read touched outer slices end */

)

/* victim selection start */

where t_cnt > 1

and ( (nonnull_cnt = 0 and r > 1)

or

(nonnull_cnt > 0 and r <= t_cnt - nonnull_cnt)

)

/* victim selection end */

)

this substep has a SQL structure very similar to DEL.upd, hence I will simply outline the algorith: first, the statement identifies (in the “read touched outer slices” section) all the outer slices that had at least one rows inserted by INS.ins, by replaying its join; then, for each slice, it deletes any row, if it exists, that has column “test_inner_rowid” set to null (check the “victim selection predicate”).

Side note: I cannot understand how nonnull_cnt could be = 0 – possibly that is for robustness only or because it can handle variants of the DEL step I haven’t observed.

speeding up

These are the indexes that the CBO might enjoy using to optimize the steps of the propagation from the inner table:

- DEL.del: test_mv(test_inner_rowid, test_outer_rowid)

- DEL.upd: test_mv(test_inner_rowid)

- INS.ins: test_outer(jouter)

- INS.del: test_outer(jouter) and test_mv(test_outer_rowid , test_inner_rowid)

And hence, to optimize all steps:

- test_outer(jouter)

- test_mv(test_inner_rowid, test_outer_rowid)

- test_mv(test_outer_rowid , test_inner_rowid)

And of course we need the usual index on test_inner(jinner) to optimize the propagation from the outer table (not shown in this post), unless we positively know that the outer table is never modified.

Note that the two indexes test_mv(test_inner_rowid, test_outer_rowid) and test_mv(test_outer_rowid , test_inner_rowid) allow to skip visiting the MV altogether (except for deleting rows, obviously) and hence might reduce the number of consistent gets dramatically (the indexes are both “covering” indexes for the SQL statements we observed in the DEL.del and INS.del) .

For example, in my test case (check ojoin_mv_test_case_indexed.sql), the plan for the DEL.del step is:

--------------------------------------------------------------

| 0|DELETE STATEMENT | |

| 1| DELETE |TEST_MV |

| 2| NESTED LOOPS | |

| 3| VIEW |VW_NSO_1 |

| 4| SORT UNIQUE | |

| 5| VIEW | |

| 6| WINDOW SORT | |

| 7| HASH JOIN OUTER | |

| 8| HASH JOIN SEMI | |

| 9| INDEX FULL SCAN |TEST_MV_TEST_INNER_ROWID|

|10| VIEW |VW_NSO_2 |

|11| NESTED LOOPS | |

|12| TABLE ACCESS FULL |MLOG$_TEST_INNER |

|13| INDEX RANGE SCAN |TEST_MV_TEST_INNER_ROWID|

|14| VIEW | |

|15| SORT UNIQUE | |

|16| TABLE ACCESS FULL |MLOG$_TEST_INNER |

|17| MAT_VIEW ACCESS BY USER ROWID|TEST_MV |

--------------------------------------------------------------

5 - filter[ (T_CNT>1 AND ((IN_MVLOG_CNT=T_CNT AND R>1)

OR (IN_MVLOG_CNT<T_CNT AND R<=IN_MVLOG_CNT))) ]

...

Note the absence of any access to the MV to identify the rows to be deleted (row source operation 5 and its progeny; note the filter operation, which is the final “victim selection predicate”); the MV is only accessed to physically delete the rows.

Ditto for the INS.del step:

-------------------------------------------------------------------

| 0|DELETE STATEMENT | |

| 1| DELETE |TEST_MV |

| 2| NESTED LOOPS | |

| 3| VIEW |VW_NSO_1 |

| 4| SORT UNIQUE | |

| 5| VIEW | |

| 6| WINDOW SORT | |

| 7| HASH JOIN SEMI | |

| 8| INDEX FULL SCAN |TEST_MV_TEST_INNER_ROWID|

| 9| VIEW |VW_NSO_2 |

|10| NESTED LOOPS | |

|11| NESTED LOOPS | |

|12| TABLE ACCESS FULL |MLOG$_TEST_INNER |

|13| TABLE ACCESS BY USER ROWID|TEST_INNER |

|14| INDEX RANGE SCAN |TEST_OUTER_JOUTER_IDX |

|15| MAT_VIEW ACCESS BY USER ROWID |TEST_MV |

-------------------------------------------------------------------

5 - filter[ (T_CNT>1 AND ((NONNULL_CNT=0 AND R>1)

OR (NONNULL_CNT>0 AND R<=T_CNT-NONNULL_CNT))) ]

...

Page 2 of 3 | Previous page | Next page