数据库链接执行含ROLLUP的SQL的Bug

王朝mssql·作者佚名 2006-01-09

通过微软的报表服务器访问Oracle，做好一张报表，为了偷懒，直接在数据库建立了一个视图，然后显示就行了。结果测试时报ORA-600 [qks3tinit]。登陆到本机一运行，没问题啊。怀疑是微软报表服务器连接Oracle的接口有问题。但是其他几个类似视图又都没问题。于是检测SQL：

SELECT (CASE

WHEN fjname IS NULL

THEN '全局'

ELSE fjname

END) fjname,

(CASE

WHEN stat_type IS NULL

THEN '总计'

ELSE stat_type

END) stat_type,

(CASE

WHEN cust_type_old IS NULL

THEN '总计'

ELSE cust_type_old

END

) cust_type_old,

cnt, curr_user

FROM (SELECT fjname, stat_type, cust_type_old, COUNT (*) cnt, SUM(CASE state WHEN '正常' THEN 1 ELSE 0 END) curr_user

FROM tmp_tj0507001

WHERE fjname IS NOT NULL

GROUP BY grouping sets(rollup(fjname, stat_type, cust_type_old), rollup(stat_type, cust_type_old)))

ORDER BY fjname, stat_type, cust_type_old;

执行计划：

SELECT STATEMENT, GOAL = HINT: ALL_ROWS Cost=10 Cardinality=15 Bytes=780

SORT ORDER BY Cost=10 Cardinality=15 Bytes=780

VIEW Object owner=YAOYP Cost=8 Cardinality=15 Bytes=780

TEMP TABLE TRANSFORMATION

VIEW Object owner=YAOYP Cost=8 Cardinality=4 Bytes=208

UNION-ALL

TABLE ACCESS FULL Object owner=SYS Object name=SYS_TEMP_0FD9D668F_9A864B59 Cost=2 Cardinality=1 Bytes=52

TABLE ACCESS FULL Object owner=SYS Object name=SYS_TEMP_0FD9D6690_9A864B59 Cost=2 Cardinality=1 Bytes=52

TABLE ACCESS FULL Object owner=SYS Object name=SYS_TEMP_0FD9D6691_9A864B59 Cost=2 Cardinality=1 Bytes=52

RECURSIVE EXECUTION Object name=SYS_LE_7_0

RECURSIVE EXECUTION Object name=SYS_LE_7_1

RECURSIVE EXECUTION Object name=SYS_LE_7_2

RECURSIVE EXECUTION Object name=SYS_LE_7_3

就看到一个TEMP TABLE TRANSFORMATION，这个比较少见哦。另外一个类似SQL：

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

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

* Star Transformation

Description

ORA-600 [qks3tInit] possible from star transformation query

where all the tables are remote (ie: over a dblink)

Workaround:

Set star_transformation_enabled = false at the remote DB.

The full bug text (if published) can be seen at <Bug:2419335>

This link will not work for UNPUBLISHED bugs.

不过我还是觉得是TEMP TABLE TRANSFORMATION问题，没理由第2个SQL里的ROLLUP就没问题哦。唉，放弃ROLLUP咯。关于上面这个执行路径资料好少，有办法避免么？