| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Replacing a view with selects on it
Jaromir
This reminds me of a DW product I've dealt with. All tables are duplicated into a _A set and _B set. Before a data refresh begins, all views point to the _A tables. The data is loaded into the _B set. Then if the load is validated, all views are switched to point to the _B set. If the load fails, the views remain pointing to the _A set, and the users have to wait for fresh data, but are otherwise able to continue processing. I believe there is a final step where the _A tables are truncated and the _B tables are copied into them, but I can't recall exactly. Seemed pretty clever.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of jaromir nemec
Sent: Monday, May 17, 2004 4:52 PM
To: oracle-l_at_freelists.org
Subject: Re: Replacing a view with selects on it
Thanks Daniel, Wolfgang and Justin,
@Daniel - I didn't consider a change of a view *structure*. I meant only a change of the data selected in the view, e.g. a change of the where clause in the view. From this point of view the only *repeatable read* requirement is, that the select don't crash or return wrong result if the view is replaced while performed.
@Wolfgang - good point; the only access of the view while parsing the query - but what happen if the query gets out from library cache and needs to be reparsed?
Could there be a situation that it cause some troubles?
@Justin - yes I admit that the safest way is to perform the change direct with DML. The database guaranties that all is transactional safe. But e.g. in case if you have a massive change in a big snapshot it could be preferable to *prepare* the change in stage area and to *publish* the new version of this snapshot via a replacement of a view (e.g. exchange partition and repointing the access view to the new partition)
regards
Jaromir D.B. Nemec
>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.
>Unless my understanding is utterly wrong, the view definition is only
>required during the parsing of the query.
>My preference in this sort of environment would be to replace the data
>in a single transaction,
-- 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 - 19:28:39 CDT
 |
 |