{"id":4762,"date":"2016-09-05T19:26:53","date_gmt":"2016-09-06T00:26:53","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=4762"},"modified":"2016-09-05T19:26:53","modified_gmt":"2016-09-06T00:26:53","slug":"oracle-materialized-view-and-query-rewrite","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2016\/09\/05\/oracle-materialized-view-and-query-rewrite\/","title":{"rendered":"Oracle materialized view and query rewrite"},"content":{"rendered":"

Just a simple example to show what it takes to get this to work…<\/p>\n

\r\nSQL> set lines 1000 trims on pages 100\r\nSQL> explain plan for select count(*) from member;\r\n\r\nExplained.\r\n\r\nSQL> select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 2991848243\r\n\r\n---------------------------------------------------------------------\r\n| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |        |     1 |   586K  (1)| 01:57:21 |\r\n|   1 |  SORT AGGREGATE    |        |     1 |            |          |\r\n|   2 |   TABLE ACCESS FULL| MEMBER |    90M|   586K  (1)| 01:57:21 |\r\n---------------------------------------------------------------------\r\n\r\n9 rows selected.\r\n\r\nSQL> create materialized view mvtest as select \/*+ parallel(x,16) *\/ count(*) from expanalytics.member x;\r\n\r\nMaterialized view created.\r\n\r\nSQL> explain plan for select count(*) from member;\r\n\r\nExplained.\r\n\r\nSQL> select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 2991848243\r\n\r\n---------------------------------------------------------------------\r\n| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |        |     1 |   586K  (1)| 01:57:21 |\r\n|   1 |  SORT AGGREGATE    |        |     1 |            |          |\r\n|   2 |   TABLE ACCESS FULL| MEMBER |    90M|   586K  (1)| 01:57:21 |\r\n---------------------------------------------------------------------\r\n\r\n9 rows selected.\r\n\r\nSQL> alter materialized view mvtest enable query rewrite;\r\n\r\nMaterialized view altered.\r\n\r\nSQL> explain plan for select count(*) from member;\r\n\r\nExplained.\r\n\r\nSQL> select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 3317803245\r\n\r\n----------------------------------------------------------------------------------------\r\n| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |\r\n----------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT              |        |     1 |    13 |     3   (0)| 00:00:01 |\r\n|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |          |\r\n|   2 |   MAT_VIEW REWRITE ACCESS FULL| MVTEST |     1 |    13 |     3   (0)| 00:00:01 |\r\n----------------------------------------------------------------------------------------\r\n\r\nNote\r\n-----\r\n   - dynamic sampling used for this statement (level=2)\r\n\r\n13 rows selected.\r\n\r\nSQL> set timing on\r\nSQL> select count(*) from member;\r\n\r\n  COUNT(*)\r\n----------\r\n  90881231\r\n\r\nElapsed: 00:00:00.00\r\nSQL>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Just a simple example to show what it takes to get this to work… SQL> set lines 1000 trims on pages 100 SQL> explain plan for select count(*) from member; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ——————————————————————————– Plan hash…<\/p>\n
Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[22,11],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/4762"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=4762"}],"version-history":[{"count":3,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/4762\/revisions"}],"predecessor-version":[{"id":5687,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/4762\/revisions\/5687"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=4762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=4762"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=4762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}