物化视图查询重写

botang

1. SQL> SELECT SUM(p.prod_list_price - p.prod_min_price) M1,
   
2.              COUNT(p.prod_category) M2, p.prod_category, sum(s.AMOUNT_SOLD)  FROM products p, sales s
   
3.              where s.prod_id=p.prod_id
   
4.              GROUP BY prod_category;  2    3    4  
   
5. 
   
6.         M1           M2 PROD_CATEGORY                                         SUM(S.AMOUNT_SOLD)
   
7. ---------- ---------- -------------------------------------------------- ------------------
   
8.          0     116267 Electronics                                                 14597515.6
   
9.          0        95509 Photo                                                         17961865.9
   
10.          0     286369 Peripherals and Accessories                                 31163988.4
    
11.          0     405341 Software/Other                                                 13834855.7
    
12.          0        15357 Hardware                                                         20647605.6
    
13. 
    
14. 
    
15. Execution Plan
    
16. ----------------------------------------------------------
    
17. Plan hash value: 2958490228
    
18. 
    
19. -------------------------------------------------------------------------------------
    
20. | Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
21. -------------------------------------------------------------------------------------
    
22. |   0 | SELECT STATEMENT             |            |          5 |        140 |          3   (0)| 00:00:01 |
    
23. |   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |          5 |        140 |          3   (0)| 00:00:01 |
    
24. -------------------------------------------------------------------------------------
    
25. 
    
26. Note
    
27. -----
    
28.    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
29. 
    
30. 
    
31. Statistics
    
32. ----------------------------------------------------------
    
33.          92  recursive calls
    
34.          23  db block gets
    
35.          78  consistent gets
    
36.           0  physical reads
    
37.        4136  redo size
    
38.         984  bytes sent via SQL*Net to client
    
39.         568  bytes received via SQL*Net from client
    
40.           2  SQL*Net roundtrips to/from client
    
41.           5  sorts (memory)
    
42.           0  sorts (disk)
    
43.           5  rows processed
    
44. 
    
45. SQL> alter session query_rewrite_enabled=false;
    
46. alter session query_rewrite_enabled=false
    
47.               *
    
48. ERROR at line 1:
    
49. ORA-00922: missing or invalid option
    
50. 
    
51. 
    
52. SQL> show parameter query
    
53. 
    
54. NAME                                     TYPE         VALUE
    
55. ------------------------------------ ----------- ------------------------------
    
56. inmemory_query                             string         ENABLE
    
57. multishard_query_data_consistency    string         strong
    
58. multishard_query_partial_results     string         not allowed
    
59. query_rewrite_enabled                     string         TRUE
    
60. query_rewrite_integrity              string         enforced
    
61. SQL> alter session set query_rewrite_enabled=false;
    
62. 
    
63. Session altered.
    
64. 
    
65. SQL> SELECT SUM(p.prod_list_price - p.prod_min_price) M1,
    
66.              COUNT(p.prod_category) M2, p.prod_category, sum(s.AMOUNT_SOLD)  FROM products p, sales s
    
67.              where s.prod_id=p.prod_id
    
68.              GROUP BY prod_category;  2    3    4  
    
69. 
    
70.         M1           M2 PROD_CATEGORY                                         SUM(S.AMOUNT_SOLD)
    
71. ---------- ---------- -------------------------------------------------- ------------------
    
72.          0     116267 Electronics                                                 14597515.6
    
73.          0        95509 Photo                                                         17961865.9
    
74.          0     286369 Peripherals and Accessories                                 31163988.4
    
75.          0     405341 Software/Other                                                 13834855.7
    
76.          0        15357 Hardware                                                         20647605.6
    
77. 
    
78. 
    
79. Execution Plan
    
80. ----------------------------------------------------------
    
81. Plan hash value: 3535171836
    
82. 
    
83. ----------------------------------------------------------------------------------------------------------
    
84. | Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time         | Pstart| Pstop |
    
