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} from driver_table d, table_a a where 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. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated April 5th 2005 On 4/22/05, Vlado Barun <vlado@xxxxxxxxxx> 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 8.1.7.0 <http://8.1.7.0>, 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 > > www.cadre5.com <http://www.cadre5.com> <http://www.cadre5.com/> > -- //www.freelists.org/webpage/oracle-l