This content has been marked as final. Show 1 reply
843217 wrote:You use USING INDEX clause, that's why it is slower.
So, basically when I run:
TRUNCATE TABLE MY_MVIEW;
EXECUTE DBMS_MVIEW.REFRESH ('MY_MVIEW','C',PARALLELISM=>8,ATOMIC_REFRESH=>false);
this is still about 3 times slower then;
DROP MATERIALIZED VIEW MY_MVIEW;
CREATE MATERIALIZED VIEW "MY_MVIEW"
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS SELECT * FROM MY_TAB;
But why don't use a fast refresh?
your query quite simple and it allows to use fast refresh, not complete. Create MView log for MY_TAB and refresh only new/modified/deleted records.