数据库链接执行含ROLLUP的SQL的Bug_Sql Server_学习笔记★闵涛★计算机学习电脑编程软硬件技巧


	转至繁体中文版	\| 网站首页 \| 图文教程 \| 资源下载 \| 站长博客 \| 图片素材 \| 武汉seo \| 武汉网站优化 \|

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

作者：闵涛文章来源：闵涛的学习笔记点击数：2242 更新时间：2007/11/14 13:08:08

通过微软的报表服务器访问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
     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
       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:

&nbs

[1] [2] 下一页