Alter a view to point to a new table?

[DanDanAUS]

Greetings all,

Is it possible in Oracle 9i to alter a view to point to a different table then the one that it already it points to?

I have one view that users will be constantly hitting. It pressents the view of a table that has 2 million records. The update to the table takes over 20 minutes which i cant wait for.

Compared to the update the full insert takes only 2 minutes. That is why i am willing to create a second table and change the view to point to the new table.

Also one more question: Is it possible to drop the view if there is a user that is using that view?

Sincerely
Dan

 

[Dagon]

You'd have to drop and re-create the view. There wouldn't be any problem dropping the view while the user is using it, although obviously the user may get an error if they are in the middle of a query.

Have you looked at why the update takes so long ? If it's being done properly, I wouldn't expect an update to take any longer than rebuilding the table from scratch.

 

[SantaMufasa]

Dan[sup]2[/sup],

There are multiple methods of redirecting your VIEW to point to different data, especially if you are using EXTERNAL tables:[ul][li]As Dagon mentioned, re-CREATE your VIEW to point to the new table,[/li][li]Create your VIEW using a SYNONYM name that you can "adjust to fit" to the table to which you choose to point,[/li][li](With an EXTERNAL table) rename the underlying flat file to "unhook" the old flat file, then rename the (new) alternate flat file to have the name that the EXTERNAL table definition expects.[/li][/ul]Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Deuparth gwaith yw ei ddechrau.

 

[Beilstwh]

A further option is to just allow it to run until it is done. with NO commits until the update is done and then issue the commit. The new data will instantly be there. Also,are you using the merge command or updates and inserts. The merge is MUCH faster.

Bill
Oracle DBA/Developer
New York State, USA