Re: 10gR2 and _simple_view_merging issue

From: <oracle-l_at_timothyhopkins.net>
Date: Thu, 8 Dec 2005 05:32:04 -0500 (EST)
Message-ID: <35148.192.165.213.18.1134037924.squirrel@192.165.213.18>

Raj,

    That sounds like the expected behaviour to me. Setting that parameter to FALSE is equivalent to using the NO_MERGE hint against the view, which is expected to produce the error you're receiving. See below for an example which demonstrates this is the expected behaviour. Note that it isn't possible to selectively override the parameter using the MERGE hint.

    I'd recommend leaving the parameter set to TRUE then include the NO_MERGE hint where necessary in the views which had the original performance issues.

SQL> CREATE TABLE MERGE_TEST AS SELECT * FROM DUAL; Table created.

SQL> CREATE VIEW MERGE_TEST_V AS SELECT * FROM MERGE_TEST; View created.

SQL> INSERT INTO MERGE_TEST_V VALUES ('Y'); 1 row created.

Elapsed: 00:00:00.17
SQL> INSERT /*+ NO_MERGE(MERGE_TEST_V) */ INTO MERGE_TEST_V VALUES ('Y');

                                          *

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> ALTER SESSION SET "_SIMPLE_VIEW_MERGING" = FALSE; Session altered.

SQL> INSERT INTO MERGE_TEST_V VALUES ('Y');             *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> INSERT /*+ MERGE(MERGE_TEST_V) */ INTO MERGE_TEST_V VALUES ('Y');

                                       *

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Elapsed: 00:00:00.26

Cheers,
Tim

> We found an interesting issue in testing 10gR2. We have an application
> that
> inserts into a view. Mind you it is a simple view, no distincts, group-by
> etc.
>
> In earlier testing we encountered performance issues so we turned off
> "_simple_view_merging" (set to false) to improve performance. As soon as
> we
> did that, inserts into simple views (with an exist clause) failed with
> ORA-1732 and ORA-2014 (in different cases).
>
> We turned on the "_simple_view_merging" by settign it to true, both errors
> disappeared.
>
> We are doing more testing before logging a TAR ... sorry SR but does
> anyone
> have any ideas on this behavior ?? Nothing suitable found in Metalink,
> Google.
>
> TIA
> Raj
> ----------------------------------------------
> This space is available for rent.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 08 2005 - 04:32:15 CST