From oracle-l-bounce@freelists.org Thu Dec 8 04:32:15 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jB8AWFlv012688 for ; Thu, 8 Dec 2005 04:32:15 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id jB8AWEAX012680 for ; Thu, 8 Dec 2005 04:32:14 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2587B24E60E; Thu, 8 Dec 2005 05:32:08 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03239-05; Thu, 8 Dec 2005 05:32:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 96A3724E170; Thu, 8 Dec 2005 05:32:07 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 08 Dec 2005 05:32:07 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4A7AC24E2D5 for ; Thu, 8 Dec 2005 05:32:07 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03227-06 for ; Thu, 8 Dec 2005 05:32:07 -0500 (EST) Received: from server102.tchmachines.com (server102.tchmachines.com [72.9.248.66]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1795424E170 for ; Thu, 8 Dec 2005 05:32:07 -0500 (EST) Received: from server102.tchmachines.com ([72.9.248.66] helo=www.timothyhopkins.net) by server102.tchmachines.com with esmtp (Exim 4.50) id 1EkJ4G-0004ue-Pu; Thu, 08 Dec 2005 05:32:04 -0500 Received: from 192.165.213.18 (SquirrelMail authenticated user oracle-l@timothyhopkins.net); by www.timothyhopkins.net with HTTP; Thu, 8 Dec 2005 05:32:04 -0500 (EST) Message-ID: <35148.192.165.213.18.1134037924.squirrel@192.165.213.18> In-Reply-To: <9177895d0512070912x46edfa88o9e184612a57be17e@mail.gmail.com> References: <9177895d0512070912x46edfa88o9e184612a57be17e@mail.gmail.com> Date: Thu, 8 Dec 2005 05:32:04 -0500 (EST) Subject: Re: 10gR2 and _simple_view_merging issue From: oracle-l@timothyhopkins.net To: rjamya@gmail.com Cc: "Oracle Discussion List" User-Agent: SquirrelMail/1.4.3a MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server102.tchmachines.com X-AntiAbuse: Original Domain - freelists.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - timothyhopkins.net X-Source: X-Source-Args: X-Source-Dir: X-archive-position: 29024 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: oracle-l@timothyhopkins.net Precedence: normal Reply-To: oracle-l@timothyhopkins.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Not scanned: please contact your Internet E-Mail Service Provider for details X-MailScanner-From: oracle-l-bounce@freelists.org 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