Oracle 10.2 ведет себя странно. Или это я

Пожалуйста, проверьте следующий запрос. 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 (бизнес-аналитика) включал в свой фильтр несколько ЦЕНТРОВ ЗАТРАТ, в результате чего SQL с «IN» включал несколько значений, возвращалась следующая ошибка:

   [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

Проблема здесь в том, что столбец слева, account_dim_dep.account_id, определен в базе данных как charchar, а столбец справа, dw_mgr.fa_trans_fct.depreciation_account_id, определен как число.

Когда я изменил условие соединения, чтобы преобразовать число в varchar...

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

... SQL работает вне зависимости от количества ЦЕНТРОВ ЗАТРАТ, указанных в фильтре.

Я хотел бы знать, как несоответствие типов в одном, казалось бы, несвязанном столбце влияет на возможность указания нескольких ЦЕНТРОВ ЗАТРАТ в списке IN.


sql oracle distributed-transactions oracle10g ora-01722
person Chad    schedule 06.10.2009    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Если бы у вас были включены ошибки 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 может возникнуть в 2+ записях в предложении IN, но если вы предоставите две такие же записи, как вы опубликовали, то это вряд ли будет проблемой данных.

person OMG Ponies    schedule 07.10.2009
comment
Прочитав ответ APC, я пропустил, что это псевдоним, и соответствующим образом исправил свой ответ. - person OMG Ponies; 07.10.2009

arrow_upward
5
arrow_downward

Ошибка 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 имеет нечисловое значение.

person APC    schedule 07.10.2009
comment
Мы можем только догадываться, но ответ APC, вероятно, близок к истинной причине. У меня были похожие сообщения об ошибках, когда оптимизатор запросов оракула решил сделать преобразование типа, отличное от ожидаемого. Использование неявного преобразования типов исключает ошибку, потому что оракул иногда преобразует строку в число, если считает, что таким образом запрос выполняется быстрее. - person Christian13467; 07.10.2009