Updating materialized view Free adult webcam without credit

Updating materialized view

REFRESH('closed_orders_by_cust_mv','FAST'); PL/SQL procedure successfully completed. SQL> UPDATE orders SET order_amount=100.11 WHERE order_amount=74.85 AND customer_id=1020; 1 row updated. TUNE_MVIEW(v_task_name, 'CREATE MATERIALIZED VIEW closed_orders_by_cust_mv REFRESH FAST AS SELECT customer_id , COUNT(*) as num_orders , MAX(order_amount) as max_order_amount FROM orders WHERE state = ''C'' GROUP BY customer_id'); END; / ERROR at line 1: ORA-13600: error encountered in Advisor QSM-03113: Cannot tune the MATERIALIZED VIEW statement QSM-02086: mv uses the MIN or MAX aggregate functions ORA-06512: at "SYS. Finding nothing, they would go back to their customers and give them the unpopular choice of either removing the aggregate functions from their mview or switching the mview to be a complete refresh.

Is possible to work around this apparent roadblock?

The Oracle documentation says that this is not possible. Before we dive into how to make a complex materialized view (mview) query work, let’s look at a simple mview query. REFRESH('orders_by_cust_mv','FAST'); PL/SQL procedure successfully completed. SQL> SELECT * FROM orders_by_cust_mv ORDER BY customer_id; CUSTOMER_ID NUM_ORDERS MAX_ORDER_AMOUNT ----------- ---------- ---------------- 1010 5 123.41 1020 3 100.11 1030 1 13.33 SQL> UPDATE orders SET order_amount=77.85 WHERE order_amount=100.11 AND customer_id=1020; 1 row updated. In this case, it is only for the one DELETE FROM "EXAMPLE"."ORDERS_BY_CUST_MV" "SNA$" WHERE (SYS_OP_MAP_NONNULL("SNA$"."CUSTOMER_ID")) IN (SELECT /* NO_MERGE HASH_SJ */ SYS_OP_MAP_NONNULL("MAS$"."CUSTOMER_ID") FROM (SELECT "MAS$"."CUSTOMER_ID", "MAS$"."ORDER_AMOUNT" FROM "EXAMPLE"."MLOG$_ORDERS" "MAS$" WHERE "MAS$".

As a materialized view query goes from simple to complex, Oracle’s fast refresh mechanism starts to fail. How exactly does a fast refresh of a simple mview query work when it includes an aggregate function like MAX? To show how oracle performs a fast refresh of a simple materialized view with a MAX aggregate function, we will start with a fairly generic CREATE TABLE orders ( order_id NUMBER(38) NOT NULL, customer_id NUMBER(38) NOT NULL, order_date DATE NOT NULL, state CHAR(1) NOT NULL, order_amount NUMBER(18,2) NULL, CONSTRAINT orders_pk PRIMARY KEY (order_id) ); CREATE MATERIALIZED VIEW LOG on orders WITH PRIMARY KEY, ROWID, SEQUENCE (customer_id, order_amount) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW orders_by_cust_mv REFRESH FAST AS SELECT customer_id , COUNT(*) as num_orders , MAX(order_amount) as max_order_amount FROM orders GROUP BY customer_id; SQL> SELECT * FROM orders ORDER BY customer_id, order_amount; ORDER_ID CUSTOMER_ID ORDER_DAT S ORDER_AMOUNT ---------- ----------- --------- - ------------ 1 1010 08-DEC-10 C 5.05 2 1010 08-DEC-10 O 12.99 5 1010 06-DEC-10 C 42.42 4 1010 07-DEC-10 C 55.01 3 1010 07-DEC-10 C 123.41 8 1020 07-DEC-10 C 74.85 7 1020 07-DEC-10 C 99.15 6 1020 08-DEC-10 O 9 1030 08-DEC-10 O 13.33 SQL> SELECT * FROM orders_by_cust_mv ORDER BY customer_id; CUSTOMER_ID NUM_ORDERS MAX_ORDER_AMOUNT ----------- ---------- ---------------- 1010 5 123.41 1020 3 99.15 1030 1 13.33 SQL> UPDATE orders SET order_amount=100.11 WHERE order_amount=74.85 AND customer_id=1020; 1 row updated. SNAPTIME$$ > :1 ) AS OF SNAPSHOT (:2) "MAS$") INSERT INTO "EXAMPLE"."ORDERS_BY_CUST_MV" SELECT /* */ "AV$"."CUSTOMER_ID", COUNT(*), MAX("AV$"."ORDER_AMOUNT") FROM (SELECT "MAS$".* FROM "EXAMPLE"."ORDERS" "MAS$" WHERE (SYS_OP_MAP_NONNULL("MAS$"."CUSTOMER_ID")) IN (SELECT SYS_OP_MAP_NONNULL("MAS$"."CUSTOMER_ID") FROM (SELECT /* HASH_SJ */ "MAS$"."CUSTOMER_ID", "MAS$"."ORDER_AMOUNT" FROM "EXAMPLE"."MLOG$_ORDERS" "MAS$" WHERE "MAS$".

You can query against the materialized view while it is being updated.

The example above shows that a simple mview with an aggregate function will fast refresh, but what happens when the mview query becomes more complex?

As we will see, the fast refresh ability starts to fail.

Is it possible to overcome these limitations with a fast refresh materialized view? SNAPTIME$$ > :1 ) AS OF SNAPSHOT (:2) "MAS$" )) "AV$" GROUP BY "AV$"."CUSTOMER_ID" We can see that these DELETE and INSERT statements are fairly complicated for even our simple mview query.

As our mview query goes from simple to complex, the logic to generate the appropriate DELETE and INSERT statements becomes more and more difficult.

updating materialized view-68updating materialized view-64updating materialized view-45

This means queries can still use the materialized view while it's being updated.

Join our conversation (50 Comments).
Click Here To Leave Your Comment Updating materialized view.

Comments:

Leave a Reply

Your email address will not be published. Required fields are marked *