85. ----------------------------------------------------------------------------------------------------------
    
86. |   0 | SELECT STATEMENT              |          |     5 |   200 |    59  (50)| 00:00:01 |         |         |
    
87. |   1 |  HASH GROUP BY                      |          |     5 |   200 |    59  (50)| 00:00:01 |         |         |
    
88. |*  2 |   HASH JOIN                      |          |   918K|    35M|    38  (22)| 00:00:01 |         |         |
    
89. |   3 |    TABLE ACCESS FULL              | PRODUCTS |    72 |  2232 |     3   (0)| 00:00:01 |         |         |
    
90. |   4 |    PARTITION RANGE ALL              |          |   918K|  8075K|    32  (16)| 00:00:01 |     1 |    28 |
    
91. |   5 |     TABLE ACCESS INMEMORY FULL| SALES         |   918K|  8075K|    32  (16)| 00:00:01 |     1 |    28 |
    
92. ----------------------------------------------------------------------------------------------------------
    
93. 
    
94. Predicate Information (identified by operation id):
    
95. ---------------------------------------------------
    
96. 
    
97.    2 - access("S"."PROD_ID"="P"."PROD_ID")
    
98. 
    
99. Note
    
100. -----
     
101.    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
     
102. 
     
103. 
     
104. Statistics
     
105. ----------------------------------------------------------
     
106.          19  recursive calls
     
107.           0  db block gets
     
108.          50  consistent gets
     
109.           0  physical reads
     
110.           0  redo size
     
111.         984  bytes sent via SQL*Net to client
     
112.         591  bytes received via SQL*Net from client
     
113.           2  SQL*Net roundtrips to/from client
     
114.           3  sorts (memory)
     
115.           0  sorts (disk)
     
116.           5  rows processed
     
117. 
     
118. SQL>
     

复制代码

1. SQL>  @?/rdbms/admin/utlxrw.sql;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> exec dbms_mview.explain_rewrite('SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category', 'PROD_MV');
   
6. 
   
7. PL/SQL procedure successfully completed.
   
8. 
   
9. SQL> select message from rewrite_table order by sequence;
   
10. 
    
11. MESSAGE
    
12. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
13. QSM-01150: query did not rewrite
    
14. QSM-01091: cost based optimizer found query rewrite is more expensive
    
15. QSM-01209: query rewritten with materialized view, PROD_MV, using text match algorithm
    
16. 
    
17. SQL> exec dbms_stats.gather_schema_stats('SH');
    
18. 
    
19. PL/SQL procedure successfully completed.
    

复制代码

1. declare
   
2. v_task  varchar2(32);
   
3. v_sqltext varchar2(4000);
   
4. begin
   
5. v_sqltext := ' CREATE MATERIALIZED VIEW sh.mv1 AS SELECT SUM(p.prod_list_price - p.prod_min_price) M1, COUNT(p.prod_category) M2, p.prod_category, sum(s.AMOUNT_SOLD)  FROM sh.products p, sh.sales s where s.prod_id=p.prod_id GROUP BY prod_category';
   
6. dbms_advisor.tune_mview(v_task,v_sqltext);
   
7. dbms_advisor.create_file(dbms_advisor.get_task_script(v_task),'DIR1','tunemv.sql');
   
8. end;
   
9. /

复制代码

1. Rem  SQL Access Advisor: Version 19.0.0.0.0 - Production
   
2. Rem
   
3. Rem  Username:        SYS
   
4. Rem  Task:            TASK_3753
   
5. Rem  Execution date:
   
6. Rem
   
7. 
   
8. CREATE MATERIALIZED VIEW LOG ON
   
9.     "SH"."SALES"
   
10.     WITH ROWID, SEQUENCE("PROD_ID","AMOUNT_SOLD")
    
11.     INCLUDING NEW VALUES;
    
12. 
    
13. ALTER MATERIALIZED VIEW LOG FORCE ON
    
