Oracle 10.2 acting weird. Or is it me
Please check out the following query. The SQL isn't as bad as it looks. Basically, we have a fact table and some simple joins to some dimension tables. Then we have a join to a derived table, given the alias 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
开发者_JS百科 = 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
The users were complaining that when their WebI (business intelligence) report included multiple COST CENTERS in their filter, resulting in a SQL with an "IN" include multiple values, the following error was returned:
[1]: (Error): ORA-01722: invalid number ORA-02063: preceding line from [dbname]
Otherwise, for a single COST CENTER, the report worked fine. The interesting part is that I noticed that the following join condition, which to me appears UNRELATED, was negatively impacting the SQL:
accounts_dim_dep.account_id = dw_mgr.fa_trans_fct.depreciation_account_id
The problem here is that the column on the left, accounts_dim_dep.account_id, is defined in the db as a charchar and the col on the right, dw_mgr.fa_trans_fct.depreciation_account_id, is defined as a number.
When I modified the join condition to convert the number to a varchar...
accounts_dim_dep.account_id
= to_char(dw_mgr.fa_trans_fct.depreciation_account_id)
...the SQL works regardless of the number of COST CENTERS that are specified in the filter.
I'd like to know how a type mismatch on one seemingly unrelated column affects the whether one can specify multiple COST CENTERS in the IN list.
The ORA-02063 error means that the ORA-01722 error occurs in a remote database. That fits with the fact that (according to the explain plan) the RF_FND_FLEX_VALUES_DET table is remote.
The value accounts_dim_dep.account_id
is an alias for a flex_value
, which appears to be a varchar2 and almost certainly contains non-numeric values. When you compare it to a numeric column Oracle applies an implicit TO_NUMBER()
to it, which fails with the ORA-01722 if it hits a value which is not a number. By converting
dw_mgr.fa_trans_fct.depreciation_account_id
to a string you avoid the implicit conversion.
So why does the original query succeed when you only have one cost centre but fail when you have several? Without having access to your data to run some tests, or at the very least the explain plans for the different versions, it is hard to be sure. But the explain plan you have published shows that the remote operation retrieves just the one row from RF_FND_FLEX_VALUES_DET. I'm guessing that when you run the query with multiple cost centres it pulls back a fistful of rows, which include some where flex_value
has a non-numeric value.
If you had PLW errors enabled, you'd have been alerted to the situation earlier - you'd have gotten a "conversion away from type" error. I re-wrote your query:
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'
Changelog:
- Save yourself some typing by using table aliases (also makes it easier for others to read & help)
- Removed: hyperion_organization was a join to the same table, using the same criteria
- Specified
TO_NUMBER(RF_FND_FLEX_VALUES_DET.flex_value)
so the conversion occurs before the JOIN
I don't know why the ORA error would occur on 2+ entries in the IN clause, but it you provide two of the same as you posted then it's not likely to be a data issue.
精彩评论