Re: Replacing a view with selects on it

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 17 May 2004 08:34:22 -0600
Message-id: <40A8CD6E.2010503@sun.com>

There is one problem with this that I can see. By changing the view while another session has referenced it, you invite the possibility of a non-repeatable read.

In the example, I have 2 sessions running. The commands are mapped to each session by the S1 or S2 prefix and the commands are displayed in chronological order

ex1: Simple recreation of view

S1: create view seq_view as (select rec_no, insert_text from sequential_data);
S1: select * from seq_view;
S1: output from running query
     REC_NO INSERT_TEXT
---------- --------------------------------------------------------------------------------
          1 Record 1
          2 Record 2

S2: create or replace view seq_view as (select insert_date, rowid_blocknum, rowid_rownum from sequential_data);

S1: ouptut continues

     REC_NO INSERT_TEXT

---------- --------------------------------------------------------------------------------
     999997 Record 999997
     999999 Record 999999

500000 rows selected.

S1: / -- to invoke the command still in the buffer S1: output from running query
INSERT_DA ROWID_BLOCKNUM ROWID_ROWNUM

--------- -------------- ------------
22-MAR-04         109118            7
22-MAR-04         109118            9
22-MAR-04         109118           11
22-MAR-04         109118           13
22-MAR-04         109118           15
22-MAR-04         109118           17

500000 rows selected.

• The reexecution of the statement was performed against the new view. The old view was recreated, so the old view definition was invalidated in the library cache and a hard parse was required. This could cause a non-repeatable read in an application or query.

One area to check is what happens if the view is referenced as a cursor inside a pl/sql loop. The cursor is opened against view1 and data is retrieved. Then the cursor is closed, but the pl/sql block is still executing. The view is changed. The cursor is opened again, but this time the view referenced contains different data. Definitely something to test.

Daniel Fink

Wolfgang Breitling wrote:
> At 09:49 AM 5/16/2004, you wrote:
>
>

>>1) start long select from a view
>>
>>2) create or replace the view while the session is running
>>3) see what happen:)
>>
>> The reader session did "survived" (I guess with the cached  version of 
>>the view).

>
>
> Unless my understanding is utterly wrong, the view definition is only
> required during the parsing of the query. Once it starts the execute/fetch
> phase the plan is bound to the physical objects and the view definition can
> change.
> I am confident that Oracle would prevent view changes until the query
> finishes if it was needed beyond the parse.
>
>
>

>>But it is no prove!
>>
>>Are there situation, where this approach can cause problems? Any 
>>experience with this topic on the list?

>
>
> Based on my understanding and assessment, I do not foresee any problems. I
> have not done it on a massive or regular basis, but I have never had a
> problem altering a view definition in a live system, except when not using
> replace and permissions get lost temporarily.
>
>
>

>>Jaromir D.B. Nemec

>
>
> regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon May 17 2004 - 09:34:12 CDT