14.     "SH"."SALES"
    
15.     ADD ROWID, SEQUENCE("PROD_ID","AMOUNT_SOLD")
    
16.     INCLUDING NEW VALUES;
    
17. 
    
18. CREATE MATERIALIZED VIEW LOG ON
    
19.     "SH"."PRODUCTS"
    
20.     WITH ROWID, SEQUENCE("PROD_ID","PROD_CATEGORY","PROD_LIST_PRICE","PROD_MIN_PRICE")
    
21.     INCLUDING NEW VALUES;
    
22. 
    
23. ALTER MATERIALIZED VIEW LOG FORCE ON
    
24.     "SH"."PRODUCTS"
    
25.     ADD ROWID, SEQUENCE("PROD_ID","PROD_CATEGORY","PROD_LIST_PRICE","PROD_MIN_PRICE")
    
26.     INCLUDING NEW VALUES;
    
27. 
    
28. CREATE MATERIALIZED VIEW "SH"."MV1"
    
29.     REFRESH FAST WITH ROWID
    
30.     DISABLE QUERY REWRITE
    
31.     AS SELECT "SH"."PRODUCTS"."PROD_CATEGORY" C1, COUNT("SH"."PRODUCTS"."PROD_CATEGORY")
    
32.        M1, SUM("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
    
33.        M2, COUNT("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
    
34.        M3, SUM("SH"."SALES"."AMOUNT_SOLD") M4, COUNT("SH"."SALES"."AMOUNT_SOLD")
    
35.        M5, COUNT(*) M6 FROM "SH"."PRODUCTS", "SH"."SALES" WHERE "SH"."SALES"."PROD_ID"
    
36.        = "SH"."PRODUCTS"."PROD_ID" GROUP BY "SH"."PRODUCTS"."PROD_CATEGORY";
    

复制代码

1. 
   
2.   begin
   
3.     dbms_mview.explain_mview('MV1');
   
4.   end;
   
5.   /

复制代码

1.   select  * from mv_capabilities_table;      

复制代码

1.    truncate table    mv_capabilities_table;
   
2.     SH    MV1    PCT    N                    1
   
3.     SH    MV1    REFRESH_COMPLETE    Y                    1002
   
4.     SH    MV1    REFRESH_FAST    Y                    2003
   
5.     SH    MV1    REWRITE    N                    3004
   
6.     SH    MV1    PCT_TABLE    N    SH.PRODUCTS    359    2068    relation is not a partitioned table    4005
   
7.     SH    MV1    PCT_TABLE    N    SH.SALES    376    2067    no partition key or PMARKER or join dependent expression in select list    4006
   
8.     SH    MV1    REFRESH_FAST_AFTER_INSERT    Y                    5007
   
9.     SH    MV1    REFRESH_FAST_AFTER_ONETAB_DML    Y                    6008
   
10.     SH    MV1    REFRESH_FAST_AFTER_ANY_DML    Y                    7009
    
11.     SH    MV1    REFRESH_FAST_PCT    N            2157    PCT is not possible on any of the detail tables in the materialized view    8010
    
12.     SH    MV1    REWRITE_FULL_TEXT_MATCH    N            2159    query rewrite is disabled on the materialized view    9011
    
13.     SH    MV1    REWRITE_PARTIAL_TEXT_MATCH    N            2159    query rewrite is disabled on the materialized view    10012
    
14.     SH    MV1    REWRITE_GENERAL    N            2159    query rewrite is disabled on the materialized view    11013
    
15.     SH    MV1    REWRITE_PCT    N            2158    general rewrite is not possible or PCT is not possible on any of the detail tables    12014
    
16.     SH    MV1    PCT_TABLE_REWRITE    N    SH.PRODUCTS    359    2068    relation is not a partitioned table    13015
    
17.     SH    MV1    PCT_TABLE_REWRITE    N    SH.SALES    376    2185    no partition key or PMARKER in select list    13016
    

复制代码