oracle 11g版本出现的字段查询诡异问题及解决办法

WITH resultTable AS ( www.2cto.com

SELECT b.STAND_CFG_PRI_ID,

b.PRODFAM_ID,

b.PRODFAM_CODE,

b.PRODUCT_LINE_ID,

b.PRICE_LOOUP_TYPE,

b.PRICE_LOOUP_CODE,

b.STAND_CFG_CONST,

b.SALES_CHANNEL_TYPE,

b.SALES_CHANNEL_CODE,

b.PRICE,

b.MATER_COST_PRICE,

b.PRODU_COST_PRICE,

b.UNITS,

b.CURRENCY,

b.CREATE_BY,

b.ENABLE_FLAG,

b.REGISTER_ID,

TO_CHAR(b.CREATE_DATE,'YYYY-MM-DD') AS CREATE_DATE,

b.LAST_UPDATE_BY,

TO_CHAR(b.LAST_UPDATE_DATE,'YYYY-MM-DD') AS LAST_UPDATE_DATE,

FROM ECC_OC.STAND_CFG_COST_PRICE_V b,

ECC_OC.STAND_CFG_PRICE scp

WHERE b.ENABLE_FLAG ='T'

AND scp.STAND_CFG_PRI_ID = b.STAND_CFG_PRI_ID

AND b.PROD_LINE_CODE ='PL_DZB'

AND b.PRICE_LOOUP_CODE ='CONFIG_SALES_PRICE_CODE'

ORDER BY b.CREATE_DATE

)

SELECT result.* FROM

( SELECT * FROM

( SELECT resultTable.*,viewTable.*,ROWNUM AS resultNums FROM resultTable , (SELECT Count(1) AS resultCount FROM resultTable) viewTable

)

) result

整个查询中，只有那个红色的字段有问题，原因很可能是 ECC_OC.STAND_CFG_COST_PRICE_V b 这个视图，本来就包含了ECC_OC.STAND_CFG_PRICE scp里面的某些字段，然后再复杂的查询下，11g bug了。。

解决方法是：把optimizer_features_enable设成10g。 语句如下：

alter system set optimizer_features_enable = "10.1.0.5" scope=both;