Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Side effect of replacing a table with a view

Re: Side effect of replacing a table with a view

From: Jonathan Lewis <>
Date: Sun, 24 Apr 2005 10:03:12 +0100
Message-ID: <008601c548ac$71be5140$6702a8c0@Primary>

Given the early versions of Oracle, there are likely to be some queries where the
optimizer instantiates the view before
performing a join. The first example that springs to mind is the outer join:

Try something like this:

    select {cols}

        driver_table    d,
        table_a            a
        a.primary_key(+) = d.colX


Then replace table_a with the view equivalent. I think you'll find in the execution plan that the path stops using the primary key on table_a and switches to a full scan with VIEW instantiation appearing as the parent operation.


Jonathan Lewis The Co-operative Oracle Users' FAQ Public Appearances - schedule updated April 5th 2005

 On 4/22/05, Vlado Barun <> wrote:
> I'm looking for input on what possible negative side effects I could
> encounter, if I replace a table with a view.
> Situation:
> I need to add a few columns to an existing, non-partitioned, heap table
> (let's call it "a"). However, I have identified a large number of "select
> *
> " queries against this table. Finding where these queries are coming from
> and changing them is not feasible within the current environment.
> So, I'm basically looking at two alternatives:
> 1. rename the existing table to "b", add the new columns to it and
> create a view named "a". The view will not expose any of the new columns
> 2. create another table "a_ext", with the same PK as "a", enforce RI
> (cascade delete), add the new columns to it. Also add an update trigger on
> "a", in case the PK in "a" gets updated, so that the PK in "a_ext" gets
> updated too.
> I prefer #1, but I'm concerned about possible negative side effect of
> renaming the table. Let's not worry about downtime, since it's not an
> issue
> in this environment. However, since table "a" is a very critical table, we
> need to make sure we do not break any existing code.
> We use Oracle <>, and yes it is .0, not .4
> Question:
> Are there any operations that could fail as the result of replacing the
> table with a view?
> Vlado Barun, M.Sc.
> Senior Data Architect, Cadre5
> <> <>

Received on Sun Apr 24 2005 - 05:07:22 CDT

Original text of this message