Oracle 10.2 表现得很奇怪。还是我

Question

请查看以下查询。 SQL 并不像看起来那么糟糕。基本上，我们有一个事实表和一些维度表的简单连接。然后我们连接到一个派生表，给定别名 ACCOUNTS-DIM-DEP

  SELECT dw_mgr.fa_trans_fct.period,
         dw_mgr.fa_trans_fct.asset_cost_company_code,
         dw_mgr.fa_trans_fct.asset_cost_center_id,
         dw_mgr.fa_trans_fct.depreciation_account_id,
         accounts_dim_dep.description, 
         dw_mgr.projects_dim.project_num,
         dw_mgr.projects_dim.project_name,
         ROUND (dw_mgr.fa_trans_fct.activity_deprn_amount_us, 2),
         organizations_cost.major_geography,
         organizations_cost.business_unit || organizations_cost.bu_desc,
         organizations_cost.industry_sector_num
              ||organizations_cost.industry_sector_desc,
         hyperion_organizations.hyperion_num,
         hyperion_organizations.hyperion_desc,
         hyperion_organizations.hyperion_reporting
    FROM dw_mgr.fa_trans_fct,
         (SELECT DISTINCT flex_value account_id, description
                     FROM rf_fnd_flex_values_det
                    WHERE flex_value_set_id = '1002363' 
                      AND summary_flag = 'N') accounts_dim_dep,
         dw_mgr.projects_dim,
         dw_mgr.organizations organizations_cost,
         dw_mgr.organizations hyperion_organizations
   WHERE 
         --Fact to Org on Company Code / Cost Center
         (dw_mgr.fa_trans_fct.asset_cost_center_id   
                                     = organizations_cost.cost_center_id)
     AND (dw_mgr.fa_trans_fct.asset_cost_company_code 
                                     = organizations_cost.company_code)
     --Fact to Projects Dim on Proj Num
     AND (dw_mgr.projects_dim.project_num = dw_mgr.fa_trans_fct.project_num)
     --Fact to Accounts_Dim_Dep on Account ID
     --convert account_ID on left to_number??????
     AND (accounts_dim_dep.account_id 
                            = dw_mgr.fa_trans_fct.depreciation_account_id) 
     --Fact Hyp Company Code Cost Center to Hyp Org
     AND (hyperion_organizations.cost_center_id 
                            = dw_mgr.fa_trans_fct.asset_cost_center_id AND
          hyperion_organizations.company_code  
                            = dw_mgr.fa_trans_fct.asset_cost_company_code)
   --Filters
     AND (
          dw_mgr.fa_trans_fct.period IN ('01-Jun-2009')
          --works
          --AND dw_mgr.fa_trans_fct.asset_cost_center_id IN ('000296') 
          --does not work               
          AND dw_mgr.fa_trans_fct.asset_cost_center_id IN ('000296','000296') 
          AND dw_mgr.fa_trans_fct.asset_cost_company_code = '0007'
         )



  ------------------------------------------------------------

  Statement Id=4203172   Type=
  Cost=2.64018716311899E-308  TimeStamp=06-10-09::17::51:43

       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 1  Cost: 6
       (14)  NESTED LOOPS 
     Est. Rows: 1  Cost: 6
           (11)  NESTED LOOPS 
                Est. Rows: 1  Cost: 5
               (9)  HASH JOIN 
                    Est. Rows: 1  Cost: 3
                   (3)  TABLE TABLE ACCESS BY INDEX ROWID DW_MGR.ORGANIZATIONS  [Analyzed] 
                   (3)   Blocks: 1,669 Est. Rows: 1 of 31,748  Cost: 1 
                        Tablespace: DIM_DATA
                       (2)  INDEX (UNIQUE) INDEX UNIQUE SCAN DW_MGR.ORG_PK  [Analyzed] 
                            Est. Rows: 1  Cost: 1
                   (8)  PARTITION RANGE SINGLE 
                        Est. Rows: 7  Cost: 1
                       (7)  PARTITION LIST ALL 
                            Est. Rows: 7  Cost: 1
                           (6)  TABLE TABLE ACCESS BY LOCAL INDEX ROWID DW_MGR.FA_TRANS_FCT  [Analyzed] 
                                Blocks: 1,431,026 Est. Rows: 7 of 32,900,663  Cost: 1
                               (5)  BITMAP CONVERSION TO ROWIDS
                                   (4)  INDEX (BITMAP) BITMAP INDEX SINGLE VALUE DW_MGR.FA_TRANS_AST_COMP_CC_BM_I
               (10)  REMOTE REMOTE.RF_FND_FLEX_VALUES_DET 
                    Est. Rows: 1  Cost: 2
           (13)  TABLE TABLE ACCESS BY INDEX ROWID DW_MGR.PROJECTS_DIM  [Analyzed] 
           (13)   Blocks: 12,184 Est. Rows: 1 of 163,117  Cost: 1 
                Tablespace: PROJECT_DATA
               (12)  INDEX (UNIQUE) INDEX UNIQUE SCAN DW_MGR.PROJECTS_UI  [Analyzed] 
                    Est. Rows: 1  Cost: 1

