select (case
when fjname is null
then '全局'
else fjname
end) fjname,
(case
when cust_type is null
then '总计'
else cust_type
end
) cust_type,
curr_user
from (select fjname, (case when cust_type is null then '公免等' else cust_type end) cust_type,
sum(case state when '正常' then 1 else 0 end) curr_user
from tmp_tj0507001
where fjname is not null and state = '正常'
group by rollup (fjname, (case when cust_type is null then '公免等' else cust_type end)))
order by fjname, cust_type;
执行计划：
select statement, goal = choose cost=3021 cardinality=43 bytes=1376
sort order by cost=3021 cardinality=43 bytes=1376
view object owner=yaoyp cost=3020 cardinality=43 bytes=1376
sort group by rollup cost=3020 cardinality=43 bytes=989
index fast full scan object owner=yaoyp object name=sys_iot_top_62350 cost=367 cardinality=1132418 bytes=26045614
没有temp table transformation了。看来要避免这个执行路径，想了半天也没办法，开始还怀疑是参数star_transformation_enabled问题，后来一看是false的。只能到metalink上找了，结果又是一个bug，最近简直是一出问题就是bug，以前怎么就不觉得呢？
文档 id: 注释:2836690.8
主题: support description of bug 2836690
类型: patch
状态: published
内容类型: text/x-html
创建日期: 08-aug-2003
上次修订日期: 14-aug-2003
click here for details of sections in this note.
bug 2836690 rollup over dblink may fail with oeri:qks3tinit
this note gives a brief overview of bug 2836690.
affects:
product (component) oracle server (rdbms)
range of versions believed to be affected versions >= 9 but < 10g
versions confirmed as being affected
* 9.2.0.3
platforms affected generic (all / most platforms affected)
fixed:
this issue is fixed in
* 9.2.0.4 (server patch set)
* 10g production base release
symptoms:
* internal error may occur (ora-600)
* ora-600 [qks3tinit]
related to:
* db-link / distributed
* analytic sql (windowing etc..)
description
ora-600 [qks3tinit] possible from queries using rollup which reference
remote objects.
the full bug text (if published) can be seen at <bug:2836690>
this link will not work for unpublished bugs.
类似还有一个bug：
文档 id: 注释:2419335.8
主题: support description of bug 2419335
类型: patch
状态: published
内容类型: text/x-html
创建日期: 08-aug-2003
上次修订日期: 14-aug-2003
click here for details of sections in this note.
bug 2419335 oeri:[qks3tinit] possible from star transformation
this note gives a brief overview of bug 2419335.
affects:
product (component) oracle server (rdbms)
range of versions believed to be affected versions >= 9.2 but < 10g
versions confirmed as being affected
* 9.2.0.3
