查询重写实验

本实验目标是实现查询重写。
假设有1个视图，是2张基表连接，每次查询该视图，都需要对2张表进行全表扫描再连接。
2张基本都没有涉及索引。
如果使用物化连接，开销更小。
利用查询重写，不改动sql文本内容的情况下，执行计划走物化视图。

查询重写需要成本更低才会走查询重写。

创建用户liaody：

CREATE USER LIAODY IDENTIFIED BY LIAODY;
ALTER USER LIAODY TABLESPACE LDY;

分配权限，需要有query rewrite和创建物化视图的权限。

GRANT CREATE SESSION TO LIAODY;
GRANT RESOURCE TO LIAODY;
GRANT QUERY REWRITE TO LIAODY;
grant create view to liaody;
grant create materialized view to liaody;

参数query_rewrite_enabled 需要是true。

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

创建2张表：

create table qrw01
(id number,
a1 char(10),
a2 char(10),
a3 CHAR(10),
a4 CHAR(10),
a5 CHAR(10),
other1 CHAR(10),
other2 CHAR(10),
other3 CHAR(10)
);

 

create table qrw02
(id number,
a1 CHAR(10),
a2 CHAR(10),
a3 CHAR(10),
a4 CHAR(10),
a5 CHAR(10),
b1 CHAR(10),
b2 CHAR(10),
b33 CHAR(10)
);

生成数据插入表

begin
for i in 1..100000 loop
insert into qrw01 values (trunc(dbms_random.value(0,50)),
dbms_random.string('u', 2),dbms_random.string('u', 2),
dbms_random.string('u', 2),dbms_random.string('u', 2),
dbms_random.string('u', 2),dbms_random.string('u', 2),
dbms_random.string('u', 2),dbms_random.string('u', 2));
if mod(i,1000)=0 then
commit;
end if;
end loop;
end;
/

declare
varsql varchar2(200);
CURSOR mycur is
select id,a1,a2,a3,a4,a5
from qrw01;
begin
for i in 1..5 loop
for varsql in mycur loop
insert into qrw02
values (varsql.id,varsql.a1,varsql.a2,varsql.a3,varsql.a4,varsql.a5,
dbms_random.string('u', 2),dbms_random.string('u', 2),dbms_random.string('u', 2));
end loop;
commit;
end loop;
end;
/

2张表的数据量：

SQL> SELECT COUNT(*) FROM QRW02;

  COUNT(*)
----------
    500000

SQL> SELECT COUNT(*) FROM QRW01;

  COUNT(*)
----------
    100000
   
   
创建一个普通视图：

create view v_qrw01 as
select q1.id,
q1.other1,q1.other2,q1.other3,q2.b1,q2.b2,q2.b33
from qrw01 q1,qrw02 q2
where q1.id=q2.id
and q1.a1=q2.a1
and q1.a2=q2.a2
and q1.a3=q2.a3
and q1.a4=q2.a4
and q1.a5=q2.a5;

该视图查出来的数据量有50w。

SQL> select count(*) from v_qrw01;

  COUNT(*)
----------
    500000
   

下面一条语句的开销，2张表的全表扫描：

SQL> select id,other1,other2,other3,b1,b2,b33 from liaody.v_qrw01 where id=14;

已选择9845行。

执行计划
----------------------------------------------------------
Plan hash value: 151944771

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    32 |  6976 |  1772   (1)| 00:00:22 |
|*  1 |  HASH JOIN         |       |    32 |  6976 |  1772   (1)| 00:00:22 |
|*  2 |   TABLE ACCESS FULL| QRW01 |  1990 |   211K|   304   (1)| 00:00:04 |
|*  3 |   TABLE ACCESS FULL| QRW02 |  8787 |   935K|  1467   (1)| 00:00:18 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("Q1"."ID"="Q2"."ID" AND "Q1"."A1"="Q2"."A1" AND
              "Q1"."A2"="Q2"."A2" AND "Q1"."A3"="Q2"."A3" AND "Q1"."A4"="Q2"."A4
" AND

              "Q1"."A5"="Q2"."A5")
   2 - filter("Q1"."ID"=14)
   3 - filter("Q2"."ID"=14)

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        179  recursive calls
          0  db block gets
       8864  consistent gets
          0  physical reads
          0  redo size
     782114  bytes sent via SQL*Net to client
       7601  bytes received via SQL*Net from client
        658  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       9845  rows processed