用户抱怨说，当他们的 WebI（商业智能）报告在其过滤器中包含多个 COST CENTERS 时，导致带有“IN”的 SQL 包含多个值时，返回以下错误：

   [1]: (Error): ORA-01722: invalid number ORA-02063: preceding line from [dbname]

否则，对于单个成本中心，报告工作正常。有趣的是，我注意到以下连接条件（在我看来不相关）对 SQL 产生了负面影响：

accounts_dim_dep.account_id = dw_mgr.fa_trans_fct.depreciation_account_id

这里的问题是左侧的列，accounts_dim_dep.account_id，是在db 为 charchar，右侧的 col dw_mgr.fa_trans_fct.depreciation_account_id 定义为数字。

当我修改连接条件以将数字转换为 varchar 时......

accounts_dim_dep.account_id 
                       = to_char(dw_mgr.fa_trans_fct.depreciation_account_id)

无论过滤器中指定的 COST CENTERS 数量如何，SQL 都会正常工作。

---

我想知道一个看似不相关的列上的类型不匹配如何影响是否可以在 IN 列表中指定多个成本中心。

Answer 1

ORA-02063错误意味着ORA-01722错误发生在远程数据库中。这符合以下事实：（根据解释计划）RF_FND_FLEX_VALUES_DET 表是远程的。

值 accounts_dim_dep.account_id 是 flex_value 的别名，它看起来是 varchar2 并且几乎肯定包含非数字值。当您将其与数字列进行比较时，Oracle 会对其应用隐式 TO_NUMBER()，如果命中的值不是数字，则会失败并返回 ORA-01722。通过转换
将 dw_mgr.fa_trans_fct.depreciation_account_id 转换为字符串，以避免隐式转换。

那么为什么当你只有一个成本中心时原始查询会成功，而当你有多个成本中心时原始查询会失败呢？如果无法访问您的数据来运行某些测试，或者至少无法访问不同版本的解释计划，则很难确定。但您发布的解释计划显示远程操作仅从 RF_FND_FLEX_VALUES_DET 检索一行。我猜测当您使用多个成本中心运行查询时，它会拉回一些行，其中包括一些 flex_value 具有非数字值的行。

Answer 2

如果您启用了 PLW 错误，您会提前收到有关情况的警报 - 您会收到“转换远离类型”错误。我重写了您的查询：

SELECT t.period,
       t.asset_cost_company_code,
       t.asset_cost_center_id,
       t.depreciation_account_id,
       add.description, 
       pd.project_num,
       pd.project_name,
       ROUND(t.activity_deprn_amount_us, 2),
       o.major_geography,
       o.business_unit || o.bu_desc,
       o.industry_sector_num || o.industry_sector_desc,
       o.hyperion_num,
       o.hyperion_desc,
       o.hyperion_reporting
  FROM DW_MGR.FA_TRANS_FCT t
  JOIN DW_MGR.PROJECTS_DIM pd ON pd.project_num = t.project_num
  JOIN DW_MGR.ORGANIZATIONS o ON o.cost_center_id = t.asset_cost_center_id
                             AND o.company_code = t.asset_cost_company_code
  JOIN (SELECT TO_NUMBER(rffvd.flex_value) 'account_id',
               rffvd.description
          FROM RF_FND_FLEX_VALUES_DET rffvd
         WHERE rffvd.flex_value_set_id = '1002363' 
           AND rffvd.summary_flag = 'N'
      GROUP BY rffvd.flex_value,
               rffvd.description) add ON add.account_id = t.depreciation_account_id
 WHERE t.period IN ('01-Jun-2009')
   AND t.asset_cost_center_id IN ('000296','000296') --doesn't work        
   AND t.asset_cost_company_code = '0007'

更改日志：

• 通过使用表别名来节省一些打字时间（也使其他人更容易阅读和帮助）
• 删除：hyperion_organization 是使用相同条件的同一个表的联接
• 指定的TO_NUMBER (RF_FND_FLEX_VALUES_DET.flex_value) 因此转换发生在 JOIN 之前

我不知道为什么 ORA 错误会发生在 IN 子句中的 2 个以上条目上，但如果您提供了两个与您发布的相同的条目，那么它就不是可能是数据问题。