物化视图查询重写
SQL> SELECT SUM(p.prod_list_price - p.prod_min_price) M1,COUNT(p.prod_category) M2, p.prod_category, sum(s.AMOUNT_SOLD)FROM products p, sales s
where s.prod_id=p.prod_id
GROUP BY prod_category;2 3 4
M1 M2 PROD_CATEGORY SUM(S.AMOUNT_SOLD)
---------- ---------- -------------------------------------------------- ------------------
0 116267 Electronics 14597515.6
0 95509 Photo 17961865.9
0 286369 Peripherals and Accessories 31163988.4
0 405341 Software/Other 13834855.7
0 15357 Hardware 20647605.6
Execution Plan
----------------------------------------------------------
Plan hash value: 2958490228
-------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 140 | 3 (0)| 00:00:01 |
| 1 |MAT_VIEW REWRITE ACCESS FULL| MV1| 5 | 140 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
92recursive calls
23db block gets
78consistent gets
0physical reads
4136redo size
984bytes sent via SQL*Net to client
568bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
5sorts (memory)
0sorts (disk)
5rows processed
SQL> alter session query_rewrite_enabled=false;
alter session query_rewrite_enabled=false
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_query string ENABLE
multishard_query_data_consistency string strong
multishard_query_partial_results string not allowed
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
SQL> alter session set query_rewrite_enabled=false;
Session altered.
SQL> SELECT SUM(p.prod_list_price - p.prod_min_price) M1,
COUNT(p.prod_category) M2, p.prod_category, sum(s.AMOUNT_SOLD)FROM products p, sales s
where s.prod_id=p.prod_id
GROUP BY prod_category;2 3 4
M1 M2 PROD_CATEGORY SUM(S.AMOUNT_SOLD)
---------- ---------- -------------------------------------------------- ------------------
0 116267 Electronics 14597515.6
0 95509 Photo 17961865.9
0 286369 Peripherals and Accessories 31163988.4
0 405341 Software/Other 13834855.7
0 15357 Hardware 20647605.6
Execution Plan
----------------------------------------------------------
Plan hash value: 3535171836
----------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 200 | 59(50)| 00:00:01 | | |
| 1 |HASH GROUP BY | | 5 | 200 | 59(50)| 00:00:01 | | |
|*2 | HASH JOIN | | 918K| 35M| 38(22)| 00:00:01 | | |
| 3 | TABLE ACCESS FULL | PRODUCTS | 72 |2232 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 918K|8075K| 32(16)| 00:00:01 | 1 | 28 |
| 5 | TABLE ACCESS INMEMORY FULL| SALES | 918K|8075K| 32(16)| 00:00:01 | 1 | 28 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROD_ID"="P"."PROD_ID")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
19recursive calls
0db block gets
50consistent gets
0physical reads
0redo size
984bytes sent via SQL*Net to client
591bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
3sorts (memory)
0sorts (disk)
5rows processed
SQL>
SQL>@?/rdbms/admin/utlxrw.sql;
Table created.
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');
PL/SQL procedure successfully completed.
SQL> select message from rewrite_table order by sequence;
MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01091: cost based optimizer found query rewrite is more expensive
QSM-01209: query rewritten with materialized view, PROD_MV, using text match algorithm
SQL> exec dbms_stats.gather_schema_stats('SH');
PL/SQL procedure successfully completed.
declare
v_taskvarchar2(32);
v_sqltext varchar2(4000);
begin
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';
dbms_advisor.tune_mview(v_task,v_sqltext);
dbms_advisor.create_file(dbms_advisor.get_task_script(v_task),'DIR1','tunemv.sql');
end;
/
RemSQL Access Advisor: Version 19.0.0.0.0 - Production
Rem
RemUsername: SYS
RemTask: TASK_3753
RemExecution date:
Rem
CREATE MATERIALIZED VIEW LOG ON
"SH"."SALES"
WITH ROWID, SEQUENCE("PROD_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."SALES"
ADD ROWID, SEQUENCE("PROD_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"SH"."PRODUCTS"
WITH ROWID, SEQUENCE("PROD_ID","PROD_CATEGORY","PROD_LIST_PRICE","PROD_MIN_PRICE")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."PRODUCTS"
ADD ROWID, SEQUENCE("PROD_ID","PROD_CATEGORY","PROD_LIST_PRICE","PROD_MIN_PRICE")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW "SH"."MV1"
REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE
AS SELECT "SH"."PRODUCTS"."PROD_CATEGORY" C1, COUNT("SH"."PRODUCTS"."PROD_CATEGORY")
M1, SUM("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
M2, COUNT("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
M3, SUM("SH"."SALES"."AMOUNT_SOLD") M4, COUNT("SH"."SALES"."AMOUNT_SOLD")
M5, COUNT(*) M6 FROM "SH"."PRODUCTS", "SH"."SALES" WHERE "SH"."SALES"."PROD_ID"
= "SH"."PRODUCTS"."PROD_ID" GROUP BY "SH"."PRODUCTS"."PROD_CATEGORY";
begin
dbms_mview.explain_mview('MV1');
end;
/
select* from mv_capabilities_table;
truncate table mv_capabilities_table;
SH MV1 PCT N 1
SH MV1 REFRESH_COMPLETE Y 1002
SH MV1 REFRESH_FAST Y 2003
SH MV1 REWRITE N 3004
SH MV1 PCT_TABLE N SH.PRODUCTS 359 2068 relation is not a partitioned table 4005
SH MV1 PCT_TABLE N SH.SALES 376 2067 no partition key or PMARKER or join dependent expression in select list 4006
SH MV1 REFRESH_FAST_AFTER_INSERT Y 5007
SH MV1 REFRESH_FAST_AFTER_ONETAB_DML Y 6008
SH MV1 REFRESH_FAST_AFTER_ANY_DML Y 7009
SH MV1 REFRESH_FAST_PCT N 2157 PCT is not possible on any of the detail tables in the materialized view 8010
SH MV1 REWRITE_FULL_TEXT_MATCH N 2159 query rewrite is disabled on the materialized view 9011
SH MV1 REWRITE_PARTIAL_TEXT_MATCH N 2159 query rewrite is disabled on the materialized view 10012
SH MV1 REWRITE_GENERAL N 2159 query rewrite is disabled on the materialized view 11013
SH MV1 REWRITE_PCT N 2158 general rewrite is not possible or PCT is not possible on any of the detail tables 12014
SH MV1 PCT_TABLE_REWRITE N SH.PRODUCTS 359 2068 relation is not a partitioned table 13015
SH MV1 PCT_TABLE_REWRITE N SH.SALES 376 2185 no partition key or PMARKER in select list 13016
页:
[1]