急！创建的视图用了union连接表但执行select count(*)发现没有用索引，请斑竹回复！！
如A B两个表字段一致，分别存储各个月份的数据，对所有月份数据查询通过视图实现：create view v_test(col1,col2,col3) as
select col1,col2,col3 from A
union
select col1,col2,col3 from B
执行查询：
select count(*) from v_test
通过set explain on 发现此SQL查询语句没有通过索引扫描而是全表扫描，结果占用了大量临时表空间
不知是和原因能否？
或者能否 通过join等其它办法解决？
请斑竹回复！！ 我也遇到这种情况,帮顶下 select count(*)本来就是走全表扫描~ 我的视图是这样的.
create view tt_view as
select * from tt@online:tt
union
select * from tt1
单独 count(*) 某个表速度很快,直接count(*)tt_view报临时表空间不够,郁闷 我的视图是这样的.
create view tt_view as
select * from tt@online:tt
union
select * from tt1
...
sqlnet 发表于 2010-03-24 12:16 http://bbs3.chinaunix.net/images/common/back.gif
单count(*)一个表是从systables中读数的.自然快.... 单count(*)一个表是从systables中读数的.自然快....
liaosnet 发表于 2010-03-24 15:32 http://bbs2.chinaunix.net/images/common/back.gif
select count(*) from t1;
----------------------- test@test -------------- Press CTRL-W for Help --------
(count(*))
4917
select * from systables
where tabname='t1';
----------------------- test@test -------------- Press CTRL-W for Help --------
tabname t1
owner informix
partnum 1048939
tabid 103
rowsize 4
ncols 1
nindexes 0
nrows 0.00
created 2010/03/06
version 6750210
tabtype T
locklevel P
npused 0.00
fextsize 16
nextsize 16
flags 0
site
dbname
type_xid 0
am_id 0
pagesize 2048
ustlowts
secpolicyid 0
protgranularity
请问下 那个字段记录了 t1的总条数 4917 呢 select count(*) from t1;
----------------------- test@test -------------- Press CTRL-W...
sqlnet 发表于 2010-03-26 20:55 http://bbs2.chinaunix.net/images/common/back.gif
select t.tabname, p.nrows, t.nrows
from sysmaster:sysptnhdr p, systables t
where p.partnum = t.partnum
andt.tabname = 't1';
-----------
tabnamet1
nrows 16657
nrows 16657.00000000
select count(*) from t1 实际取的就是p.nrows的值, 这也是为什么这个语句的explain走索引的原因..
若我们在这个语句后加上 1=1 ,就会发现这走的是顺序扫描~.
若是p.nrows跟t.nrows相差太多,表时你这个表需要做统计更新了.
QUERY: (OPTIMIZATION TIMESTAMP: 03-26-2010 21:07:10)
------
select count(*) from t1
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.t1: INDEX PATH
(1) Index Name: (count)
Index Keys: (count)
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
type rows_prodest_rowsrows_constime
-------------------------------------------------
group 1 1 0 00:00.00
QUERY: (OPTIMIZATION TIMESTAMP: 03-26-2010 21:07:19)
------
select count(*) from t1 where 1=1
Estimated Cost: 790
Estimated # of Rows Returned: 1
1) informix.t1: SEQUENTIAL SCAN
Filters: t
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1
type tablerows_prodest_rowsrows_scantime est_cost
-------------------------------------------------------------------
scan t1 16657 16657 16657 00:01.04 791
type rows_prodest_rowsrows_constime
-------------------------------------------------
group 1 1 16657 00:01.27
谢谢解答。
页:
[1]