Oracle version 11
OPTIMIZER_FEATURES_ENABLE=10.2
The following SQL
Select
To_date (col1, 'yyyymmdd')
The from (
Select
To_char (table1. CUR_MON, 'YYYYMM') | | '01' as col1
The from
Table1
Where
Table1. CUR_MON is not null
)

Table1. CUR_MON is date type, and have a null value data,
The question now is in the customer environment, execute SQL complains, ora - 01840,

If the customer environment changing OPTIMIZER_FEATURES_ENABLE to 9.2, is not an error.

Why want to consult everybody complains, the condition has been remove the null data in
And this period of SQL I to the test environment of this error is not reproduce... Only in a customer environment will go wrong.
Test environment and customer environment database version is the same as

If you do not modify the SQL,,, from the perspective of oracle optimization strategy, such as do table analysis can solve the error, and always feel the error is very strange, that should not happen.

Because the customer environment temporarily unable to visit, so can't go to test, and now the customer want to know the reason for the error and solution over there (do not modify the SQL)


Also said about ora - 01840 error,,, you can perform a SQL select to_date (' 01 ', 'YYYYMMDD') from dual;

CodePudding user response:

Ding xia I

CodePudding user response:

OPTIMIZER_FEATURES_ENABLE will influence the execution of the query optimizer, but an exception is thrown, not met,
I suggest that from the data layer analysis again and see if there were any abnormal data

CodePudding user response:

refer to the second floor jdsnhan response:

OPTIMIZER_FEATURES_ENABLE affects the execution of the query optimizer, but an exception is thrown, not met,
I suggest that from the analysis of the data layer again and see whether there are abnormal data

Thank you for your reply,
No problem, the data level, date type of data in addition to the legal date value, is null, so the data is no problem.
Here should be involving the optimizer and optimize the selection of the mode,
In oracle9i version optimize_mode is CHOOSE, CHOOSE according to table if there is a statistical information to determine the CBO or RBO optimization.
If there is no statistical information, and is optimize_mode RULE when SQL execution is an error, and I only analysis here, as for the deeper reason why I don't know.
I wonder if there are some people need this kind of question.

CodePudding user response:

To SQL sentence, let the CBO across two big version relegation, is the behavior of things...

Agree with 2 # : carefully check whether the data has a problem, also can try to modify the SQL to bypass this problem