The Overview of Materialized View Getting the most out of MetaLink 최창권 한국오라클제품지원실 안녕하십니까? 한국오라클에서주최하는 Technical iseminar Mview Overview 에참석해주신여러분께감사드립니다. 저는이번세미나를진행하게될한국오라클제품지원실에근무하는최창권입니다. 본 seminar 는 Replication 환경에서사용하는 Mview 와 DW 환경에서사용하는 Mview 에대해서알아보기로하겠습니다. Mview 를사용함으로인해 Query speed 를보다빠르게사용할수있게하는 Query Rewrite 기능및 I/O 를분산하여 Base Table 에대한 Load 를줄일수있는데대해서목적을가지고있습니다.
Agenda What is Mview? Refresh method and option Mview for Replication Mview for DW Query Rewrite Troubleshooting. Reference 2 본 seminar 는 먼저 Mview 가무엇인지? 그리고 Mview 의 Refresh method 및 Option 에대해서알아보도록하겠습니다. 이후 Replication 에서사용되는 Mview 및 DW 에서사용되는 Mview 에대해서알아보도록하겠습니다. 그리고 Query Rewrite 기능에대해서알아보며, Troubleshooting 에대해서알아보고자합니다.
VIEW What is Mview? 논리적인 Table Object 로존재하지않는다. DBA_SEGMENTS 에존재하지않는다. Mview 물리적인 Table 을포함한 view View 의결과를물리적으로 Table 에저장한다. DBA_SEGMENTS 에서확인이가능함. Table 과 Mview 는동일한이름으로생성. ORACLE 8i 이전 : Snapshot 으로제공됨 8i 에서 Snapshot 의기능을발전시켜 Mview 로소개됨 3 먼저 Mview 는무엇인가? 이전에 VIEW 에대해서먼저확인해볼필요성이있습니다. VIEW 의경우물리적인 Object 가아닌단지논리적인 Definition 입니다. 이에반해 MIVEW 의경우물리적인 Table 을포함하는 View 의형태입니다. 이는실제로 DBA_ObjectS 를조회하게되면다음과같은형태로 Table 을포함하여생성이되게됩니다. SQL> Select OWNER,Object_NAME,Object_TYPE,STATUS 2 FROM DBA_ObjectS 3 WHERE Object_NAME='DEPTV' ; OWNER Object_NAME Object_TYPE STATUS ---------- ------------------------------ ------------------ ------ - SCOTT DEPTV Table VALID SCOTT DEPTV MATERIALIZED VIEW VALID 위의결과와같이 Table 은 Mview 와동일한이름으로생성이됩니다. 8i 이전에서는주로 Replication 환경에서 Snapshot 으로사용이되었습니다. 이후 8i 에서 Mview 로통합되어 Optimizer 의위에서동작할수있도록하는 Local site 의 Summary Table 을 Reference 할수있는기능을제공합니다.
The Need for Materialized Views Remote 의 Table 에대한 Data 를 Acess 하지않고 Local Table 에서만 Query 를수행하여 Network Overload 를줄이기위해서 Large Database 의 Query Performance 를증대하기위해서 Database 의 Table 에대한 Query 를수행하기전 Join 및통계를위한 Data 를미리수집하기위해서 Detail Table 에서 Query 를수행하지않고 Mview 에대해 Query 를수행하기위해서 4 Mview 를사용하는이유는먼저 Replication 의관점에서보면, Master Node 의 Data 를복제하여 Network 을통하여 Access 하여 Network 에대한 Overload 를줄여주기위하여 Mview 를생성합니다. 따라서 Remote Node 에서만 Query 를수행하기때문에 Master Table 에대한 Network Load 를줄일수가있습니다. 그리고 DW 관점에서보자면 Large Database 에대한 Query Performance 를증대할수있으며, Join 방식형태의 Query 나통계를수집하기위한 Data 를 Mview 로미리생성해놓음으로써 Query 에대한 Cost 를줄일수가있습니다. 또한 Base Table에대한 Query 를수행시관련되어있는 Mview 의 Rewrite 기능을이용하여 Mview 의통계정보를직접이용할수가있습니다.
On commit Refresh Mode Commit 발생시자동으로 Refresh. Master Table 에대하여 concurrent 한변경작업이일어나는경우부적합. On demand (default) Package 를이용하여 manual 하게 Refresh DBMS_MVIEW Dbms_Refresh : Refresh Group 을생성했을경우 Dbms_job : JOB 에등록을했을경우 5 Refresh mode 로는 On Commit 방식과 On Demand 방식이있습니다. 이중 On Commit 방식은 Refresh Group 등에대한고려가필요없으며, Commit 이발생함과동시에자동으로 Refresh 가됩니다. 이는 Mview 가 Reference 하고있는 Master Table 에동시에많은 Session 들에의한변경작업이있을경우 Recursive 하게 Mview 까지반영을해야하므로 Performance Issue 를발생할수있습니다. 그리고 On Demand 방식은 Create Mview 시의 Default Option 으로 DBMS_MVIEW 나 Refresh Group 을생성하여 Dbms_Refresh 또는 Job 으로등록을하여 Dbms_job 등에의해서 Manual 하게 Refresh 를수행하는작업이필요합니다.
Refresh Options Complete Refresh 시점에 Mview 에대한 Table TRUNCATE 전체 Data 를다시 Query 해서적용 Fast Base Table 에대한 Mview log 를이용하여변경된 Data 를적용 Force (default) 먼저 FAST 방식으로시도하지만 FAST Refresh 방식이실패할경우 COMPLETE Refresh 로시도 Never Refresh 를전혀수행하지않음. 6 그럼 Mview 가 Refresh 되는 Option 에대해서알아보도록하겠습니다. Refresh 가되는 Option 으로는다음과같이 4 가지가소개가됩니다. 먼저 Complete 의경우는이는 Mview 를새롭게생성하는것과다를바가없습니다. 이는먼저 Mview Table 에 Truncate 를하여전체 Data 를모두삭제한후 Master Table 의 Data 를재 Query 하여 Refresh 하는방법입니다. 그리고가장많이사용되는 Option 으로 Fast 방식이있습니다. Fast 방식은 Master Table 의 Mview log 를이용하여 Master Table 에변경된 Data 만을 Mview 에적용하는방식입니다. 그리고 Force 방식과 Never 방식이있습니다. Force 방식은 Refresh 시에먼저 REFRESH FAST 방식으로시도를하지만 REFRESH FAST 방식이실패했을경우 Complete 방식으로 Refresh 를시도하게됩니다. Never 는 Refresh 를전혀수행하지않는것을의미합니다.
Restrictions on Fast Refresh(1) SYSDATE, ROWNUM 을포함하지않아야함. RAW, LONG LAW type 을지원하지않음. Select List 에 subquery 는지원되지않음. Select 절에 RANK 함수등 analytical function 은허용되지않음. MODEL 절을포함하지말것. SubQuery 를가지는 Having 절을포함하지말것. 7 REFRESH FAST 를수행중의제약사항은다음과같은내용이있습니다. 1. Select List 에서 SYSDATE, ROWNUM 을포함하지않아야합니다. 2. Datatype 중 RAW, LONG RAW Type 을지원하지않습니다. 3. Select List 에 subquery 는지원이되지않습니다. 4. Select 절에 RANK 등의분석함수는허용이되지않습니다. 5. MODEL 절에대해서는허용이되지않습니다. 6. SubQuery 를가지는 HAVING 절을포함해서는안됩니다.
Restrictions on Fast Refresh (2) ANY, ALL, NOT EXISTS 등을가지는 Nested Query 를포함하지말것. CONNECT BY 절을포함하지말것. 각기다른 site 의여러Table 을포함하지말것. On-commit Mview 는 Remote Table 을참조해서는안됨. Nested Mview 의경우Join 이나집합연산을가져야함. 8 7. ANY, ALL, NOT EXISTS 등을가지는 Nested Query 를포함하지않아야합니다. 8. CONNECT BY 절에대해서는허용이되지않습니다. 9. 각각다른 Site 의여러 Table 에대해서는지원이되지않습니다. 10. ON-commit Mview 의경우 Remote Table 을참조해서는안됩니다. 11. Nested Mview 의경우 Join 이나집합연산에대한 Mview 이어야합니다.
Mview for Replication 분산 Database 환경에서 Data 를복제하기위해서사용 각각의 site 의변경된 Data를동기화하기위해서사용 Remote 의 Master Table 을 access 하지않고 Local site 의 Mview 로 access 함으로이해 network overload 해소 Master site 로의 connection 이끊어져있는상황에서 Local 에서 Data 접근가능 Remote Data Mart 에유용 9 Replication 상에서 Mview 를사용하는이유는 1. 분산 Database 환경에서 Data 를복제하여 Local Table 에 Data 를저장하기위함입니다. 이는 Read Only 형태로복제가가능하며, Updatable 형태로복제가가능합니다. Read Only의경우는 Remote 에서는전혀변경을할수없으며, 단지 Master Site 에서변경된 Data 를 Sync 하여참조하는형태입니다. 다음으로 Updatable Mview 의경우 Remote 에서도 Update 가가능합니다. 이는 Read Only 의 Data 복제하는방법과같은맥락으로복제가되지만 Remote Site 에서는변경된 Data 를따로저장하는 USLOG$_MVNAME 이생성되는점과변경된 Data 를 Push 하는 Mechanism 이있다는점의차이가있습니다. 2. 각각의변경된 Data 에동기화가가능합니다. 3. Master Site 에대한 Data 를 Remote 의 Table 로저장하기때문에 Network 을이용하여 Access 해야하는부담을줄이며, Network 에대한 Overload 를해소할수있습니다. 또한 Network 이끊기거나상대 Node 의 Instance Fail 이일어나더라도 Mview Site 의 Local Table 에 Access 하여 Data 에대한 Query 가가능합니다. 4. Remote Data mart 등에유용하게사용이됩니다.
Type of Mview for Replication Read only Mview Mview 에대한변경작업이불가능 Master site 의 Mview log 를이용하여변경된 Data 반영 Updatable Mview Mview site 에서변경작업이가능 USLOG$ Table 에변경 Data 를저장 Master site 에변경된 Data 를반영후 Mview log 의정보를 Refresh Uslog$ Table 의정보 purge. 10 Replication 을위한 Mview 는다음과같이 Read Only Mview, Updatable Mview 가있습니다. 먼저 READ ONLY Mview 의경우 Master Table 에서만변경작업이가능하며 Mview Site 에서는변경작업자체가불가능합니다. 그리고 Updatable Mview 의경우, Master Site 뿐만아니라 Mview Site 에서모두변경작업이가능합니다. Mview Site 에서변경작업이일어날때 Data 가반영되는순서를보자면, 먼저 Mview Site 에서변경작업이일어나게되면변경된 Data 는 USLOG$ Table 에저장이됩니다. 이후 Refresh 를수행했을경우혹은 Deferred Transaction 에대해서 Master Site 에 Uslog$ Table 의 Data 를전송하게되며, Master Site 에전송된 Data 는 Master Table 및 Mview log Table 에실제로반영이됩니다. 이후 Mview Site 에서는해당 Data 에대한 mlog$ Table 의정보를 Refresh 하여 Uslog$ Table 과비교하여해당 Data가정상적으로반영이되어있는것을확인후 Uslog$ Table 정보를 Purge 하는형태의구조를가지고있습니다.
Mview for DW SUM 및 AVG 등의집합연산에대한통계 Data 를 Mview 에저장하여미리연산을하는역할 Join 에대한 Query 를미리수행 중요한 Query 에대해서 Join 및집합연산에대한 cost 를줄이는역할 11 본 SLIDE 는 DW 환경에서의 Mview 를소개하고있습니다. DW 환경에서는 SUM 등의집합연산을통한 SQL 문장을많이사용하게됩니다. 또한여러 Table 을 Join 하여통계정보를수집하는 SQL 문장들을많이발행하게됩니다. 하지만해당 Query 들은전체의 Data 에대한정보를기반으로통계가산출되기때문에많은 COST 가필요로하며, 해당 Query 들로인하여전반적인 Database 의 Performance 에영향을줄수있습니다. 이에 Mview 를사용하여 SUM 및 AVG 등의집합연산을하는통계 Data 를미리 Table 에저장하여미리연산을할수있습니다. 또한 Join 등이실행되는 SQL 문장에대해서 Join Operation 을수행하여 Mview 내에저장이가능합니다. 그리고 Join 연산및집합연산에대한 Mview 들을 Nested Mview 로생성이가능합니다.
Types of Mview for DW Materialized Views with Aggregates Materialized Views Containing Only Joins Nested Materialized Views 12 본 SLIDE 에서는 DW 환경에서사용되는 Mview 의 TYPE 에대해서소개하고있습니다. DW 환경에서의 Mview type 은다음과같이 3 가지형태로존재합니다. 1. 집합함수를가지는 Mview 의형태 2. 여러 Table 에대한 Join 의형태를지닌 Mview 의형태. 3. 그리고이를조합한형태의 Nested Mview 의형태 가있습니다.
Restrictions on Fast Refresh on Materialized Views with Aggregates All restrictions from General Restrictions on Fast Refresh 모든 base Table 에대해서 Mview log 가생성되어있어야한다. Mview 에서 reference 하는모든 column 이포함되어야한다. 반드시 rowid 와 INCLUDING NEW VALUES 절이포함되어야한다. Fast Refresh 의경우 SUM, COUNT, AVG, STDDEV, VARIANCE, MIN, MAX function 만을지원한다. COUNT(*) 가반드시포함되어야한다. AVG(expr) 를사용시반드시 COUNT(expr) 이존재해야한다. 13 먼저집합연산을가지는 Mview 에대해서알아보도록하겠습니다. 본 SLIDE 는집합연산을가지는 Mview 를사용하고자하는경우 REFRESH FAST 시의제약사항을소개하고있습니다. 1. General Restriction of Fast Refresh 의제약사항이적용되며, 2. 모든 Base Table 에대해서는 Mview log 가생성되어있어야합니다. 이때 Mview log 는반드시 Rowid 와 INCLUDING NEW VALUES 절이포함되어야하며, Mview 에서 Reference 하는모든 Column 이포함되어있어야합니다. 3. FAST Refresh 의경우 SUM, COUNT, AVG, STDDEV ( 편차 ), VARIANCE ( 분산 ), MIN, MAX function 만을지원합니다. 4. COUNT(*) 이반드시포함되어있어야합니다. 5. AVG(expr) function 을사용하는경우에는 COUNT(expr) 이같이존재해야합니다.
Restrictions on Fast Refresh on Materialized Views with Aggregates VARIANCE(expr) or STDDEV(expr) function 이사용되는경우 COUNT(expr) 과 SUM(expr) 이반드시존재해야한다. Select List 에는 GROUP BY column 이존재해야함. 각각의집합연산을하는 column 에대해서COUNT function이포함되어야한다. AVG(amount_sold), COUNT(amount_sold) Only-insert 에대해서만지원되는경우 MIN or MAX 연산을가지는 Mview SUM(expr) 는있으나 COUNT(expr) 는없는경우 COUNT(*) 이없는경우 14 6. VARIANCE(expr) or STDDEV(expr) function 이사용되는경우 COUNT(expr) 과 SUM(expr) 이반드시존재해야합니다. 7. GROUP BY 가사용되는경우관련 Column 은 Select List 에존재해야합니다. 8. AVG(expr) 등의집합연산을수행하는경우 COUNT(expr) function 이포함되어야합니다. 9. 다음의경우에도 Fast Refresh 를지원을하지만단지 Insert Operation 에대해서만지원이됩니다. MIN or MAX Function 을가지는 Materialized Views, SUM(expr) 은있으나 COUNT(expr) 이없는경우, COUNT(*) 이없는경우
Creation of Mview log for Materialized Views with Aggregates CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, rowid (prod_id, prod_name, prod_desc, prod_subcategory, prod_subcat_desc, prod_ category, prod_cat_desc, prod_weight_class, prod_unit_of_measure, prod_pack_ size, supplier_id, prod_status, prod_list_price, prod_min_price) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, rowid (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; 15 본 SLIDE 에서는집합연산을가지는 Mview 를생성시의 Mview log 를생성하는실제예를소개하고있습니다. 본예에서가장먼저확인할수있는사항은 1. Mview Log 는반드시 Rowid 와 INCLUDING NEW VALUES 절을가져야합니다. 2. Mview Log 는 Mview 가 Reference 하는 Column을모두포함해야합니다. 따라서 SLIDE 에서소개되는 Mview Log 는집합연산을가지는 Mview 를생성하기위한조건에부합한다고할수있습니다.
Materialized Views with Aggregates Example CREATE MATERIALIZED VIEW product_sales_mv BUILD IMMEDIATE REFRESH FAST ENABLE Query Rewrite AS Select p.prod_name, SUM(amount_sold) AS dollar_sales, COUNT(*) AS cnt, COUNT(amount_sold) AS cnt_amt FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY prod_name; 16 다음으로이전 SLIDE 에서생성된 Mview Log 를이용하여집합연산을가지는 Mview 를생성하는예를소개하고있습니다. 본 SLIDE 에서 CHECK 해야하는점은 REFRESH FAST 방식으로 Mview 가생성된다는점입니다. 따라서집합연산을가지는 Mview 를 FAST Refresh 하고자하는경우의제약사항에부합해야합니다. 1. GROUP BY 절에서 prod_name Column 이사용이되었기때문에반드시 Select List 에존재해야합니다. 2. Amount_sold 에대한 SUM function 을사용하였기때문에 COUNT(amount_sold) 가존재해야합니다. 3. COUNT(*) 절을가지고있습니다. 따라서본 SLIDE 의 Mview 문장은모든조건이부합되기때문에 FAST Refresh 방식의 Mview를생성하는데부합한조건을가지고있습니다.
Materialized Views with Aggregates Example CREATE MATERIALIZED VIEW product_sales_mv BUILD DEFERRED REFRESH COMPLETE ON DEMAND ENABLE Query Rewrite AS Select p.prod_name, SUM(amount_sold) AS dollar_sales FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name; 17 본 SLIDE 에서는 COMPLETE REFRESH 방식의 Mview 에대한예를들고있습니다. SLIDE 에서소개되는 Mview 는 COMPLETE Refresh 방식으로생성이되었지만 REFRESH FAST 방식으로도생성이가능합니다. 하지만 제약조건의내용중 COUNT(*) 가 Select List 가없으며, SUM(amount_sold) function 을사용하고있지만 COUNT(amount_sold) function 이존재하지않습니다. 따라서 SLIDE 에서소개되는 Mview 를 FAST Refresh 방식으로생성했을경우해당 Base Table 에는오직 INSERT 만이존재해야하는 Mview 입니다. 따라서전체적인 DML 에대하여 Refresh 를해야하는상황에서 COMPLETE Refresh 방식의 Mview 를생성한예를보여주고있습니다.
Materialized Views with Aggregates Example CREATE MATERIALIZED VIEW SUM_sales REFRESH FAST ON COMMIT AS Select s.prod_id, s.time_id, COUNT(*) AS COUNT_grp, SUM(s.amount_sold) AS SUM_dollar_sales, COUNT(s.amount_sold) AS COUNT_dollar_sales, SUM(s.quantity_sold) AS SUM_quantity_sales, COUNT(s.quantity_sold) AS COUNT_quantity_sales FROM sales s GROUP BY s.prod_id, s.time_id; 18 본 SLIDE 는이전에언급되었던있습니다. FAST Refresh 방식과동일한구조의 Mview 를소개하고 단지 Refresh MODE 로 ON DEMAND 만지원되는것이아니며, ON COMMIT MODE 로생성이가능한예를보여주고있습니다. REFRESH FAST 의제약조건을다시확인하자면 1. GROUP BY절이가지는 Column s.prod_id, s.time_id 를모두 Select List 에포함하고 있으며, 2. COUNT(*) 이 Select List 에존재하며, 3. SUM(s.amount_sold) 와함께 COUNT(a.amount_sold) 가존재하며, 4. SUM(s.quantity_sold) 와함께 COUNT(a.quantity_sold) 가존재합니다. 따라서집합연산을가지는 Mview 에대한 FAST Refresh 방식에모두부합이됩니다.
Restrictions on Fast Refresh on Materialized Views with Joins Only All restrictions from General Restrictions on Fast Refresh GROUP BY 절을가질수없다. Select List 에모든 Table 의 Rowid 가반드시기술이되어야한다. Mview log 는반드시 Rowid 를이용하여생성이되어야한다. 만약 outer Join 이있다면 inner Table 에 Unique Constraint 가존재해야하며 Where 절을가질수없다. 19 다음으로 Join 만을가지는 MIVEW 에대해서소개를하도록하겠습니다. 본 SLIDE 는 Join 만을가지는 Mview 를 FAST Refresh 방식으로생성하고자하는경우의제약사항에대해서소개를하고있습니다. 1. FAST Refresh 의일반적인사항에부합해야하며, 2. GROUP BY 절을가질수없습니다. 3. Select List 에는 Join 되는모든 Table의 Rowid 가반드시존재해야하며, 4. Mvew Log 는반드시 Rowid 를이용하여생성이되어야합니다. 5. 만약 Outer Join 이있다면 Inner Table에 Unique Constraint 가존재해야하며 Where 절을가질수없습니다.
Materialized Views Containing Only Joins Example CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON times WITH ROWID; CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID; CREATE MATERIALIZED VIEW detail_sales_mv REFRESH FAST AS Select s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid", c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id FROM sales s, times t, customers c WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+); 20 본 SLIDE 는 FAST Refresh 방식으로 Join 만을가지는 Mview 에대한예를소개하고있습니다. 먼저 Mview Log 는 Rowid 를이용하여생성이되었으며, 1. Mview 는관련된 Table 에대한 Rowid 를가지고있습니다. 2. GROUP BY 절이포함되지않았습니다. 따라서 Join 절을가지는 Mview 의 FAST Refresh 방식의제약사항에부합됩니다.
Nested Materialized Views SUM_sales_cust_time Join_sales_cust_time Customers Sales Times 21 본 SLIDE 는여러 Table 을이용하여 Join 만을가지는 Mview 를생성하고이 Mview 를 Reference 하여 Mview Log 를생성하여집합연산을하는 Mview 를생성하는 Nested Mview 의구조를보여주고있습니다. 이는 Join 이미리수행되어있는 Mview 에대하여집합연산을가지는 Mview 를생성함으로인해많은 COST 를요구하는 Join 및집합연산을 Mview 로생성하여 Storage 에저장함으로인해 Performance 향상을기대할수있는모델입니다.
Nested Materialized Views (1) /* create the materialized view logs */ CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID; CREATE MATERIALIZED VIEW LOG ON times WITH ROWID; /*create materialized view Join_sales_cust_time as fast Refreshable at COMMIT time */ CREATE MATERIALIZED VIEW Join_sales_cust_time REFRESH FAST ON COMMIT AS Select c.cust_id, c.cust_last_name, s.amount_sold, t.time_id, t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid FROM sales s, customers c, times t WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id; 22 먼저 Join 만을가지는 Mview 를다음과같이생성합니다. 이구조는이전에소개되었던것과같은구조를가지고있습니다.
Nested Materialized Views (2) /* create materialized view log on Join_sales_cust_time */ CREATE MATERIALIZED VIEW LOG ON Join_sales_cust_time WITH ROWID (cust_name, day_number_in_week, amount_sold) INCLUDING NEW VALUES; /* create the single-table aggregate materialized view SUM_sales_cust_time on Join_sales_cust_time as fast Refreshable at COMMIT time */ CREATE MATERIALIZED VIEW SUM_sales_cust_time REFRESH FAST ON COMMIT AS Select COUNT(*) cnt_all, SUM(amount_sold) SUM_sales, COUNT(amount_sold) cnt_sales, cust_last_name, day_number_in_week FROM Join_sales_cust_time GROUP BY cust_last_name, day_number_in_week; 23 이후 Join 만을가지는 Mview 에다시 Mview Log 를생성하게됩니다. Mview Log 를생성하는구절에서는 Rowid 와 INCLUDING NEW VALUES 를포함하고있으며 Nested Mview 로생성될 Reference Column 을모두포함하고있습니다. 이후 Mview 를생성하는구조로되어있습니다.
Query Rewrite Query is Rewriten Generate Plan Mview Query Compare plan cost And Pick the Best Query Results Generate Plan 24 본 SLIDE 는 Query Rewrite 의기능을그림으로설명하고있습니다. 사용자가 SQL 문장을실행하게되면이는해당 SQL 의 Plan 이생성되게되며, 또한 Mview 가있는경우해당 Mview 에대한 Query Rewrite 를실행하게됩니다. 이후사용자가실행한 SQL 문장에대한 Plan 과 Rewrite 된 SQL 문장의 Plan 에대한 Cost 를비교하여 Optimizer 는보다 Cost 가작은 Plan 을선택하여 Query 결과를 Return 하게됩니다.
Parameters for Query Rewrite OPTIMIZER_MODE ALL_ROWS (default), FIRST_ROWS, or CHOOSE Query_Rewrite_ENABLED TRUE (default), Query_Rewrite_INTEGRITY STALE_TOLERATED, TRUSTED, or ENFORCED(default) 25 본 SLIDE 에서는 Query Rewrite 를하는경우 Setting 되는 Initialization Parameter 를소개하고있습니다. Setting 되어야할 Parameter 는다음과같습니다. OPTIMIZER_MODE : All_rows 나 First_rows 로지정을하거나 CHOOSE 로지정이되어야하며통계치정보를가지고있어야합니다. Query_Rewrite_ENABLED : TRUE Query_Rewrite_INTEGRITY TRUSTED : 모든 Mview 의 Data 가 Base Table 과일치한다고판단함. ENFORCED (default) : Base Table 과의 Consistent 를 Check. STALE_TOLERATED : Base Table 과 Consistent 하지않는 View 에대해서도 Query Rewrite 를허용.
Privilege for Query Rewrite GRANT Query Rewrite 자기자신의 Mview 에대해서 Query Rewrite 권한을제공 GRANT GLOBAL Query Rewrite Mview 에서 reference 하는 Object 가자신의 Object 가아니더라도 Query Rewrite 권한을제공 26 본 SLIDE 에서는 Query Rewrite 를수행하고자할경우필요한권한에대해서소개를하고있습니다. 권한으로는 Query Rewrite 와 GLOBAL Query Rewrite 가있습니다. Query Rewrite 권한은자기자신의 Mview 및 Base Table 에대한 Query Rewrite 할수있는권한이며, GLOBAL Query Rewrite 권한은자기자신의 Mview 및 Base Table 뿐만아니라 Reference 하는 Object 가자신의 Object 가아니더라도 Query Rewrite를할수있는권한입니다. 일반적으로 Query Rewrite 권한을부여시같이부여하는경우가많습니다.
Example of Query Rewrite TEST: SQL> Select s.prod_id, s.time_id, 2 COUNT(*) AS COUNT_grp, 3 SUM(s.amount_sold) AS SUM_dollar_sales, 4 COUNT(s.amount_sold) AS COUNT_dollar_sales, 5 SUM(s.quantity_sold) AS SUM_quantity_sales, 6 COUNT(s.quantity_sold) AS COUNT_quantity_sales 7 FROM sales s 8 GROUP BY s.prod_id, s.time_id ; Execution Plan ---------------------------------------------------------- 0 Select STATEMENT Optimizer=CHOOSE (Cost=760 Card=319489 Byte s=27795543) 1 0 Table ACCESS* (FULL) OF 'SUM_SALES' (Cost=760 Card=319489 :Q134000 Bytes=27795543) 27 본 SLIDE 는 Query Rewrite 의예제를보고있습니다. Select 문장은 Mview 를생성시의문장으로써같은문장을수행하였을경우해당 Table 에대해서 FULL Scan 을하여많은비용을들이는형태로 Optimizer 가동작하지않고 Mview 의 Table 을 FULL scan 하는형태로 Plan 을선택하여보다적은비용으로같은결과를얻어낼수있습니다.
Example of Query Rewrite SQL> Select s.prod_id, s.time_id, 2 COUNT(*) AS COUNT_grp, 3 SUM(s.amount_sold) AS SUM_dollar_sales, 4 COUNT(s.amount_sold) AS COUNT_dollar_sales, 5 SUM(s.quantity_sold) AS SUM_quantity_sales, 6 COUNT(s.quantity_sold) AS COUNT_quantity_sales 7 FROM sales s 8 GROUP BY s.prod_id, s.time_id 9 having SUM(s.amount_sold) > 1000 ; Execution Plan ---------------------------------------------------------- 0 Select STATEMENT Optimizer=CHOOSE (Cost=760 Card=15974 Bytes =1389738) 1 0 Table ACCESS* (FULL) OF 'SUM_SALES' (Cost=760 Card=15974 B :Q132000 ytes=1389738) 28 다음의예제는 Mview 의 Sql 문장에서 Having 절을추가하여조건을부여한결과입니다. 이역시 Optimizer 가 Base Table 을 Access 하지않고 Mview 를 Access 하여해당결과를 Return 하는것을볼수있습니다.
Example of Query Rewrite SQL> Select s.prod_id, 2 SUM(s.amount_sold) AS SUM_dollar_sales 3 FROM sales s 4 GROUP BY s.prod_id 5 having SUM(s.amount_sold) > 1000 ; Execution Plan ---------------------------------------------------------- 0 Select STATEMENT Optimizer=CHOOSE (Cost=1179 Card=319489 Byt es=8306714) 1 0 FILTER* :Q146001 2 1 SORT* (GROUP BY) (Cost=1179 Card=319489 Bytes=8306714) :Q146001 3 2 SORT* (GROUP BY) (Cost=1179 Card=319489 Bytes=8306714) :Q146000 4 3 Table ACCESS* (FULL) OF 'SUM_SALES' (Cost=760 Card=3 :Q146000 19489 Bytes=8306714) 29 다음의결과는 Mview 내의모든 Column 을조회하지않고특정 Field 만을조회하는결과로서역시같은형태로 Optimizer 가 Mview 의 Query Rewrite 기능을사용하는결과를보이고있습니다.
HINT For Query Rewrite SQL> set autotrace trace exp SQL> Select /*+ NOREWRITE*/ s.prod_id, s.time_id, 2 COUNT(*) AS COUNT_grp, 3 SUM(s.amount_sold) AS SUM_dollar_sales, 4 COUNT(s.amount_sold) AS COUNT_dollar_sales, 5 SUM(s.quantity_sold) AS SUM_quantity_sales, 6 COUNT(s.quantity_sold) AS COUNT_quantity_sales 7 FROM sales s 8 GROUP BY s.prod_id, s.time_id ; Execution Plan ---------------------------------------------------------- 0 Select STATEMENT Optimizer=CHOOSE (Cost=29289 Card=1016271 B ytes=20325420) 1 0 PARTITION RANGE (ALL) 2 1 SORT (GROUP BY) (Cost=29289 Card=1016271 Bytes=20325420) 3 2 Table ACCESS (FULL) OF 'SALES' (Cost=1973 Card=1016271 Bytes=20325420) 30 다음의내용은 Query Rewrite 에대한 HINT 를사용하는예를보여주고있습니다. Query Rewrite HINT 로는 Rewrite NOREWRITE 가있습니다. Rewrite Hint 는지정한 Mview 에대해서 Query Rewrite 기능을사용하는 HINT 이며, 예제로보여주고있는내용은해당 SQL 문장이 Query Rewrite 기능을이용하여 PLAN 을풀어가지만, NOWRITE HINT 를이용하여 Mview 를 Access 하지않고 Base Table 을 Access 하는형태의 PLAN을선택하는결과를보여주고있습니다.
Restrictions of Query Rewrite 만약 Local Table 과 Remote Table 이있다면, Local Table 에대해서만 Rewrite 기능이사용됨. Non-sys user 의 Detail Table 과 Mview 에대해서만사용됨. 먄약 Mview 에 GROUP BY 절이사용되었다면관련된 column 및표현식은반드시 Select List 에존재해야함. AVG(AVG(x)) 및 AVG(x) + AVG(y) 등의연산은허용이되지않음. CONNECT BY 절은허용되지않음. 31 본 Slide 에서는 Query Rewrite 의기능의제약사항에대해서보여주고있습니다. 1. Local Table 과 Remote Table 이관련되어있다면, Remote Table 에대해서는 Query Rewrite 기능이적용되지않으며, Local Table 에대해서만 Rewrite 기능을적용하게됩니다. 2. SYS User 의 Mview 나 Detail Table 에대해서는 Query Rewrite 기능이적용되지않습니다. 3. 만약 Mview 에 GROUP By 절이사용되었다면관련되어있는 Column 이나, 표현식은반드시수행하는 SQL 문장에포함이되어있어야합니다. 4. AVG ( AVG (x)) 및 AVG(x) + AVG (y) 등의연산에는 Query Rewrite 기능이적용되지않습니다. 5. CONNECT BY 절은허용되지않습니다.
DBMS_MVIEW.EXPLAIN_MVIEW FAST Refresh 및 Rewrite 가가능한지확인. Mv_capabilities_Table 에기록한다. $OH/rdbms/admin/utlxmv.sql : MV_CAPABILITIES_Table 생성 DBMS_MVIEW.EXPALIN_MVIEW ( MV_NAME ) Select mvname, capability_name, possible FROM mv_capabilities_table ; MVNAME CAPABILITY_NAME POSSIBLE ------------------------------ ----------------------------------------------- ---------------- << truncated... >> SUM_SALES Refresh_FAST Y SUM_SALES Rewrite Y SUM_SALES Refresh_FAST_AFTER_INSERT Y SUM_SALES Rewrite_FULL_TEXT_MATCH Y SUM_SALES Rewrite_PARTIAL_TEXT_MATCH Y SUM_SALES Rewrite_GENERAL Y 32 다음으로 Mview 에대해서 Fast Refresh 가가능한지혹은 Query Rewrite 기능이사용가능한지를알아볼수있는DBMS_MVIEW.EXPLAIN_MVIEW 에대해서알아보도록하겠습니다. EXPLAIN_MVIEW 를수행하게되면해당정보는 MV_CAPABILITIES_Table 에기록이됩니다. 따라서 MV_CAPABILITIES_Table 을생성할필요가있습니다. MV_CAPABILITIES_Table 을생성하기위한 Script 는 utlxmv.sql 을수행해주어야합니다. Slide 에보여주는결과는 Query Rewrite 뿐만아니라 Fast Refresh 까지가능하다는것을보여주고있습니다.
DBMS_MVIEW.EXPLAIN_MVIEW CREATE MATERIALIZED VIEW sal_dept BUILD IMMEDIATE REFRESH FAST ON DEMAND ENABLE Query Rewrite AS Select a.deptno, SUM(a.sal) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY a.deptno; SQL> Select capability_name,possible from mv_capabilities_table ; CAPABILITY_NAME POSSIBLE -------------------------------------------------------- ----------------------- Refresh_FAST_AFTER_INSERT Y Refresh_FAST_AFTER_ONETAB_DML N Refresh_FAST_AFTER_ONETAB_DML N Refresh_FAST_AFTER_ANY_DML N 33 예를들어다음과같은 Mview 가있다고가정을합니다. CREATE MATERIALIZED VIEW sal_dept BUILD IMMEDIATE REFRESH FAST ON DEMAND ENABLE Query Rewrite AS Select a.deptno, SUM(a.sal) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY a.deptno; 이경우는 FAST Refresh 의제약조건중 Select List 에 COUNT(*) 절이포함되지않으며, COUNT(a.sal) 절이포함되어있지않기때문에이는 Only Insert 에만 Fast Refresh 가되는 Mview 형태임을알수있으며, Query Rewrite 기능을사용할수있음을알수있습니다.
DBMS_MVIEW.EXPLAIN_REWRITE 관련 Query 가 Rewrite 기능을사용가능한지확인 Rewrite_Table 에기록한다. $OH/rdbms/admin/utlxrw.sql : Rewrite_Table 생성 SQL> Select message from Rewrite_Table order by sequence; MESSAGE ---------------------------------------------------------------------------------------------------- QSM-01009: materialized view, SUM_SALES, matched Query text 34 본 SLIDE 에서보여주는 DBMS_MVIEW.EXPLAIN_REWRITE package 는관련 Query 가특정 Mview 에대하여 Query Rewrite 기능을사용가능한지를알아보는 Package 입니다. 이는 Rewrite_Table 에정보를기록하며, Rewrite_Table 을생성하기위해서는 utlxrw.sql 을수행하여야합니다. 다음의결과는 SUM_SALES Mview 의 SQL 문장과같으며, Query Rewrite 를사용가능하다는것을보여주고있습니다. 만약이결과중 Query Rewrite 를사용할수없다는결과가나온다면, 10g 에서제공되는 DBMS_ADVISOR.TUNE_MVIEW 를이용하여해당 Mview 를 TUNING 할수있는정보를얻어낼수있습니다.
DBMS_ADVISOR.TUNE_MVIEW 10g 에서소개. Privilige : advisor USER_TUNE_MVIEW set autotrace off declare task_cust_mv VARCHAR2(20):= 'tune_cust_mv'; begin DBMS_ADVISOR.TUNE_MVIEW(task_cust_mv, 'CREATE MATERIALIZED VIEW cust_mv ENABLE Query Rewrite AS Select s.prod_id, s.cust_id, SUM(s.amount_sold) SUM_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id GROUP BY s.prod_id, s.cust_id' ); end; / 35 본 SLIDE 는 DBMS_ADVISOR.TUNE_MVIEW 에대해서소개하고있습니다. DBMS_ADVISOR.TUNE_MVIEW Package 는 10g 에서소개가되었으며, 해당 Package 를사용하기위해서는 ADVISOR 권한이필요합니다. 예제로위의 SILDE 와같이 Mview 를생성하고자하는경우에대해서 TUNE_MVIEW 를수행하게되면 TUNING 된정보를 USER_TUNE_MVIEW 에결과를저장하게됩니다. 결과로는 Mview 뿐만아니라 Mview Log 에대한정보까지함께보여주게됩니다.
USER_TUNE_MVIEW SQL> Select * from user_tune_mview ; TASK_NAME ACTION_ID SCRIPT_TYPE STATEMENT -------------------------------------------------------------------------------- tune_cust_mv 3 IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES tune_cust_mv 4 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD rowid, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES tune_cust_mv 5 IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES tune_cust_mv 6 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD rowid, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES tune_cust_mv 7 IMPLEMENTATION CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID ENABLE Query Rewrite AS Select SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES,SH.CUSTOMERS WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID tune_cust_mv 8 UNDO DROP MATERIALIZED VIEW SH.CUST_MV 36 본 SLIDE 에서는 DBMS_ADVISOR.TUNE_MVIEW 를수행후 USER_TUNE_MVIEW 의결과를보여주고있습니다. 이예제에서확인할수있는사항은현재해당 Mview 에대한 Mview Log 가생성이되어있지않음을보여주고있으며, 집합연산을하는 Mview 를생성하는내용이므로 Mview Log 를 Rowid Option 과함께 Column 정보및 INCLUDING NEW VALUES 를사용하여 Mview Log 를생성해주는것을 Guide 하고있으며, Mview 를생성하는 Script 를제공하고있습니다. 이기능을이용하게되면제약사항이많은 Mview 를보다효과적으로생성할수있습니다.
After Tuning Mview SQL> set autotrace off declare task_cust_mv VARCHAR2(20):= 'tune_cust_mv'; SQL> 2 begin 3 DBMS_ADVISOR.TUNE_MVIEW(task_cust_mv, 4 'CREATE MATERIALIZED VIEW SH.CUST_MV 5 REFRESH FAST WITH ROWID 6 ENABLE Query Rewrite 7 AS 8 Select SH.SALES.PROD_ID a1, SH.CUSTOMERS.CUST_ID a2, 9 SUM(SH.SALES.AMOUNT_SOLD) c1, COUNT(SH.SALES.AMOUNT_SOLD) c2, 10 COUNT(*) c3 11 FROM SH.SALES, SH.CUSTOMERS 12 WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID 13 GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID' ); 14 end; 15 / 37 다음의내용은 USER_TUNE_MVIEW 의결과를바탕으로 Mview Log 을생성한이후다음과같이하고 Tuning 된 Mview Script 를이용하여 DBMS_ADVISOR.TUNE_MVIEW 를실행하게되면이미 OPTIMAL 하다는다음과같은 MESSAGE 를받게됩니다. 이는 Error message 가아니며, 단지 Message 입니다.
Result After Tuning Mview 해당 Mview statement 는이미 tuning 이되었음을알리는 message declare task_cust_mv VARCHAR2(20):= 'tune_cust_mv'; * ERROR at line 1: ORA-13600: error encountered in Advisor QSM-03116: The materialized view is already optimal and cannot be tuned any further ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202 ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1042 ORA-06512: at "SYS.DBMS_ADVISOR", line 754 ORA-06512: at line 3 38 본 SLIDE 는이미 TUNING 된 Mview 를 DBMS_ADVISOR 를이용하여 Tuning 하고자하는경우보여주는 Message 입니다. QSM-3116 에서이는이미 OPTIMAL 한 Tuning 된값이며, 더이상 Tuning 할수없다는 Message 를보여주고있습니다.
Truobleshooting Mview log 가너무커진경우 원인 ) Refresh 작업이너무오랫동안진행되지못했을때 Replication 환경에서 network failure 로인해 Refresh 가안되었을경우 Replication 환경에서 Mview 가 drop 되었지만 Master site 에반영이되지않는경우 SOLUTION) Refresh 작업을모두수행하여 Mview log 를 purge 한다. Extent 가너무많이발생한경우에는해당 Mview log 를 truncate 해주어 FULL Table scan 시적은 block 을 access 하게한다. 39 본 SLIDE 에서는 Mview 를운영중 Mview Log 가너무커진경우에대한원인과대처방법에대해서소개하고있습니다. Mview Log 는 Mview 가 Refresh 가정상적으로수행한경우 Purge 가되게됩니다. 하지만 Mview 를너무오랫동안 Purge 하지않게되면 Mview Log 는 Mview 에반영되지않는정보를 Purge 할때까지저장하게됩니다. 이의원인으로는 Manual 하게 Mview 에대한 Refresh 작업이이루어지지않았을경우, Replication 환경에서 Network 문제로인하여통신이두절되었을경우가있습니다. 이와같은경우 Manual 하게 Refresh 를해주거나 Network 문제를해결하여 Mview Log 를 Purge 해주어야합니다. 하지만너무많은 Extent 가발생하게되면 Refresh 시에 Mview Log를 FULL scan 함에따라 Performance 에영향을줄수가있습니다. 따라서너무많은 Extent 가발생한경우에는해당 Mview Log 를 Truncate 하여 Extent 수를줄여줌에따라 Performance 를향상시킬수있습니다.
Truobleshooting Mview log Table 의 extent 가너무많아 Refresh Performance 가저하될경우 LOCK Table scott.emp IN EXCLUSIVE MODE; CREATE Table scott.templog AS Select * FROM scott.mlog$_emp; TRUNCATE scott.mlog$_emp; INSERT INTO scott.mlog$_emp Select * FROM scott.templog; DROP Table scott.templog; Mview log 를수동으로 purge 하는방법 DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (SNAPOWNER => 'SCOTT', SNAPNAME => 'EMP', SNAPSITE => 'SNAP_SITE') DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT 40 본 SLIDE 에서는이전 SLIDE 에서언급되었던 Mview Log 를 Truncate 하는방법을언급하고있습니다. 1. LOCK Table scott.emp IN EXCLUSIVE MODE; 2. CREATE Table scott.templog AS Select * FROM scott.mlog$_emp; 3. TRUNCATE scott.mlog$_emp; 4. INSERT INTO scott.mlog$_emp Select * FROM scott.templog; 5. DROP Table scott.templog; 와같은방법으로 Mview Log 를 Truncate 할수있습니다. 그리고 Mview 가 Drop 되었을시 Master Site 에반영이되지않았을경우 DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (SNAPOWNER => 'SCOTT', SNAPNAME => 'EMP', SNAPSITE => 'SNAP_SITE') DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT(SNAPOWNER => 'SCOTT', SNAPNAME => 'EMP', SNAPSITE => 'SNAP_SITE') Package 를이용하여 Mview Log Purge 및 Master Site 에등록된 Mview 를 Unregister 할수있습니다.
ORA-32314 Troubleshooting REFRESH FAST of "SCOTT"."DEPT_SAL" unsupported after deletes/updates 원인 ) insert 가아닌 operation 이수행됨 SOLUTION) REFRESH COMPLETE ORA-12033 cannot use filter columns from materialized view log on "SCOTT"."EMP 원인 ) Mview 에서 reference 하는 column 이 Mview log 에포함이되지않은경우 SOLUTION ) Mview log 에 Mview 가 reference 하는 column 을추가. 41 다음의 SLIDE 는 Mview Refresh 시에 ORA-32314 Error 가발생하는경우로 Insert-only REFRESH FAST 를지원하는 Mview 에대한 Base Table 에 Insert 가아닌 Update, Delete 등의 Operation 이수행된경우에발생할수있습니다. 이에대한 Solution 으로는 Complete 로 Refresh 해주는방법이있습니다. ORA-12033 이발생하는경우는총합을가지는 Mview 생성시 Reference 되는 Column이 Mview Log 에포함되지않았을경우발생하는 Error 입니다. 이에대한 Solution 으로는 Mview Log 를관련된 Column 을추가하여생성해주어야합니다.
ORA-12015 Troubleshooting cannot create a fast Refresh materialized view from a complex Query 원인 ) REFRESH FAST restriction 에위배되는경우 SOLUTION) REFRESH COMPLETE ORA-23413 Table "SCOTT"."EMP" does not have a materialized view log 원인 ) Mview log 가없는경우 SOLUTION) recreate Mview log and compelte Refresh Mview 42 다음의 SLIDE 는 Mview 를 REFRESH FAST Mode 로생성시 ORA-12015 Error 가발생한경우입니다. 이경우는 REFRESH FAST Restriction 을위배한경우에발생하게되며이는 REFRESH COMPLETE 형태로 Mview 를생성해야합니다. 운영중이던 Mview 를 Refresh 하는상황에서 ORA-23413 Error 가발생하는경우는 Mview 가 Reference 하는 Mview Log 가삭제되었기때문입니다. 이는 Mview Log 를생성한후 Mview 를 REFRESH COMPLETE 를수행해야합니다. 또는 Mview 를다시생성하셔야합니다.
Troubleshooting ORA-12052 cannot fast Refresh materialized view SH.DETAIL_SALES_MV 원인 ) rowid 가포함되지않은경우 SOLUTION) Mview 생성시 base Table 의 rowid 를 Select List 에넣어주어야한다. 또는 REFRESH COMPLETE 로생성. ORA-12032 cannot use rowid column from materialized view log on "SH"."CUSTOMERS" 원인 ) Mview log 에 rowid 가없는경우 SOLUTION) recreate Mview log WITH ROWID and recreate Mview 43 다음의 SLIDE 는 Join 절을가지는 Mview 를 REFRESH FAST Mode 로생성시 ORA-12052 Error 가발생한경우입니다. 이경우는 Join 절을가지는 Mview 를생성시 REFRESH FAST Restriction 을위배한경우에발생하게되며이는 Mview 의 Select List 에 Rowid 를추가해주거나 REFRESH COMPLETE 형태로 Mview 를생성해야합니다. Join 절을가지는 Mview 를 REFRESH FAST Mode 로생성시 ORA-12032 Error 가발생한경우입니다. 이경우는 Join 절을가지는 Mview 를생성시 Mview Log 에서 Rowid 를참조할수없는경우에발생하게되며, 이는 Mview Log 생성시 WITH ROWID 형태로생성을해야합니다.
Reference Data Warehousing Concept Advanced Replication Concept NOTE : 76673.1 NOTE : 252727.1 NOTE : 236233.1 44 DW Warehousing Concept Advanced Replication Concept Note 76673.1 Title: What are Materialized Views? Note 252727.1 Title: Oracle10g: Using DBMS_ADVISOR.TUNE_MVIEW to Optimize Materialized View for Query Rewrite Note 236233.1 Title: Materialized View Refresh : Log Population and Purge