Search My Oracle Blog

Custom Search

31 May, 2008

Ever heard of "_simple_view_merging" ?

We've all (or most of us, I guess) have heard of and some of us have even realised being hit by complex_view_merging issues in 9i and 10g.

A couple of days ago, I came across Bug#6653652 "Wrong results from simple view merging". {I "came across" the Bug only when going through recent notes in the "Knowledge Base" section and do NOT know if any of my databases / applications has or does suffer this bug !).

Ostensibly it is a 10g bug but I ran the test script in 9.2.0.6 and saw the same results -- except (uh oh !) that I could not run an "alter session set "_simple_view_merging"=FALSE;" in 9.2.0.6 !

I've taken the very simple test case from Note# 6653652.8 and have expanded it with a few more "verification" queries :
The tests were in 9.2.0.6 and 10.2.0.3 I plan to upgrade one of my test environments to 10.2.0.4 this weekend.

UPDATE 01-Jun : Testing on a 10.2.0.4 environment, the first query DID return the correct results (so also did the one where I used a DECODE to translate a NULL on organizer, I did get the correct value "1024") -- the bug IS fixed in 10.2.0.4


SQL>
SQL> REM Bug 6653652. Note 6653652.8
SQL> REM title : "Wrong results from simple view merging"
SQL> REM description :
SQL> REM "Wrong results are possible from queries that undergo
SQL> rem simple view merging if the query contains a scalar
SQL> rem subquery in an OR branch."
SQL>
SQL> REM Fixed In 10.2.0.4, 11.1.0.7, 11.2.x.x (future release)
SQL>
SQL>
SQL>
SQL>
SQL> drop table test1;
drop table test1
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table test2;
drop table test2
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table test3;
drop table test3
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> -- using exactly the same structures and data as in the Oracle test case
SQL> -- just change the string 'organizer' to 'is org record' to reduce confusion
SQL> create table test1 (id varchar2(128), creator number);

Table created.

SQL> create table test2 (id varchar2(128), key varchar2(64),value number);

Table created.

SQL> create table test3 (id number, value number);

Table created.

SQL> insert into test1 values('a',2);

1 row created.

SQL> rem insert into test2 values('a','organizer',1024);
SQL> insert into test2 values('a','is org record',1024);

1 row created.

SQL> insert into test3 values(1024,2);

1 row created.

SQL>
SQL>
SQL>
SQL> -- using exactly the same query as in the Oracle test case
SQL> -- this query (with the scalar subquery in the OR) returns incorrect results
SQL> select creator, organizer
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where (creator=2 or organizer in (select id from test3 group by id));

CREATOR ORGANIZER
---------- ----------
2

SQL>
SQL>
SQL> -- let's verify tables test1 and test2
SQL> select creator, organizer
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where (creator=2 )
6 -- or organizer in (select id from test3 group by id));

CREATOR ORGANIZER
---------- ----------
2 1024

SQL>
SQL>
SQL> -- let's verify table test3
SQL> select id from test3 group by id;

ID
----------
1024

SQL>
SQL> -- test3's group by returns the value
SQL> -- therefore the subquery in the test case should return TRUE and succeed !
SQL>
SQL>
SQL> -- let's explicitly define how the OR is in the WHERE
SQL> -- handle a null for organizer with a decode
SQL> select creator, decode(organizer,NULL,9999,organizer)
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where
6 (
7 (creator=2)
8 or organizer in (select id from test3 group by id)
9 );

CREATOR DECODE(ORGANIZER,NULL,9999,ORGANIZER)
---------- -------------------------------------
2 9999

SQL>
SQL>
SQL> --- retry original query with _simple_view_merging=FALSE
SQL>
SQL> alter session set "_simple_view_merging"=FALSE;

Session altered.

SQL>
SQL> select creator, organizer
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where (creator=2 or organizer in (select id from test3 group by id));

CREATOR ORGANIZER
---------- ----------
2 1024

SQL>
SQL>
SQL>
Note : alter session set "_simple_view_merging"=FALSE; was not possible in 9.2.0.6

4 comments:

Mumbai Guy said...

Hemant, your posts are very informative and educational. Keep blogging. thanks.

Mike said...

Hi Hennant, I'm faced with the bug 4730372 related to the same parameter "_simple_view_merging".
The app worked fine on oracle 9 but after migrating to oracle 10.2.0.4 we found this bug:

http://myotragusbalearicus.wordpress.com/2010/12/13/ora-0600-after-migrating-to-oracle-10-2-0-4-bug-4730372/

Hemant K Chitale said...

Mike,
In my tests, the bug manifested in 9.2 (9.2.0.6).

Hemant

Luiz Fernando Altran said...

7 years after you saved me :)
My Oracle 11g R1 suddenly start to give me ORA 7445, and after using your trick I solved the problem.
Thank you

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016