數據庫鏈接執行含ROLLUP的SQL的Bug

通過微軟的報表服務器訪問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:

    * Internal Error may oclearcase/" target="_blank" >ccur (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咯。關于上面這個執行路徑資料好少，有辦法避免么？