在2张表上创建物化视图：

create materialized view log on qrw01 with rowid;
create materialized view log on qrw02 with rowid;

创建物化视图，查询语句和前面视图的语句相同。

create materialized view qrw_mv01
refresh fast
with rowid
on commit
enable query rewrite
as
select q1.rowid q1rowid,q2.rowid q2rowid,q1.id,
q1.other1,q1.other2,q1.other3,q2.b1,q2.b2,q2.b33
from qrw01 q1,qrw02 q2
where q1.id=q2.id
and q1.a1=q2.a1
and q1.a2=q2.a2
and q1.a3=q2.a3
and q1.a4=q2.a4
and q1.a5=q2.a5;

对表进行分析：

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('liaody','qrw01');

PL/SQL 过程已成功完成。

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('liaody','qrw02');

PL/SQL 过程已成功完成。

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('liaody','qrw_mv01');

PL/SQL 过程已成功完成。

 

相同的sql，执行计划变为扫描物化视图。

SQL> select id,other1,other2,other3,b1,b2,b33 from liaody.v_qrw01 where id=14;

已选择9845行。

已用时间:  00: 00: 00.98

执行计划
----------------------------------------------------------
Plan hash value: 2499593068

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  9935 |   669K|  1492   (1)| 00:00:18 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| QRW_MV01 |  9935 |   669K|  1492   (1)| 00:00:18 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("QRW_MV01"."ID"=14)

统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
       7322  consistent gets
       3528  physical reads
          0  redo size
     781938  bytes sent via SQL*Net to client
       7601  bytes received via SQL*Net from client
        658  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9845  rows processed
      
      

create view v_qrw02 as
select q1.id,
q1.other1,q1.other2,q1.other3,q2.b1,q2.b2,q2.b33
from qrw01 q1,qrw02 q2
where q1.id=q2.id
and q1.a1=q2.a1
and q1.a2=q2.a2
and q1.a3=q2.a3
and q1.a4=q2.a4
and q1.a5=q2.a5
and q1.id=10;      

SQL> select count(*) from v_qrw02;

  COUNT(*)
----------
      9845
     
以下一条sql语句：
select id,other1,other2,other3,b1,b2,b33 from liaody.v_qrw02;

该sql语句走的是QRW_MV01物化视图：
SQL> select id,other1,other2,other3,b1,b2,b33 from liaody.v_qrw02;

已选择9845行。

已用时间:  00: 00: 01.37

执行计划
----------------------------------------------------------
Plan hash value: 2499593068

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          | 10417 |   701K|  1492   (1)| 00:00:18 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| QRW_MV01 | 10417 |   701K|  1492   (1)| 00:00:18 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("QRW_MV01"."ID"=10)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7324  consistent gets
       6655  physical reads
          0  redo size
     782070  bytes sent via SQL*Net to client
       7601  bytes received via SQL*Net from client
        658  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9845  rows processed

 

创建一个新的物化视图qrw_mv02：

create materialized view qrw_mv02
refresh fast
with rowid
on commit
enable query rewrite
as
select q1.rowid q1rowid,q2.rowid q2rowid,q1.id,
q1.other1,q1.other2,q1.other3,q2.b1,q2.b2,q2.b33
from qrw01 q1,qrw02 q2
where q1.id=q2.id
and q1.a1=q2.a1
and q1.a2=q2.a2
and q1.a3=q2.a3
and q1.a4=q2.a4
and q1.a5=q2.a5
and q1.id=10;

同一条sql语句走了物化视图qrw_mv02，该成本更低。

SQL> select id,other1,other2,other3,b1,b2,b33 from liaody.v_qrw02;

已选择9845行。

已用时间:  00: 00: 00.20

执行计划
----------------------------------------------------------
Plan hash value: 434718022

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  9600 |   796K|    33   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| QRW_MV02 |  9600 |   796K|    33   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
         42  recursive calls
          0  db block gets
        860  consistent gets
        132  physical reads
          0  redo size
     782279  bytes sent via SQL*Net to client
       7601  bytes received via SQL*Net from client
        658  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9845  rows processed