Re: Tables Partitioned by Range and Updating Data

Hi Bard,

I am not really sure that ur talking here about Oracle 8 Table partitioning.

In case ur on Oracle 7.3 then u can make use of Partitioned Views.

Partitioned views allow u to spread the data from a single table across multiple tables. The tables can be queried in a single qurey by using the union operator.

In case ur on Oracle 7.3.x then user the check contraint definition on the partition tables to determine which tables might participate in the query. The 7.3 optiomizer is smart enought to realized which partition table to look into base on the constraint. Note: To enable partition elimination, set the PARTITION_VIEW_ENABLED parameter to TRUE in ur database's init.ora file.

In case ur talking about 8. I think it could be an over kill since in 8 u can split a single table itself into multiple partition based on contraints. Each partition can reside in a different tablespace (if my understanding is correct, pl. verify) itself. So if u would do an update internal it would result in a insert to the destination and delete from the origination partition. As u have correctly stated.

Partitioning view I guess should keep every one happy in case ur on 7.3 or higher. Plus I guess all ur schema's are in the same instance (ur not using and dblinks).

HTH Sameer Mahajan
sameermj_at_usa.net

Brad Murray <BradMurray_at_SeeSigIfThere.com> wrote in article <MPG.f1b2b5fde20069f989853_at_news>...
> We have a database layout where we have a separate schema into which we
> load our data so it can be validated and manipulated before it gets moved
 

> into production. Right now these are totally separate tables in
 different
> schemas so the movement of records back and forth can be slow. If I
 create
> a table partitioned by range with the range being a flag to put the
> production data in one partition and the staging data in another I have
 the
> ability to keep my data in distinct areas which is an integrity issue
> according to the higher-ups and I also have the ability to deal with both
 

> of them as one table and still be able to use my indices. Currently I
 have
> to union the two tables together which would automatically not use the
> indices and is basically not usable.
>
> My main question is that if I do this, when I change the flag (for
 example
> to move data from staging to production) will the data actually move from
 

> one partition to the other? If it does, will it be a slow transaction
> because Oracle will be basically inserting into the one partition and
> deleting from the other under the covers?
>
> Please respond by e-mail to the below address as well as to the group.
>
> Cheers,
> Brad
> _______________________________________________________________________
> Bradley S. Murray Princeton Computer Consulting (609) 730-9100
> BradMurray(-at-)usa.net Pennington, NJ 08534-3612 Fax: 275-5651
>
Received on Tue Jan 06 1998 - 00:00:00 CST