RE: ora-1722

Well I found out I needed to flush the shared pool as well as the queries where using what was in cached. But I am concerned that this still could happen using Rule based optimizer. I don't know the sql that caused this as it was part of the application package the consultants wrote.

I found on Metalink doc 19074.1

Be careful of test cases that work for some WHERE clause or some

        OPTIMIZER_MODE / OPTIMIZER_GOAL as different execution paths may
        skip any rows containing non-numberic data.

Doc 1517098

Queries should not be written which can result in 1722s if the order of evaluation changes. The order of evaluation also will change depending on the stats and the execution plan generated. The plan probably changed between V7 and V8 causing the order of evauluation of the where clause to change.

create table temp1 (field1 varchar2(10)); insert into temp1 values (10);
insert into temp1 values ('A');
.

select * from temp1 where field1 = 10 and field1 = '10'; --> Returns correctly select * from temp1 where field1 = '10' and field1 = 10; --> returns 1722
.

In these 2 queries, the only difference is that the where clause is changed. With the first one, Oracle executes field1 = '10' first, thus no implicit coversion is needed. field1 = 'A' is eliminated because of this condition and so by the time the second comparision (field1 = 10) is done, the invalid number condition no longer occurs.
.

In the second case, field1 = 10 is done first, which causes an implicit number conversion for all the rows. Thus a to_number('A') = 10 check is done which fails with an 1722.
.

Queries should not be written which can result in 1722s if the order of evaluation changes. The order of evaluation also will change depending on the stats and the execution plan generated. The plan probably changed between V7 and V8 causing the order of evauluation of the where clause to change.
.

This is not necessarily an issue with migration. Even with V7, if the statistics changed substantially, the plan can change causing an ORA 1722.
.

This query needs to be written correctly (for example, if the implicit conversion was avoided with a to_char around the number column, there is no need to rewrite the code.
.

In the above example, the second query should be : select * from temp1 where field1 = '10' and field1 = to_char(10);
.

Kathy

-----Original Message-----
Sent: Friday, July 27, 2001 2:31 PM
To: Multiple recipients of list ORACLE-L

I have been running Cost based optimizer for 3 weeks without a problem. Today we got a 1722 and we need to revert back to Rule base. I deleted the statistics and turned monitoring off but we are still having problems.

8.1.6 on HP-UNIX

Is there something I forgot to do so the scripts would revert back to using rule based optimization. Do I need to bounce the database? The original optimizer mode was set to Choose, should I set this to Rule?

Thanks

Kathy

Confidential
This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kathy Duret
  INET: KathyD_at_belkin.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kathy Duret
  INET: KathyD_at_belkin.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).