物化视图试验

我用物化视图试验了一下，过程如下：

-------------------------------------------------------

-- 建表 a
create table a(
id        integer,
cityid    varchar2(3),
c1        varchar2(8),
c2        varchar2(8),
c3        varchar2(8),
c4        varchar2(8),
c5        varchar2(8),
c6        varchar2(8),
c7        varchar2(8),
c8        varchar2(8),
constraint pk_a primary key (id)
using index tablespace tvinfo_index
);

-- 建表 b
create table b(
id        integer,
name      varchar2(8),
c1        varchar2(8),
c2        varchar2(8),
c3        varchar2(8),
c4        varchar2(8),
c5        varchar2(8),
c6        varchar2(8),
c7        varchar2(8),
c8        varchar2(8),
constraint pk_b primary key (id)
using index tablespace tvinfo_index
);

-- 填数据到 a 表
declare
c varchar2(8) default 'XXXXXXXX';
begin
for x in 1..1000000 loop
if mod(x,1000)=1 then
insert /* +append */ into a values(x,'510',c,c,c,c,c,c,c,c);
else
insert /* +append */ into a values(x,substr(x,1,3),c,c,c,c,c,c,c,c);
end if;
end loop;

commit;
end;
/

-- 填数据到 b 表
insert /* +append */ into b value
select id,cityid||'city',c1,c2,c3,c4,c5,c6,c7,c8 from a ;

commit;

-- 建立物化视图 ab_mv
create materialized view ab_mv
tablespace tvinfo_temp
storage(buffer_pool keep)
build immediate
refresh on commit
enable query rewrite
as
select cityid from a,b where a.id=b.id;

-- 权限设置
grant create materialized view to xxxx;
alter session query_rewrite_enabled=true;
alter session query_rewrite_integrity=enforced;

-- 分析 a、b 表
analyze table a compute statistics;
analyze table b compute statistics;

SQL> set autotrace on
SQL> select count(*) from a,b where a.id=b.id and a.cityid='510';

  COUNT(*)
----------
       611

已选择 1 行。

已用时间:  00: 00: 00.78

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'AB_MV' (Cost=116 Card=619 Bytes=
          1857)

Statistics
----------------------------------------------------------
         17  recursive calls
         12  db block gets
        779  consistent gets
        757  physical reads
          0  redo size
        365  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          1  rows processed

-------------------------------------------------------------

结论：
1.查询的性能是上去了，但插入、更新的性能不理想，总的来说效果差强人意。
2.如果是一个超级大表(b)与一个小的静态代码表(a)关联，统计总数，用物化视图效果是非常好的。

谁有更好的解决方案，说说。

   建议对表进行拆分，比如根据MOD（ID，30）把A表拆分成A1、A2、A3……A30个表
按同样规则对B也拆分。
   可以考虑对表作个分区。看起来你的表应该是有地区或者城市这个字段。