我用物化视图试验了一下,过程如下:
-------------------------------------------------------
-- 建表 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也拆分。
可以考虑对表作个分区。看起来你的表应该是有地区或者城市这个字段。