Re: Newbie has Oracle questions…

In article <940d1b$ljk$1_at_nnrp1.deja.com>,   doug_clark_20_at_my-deja.com wrote:
> Newbie has Oracle questions…
>
> 1. I’ve got a whole bunch of table in a Oracle database I want to
> concatenate into a single view, where I would like to be able to
 modify
> the data in the view if I wished. All the separate tables have a
 index
> that is unique for all of the tables. So far I’ve tried the below…
>
> Create view NEW-VIEW as
> Select * from X-A
> Union select * from X-B
> Union select * from X-C
>

At least use UNION ALL and not UNION (the latter would eliminate duplicate records in tables, that means the Oracle has to sort all the tables to check for duplicates which is of course terribly slow; the former is just what you want).

Anyway I would use partitioning for that task (namely a table whose partitions are your X-A,B,C tables); check Oracle docs which are very clear on that subject.

Also I don't remember if you can update a UNION ALL-ed set of tables (certainly you can't update a UNION-ed table), but you of course can (and very efficiently) update a partitioned table. A quick test should solve all doubts, but I don't have an Oracle instance handy, sorry.

HTH
Alberto
Alberto

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 17 2001 - 16:15:30 CST