jagdish Venkat
asked on
Database Partitioning
Hi All
Here are my doubts:
Everything done in oracle DB.
I have a table X that is partitioned. (partitioned by surrogate key)
I am creating a temporary table Y by storing partition wise from table X .
I have also stored with partition-name and partition number.(no partition) in temp table Y.
(I will do some manipulation in table Y and merge them back to parent table X)
Is it possible to merge all partitions at once. (sample query structure please..)
Do I need to partition the temporary table in the same way as Parent table X to enable merge by partitions.(sample query please)
thanks in advance
Regards
Jags
Here are my doubts:
Everything done in oracle DB.
I have a table X that is partitioned. (partitioned by surrogate key)
I am creating a temporary table Y by storing partition wise from table X .
I have also stored with partition-name and partition number.(no partition) in temp table Y.
(I will do some manipulation in table Y and merge them back to parent table X)
Is it possible to merge all partitions at once. (sample query structure please..)
Do I need to partition the temporary table in the same way as Parent table X to enable merge by partitions.(sample query please)
thanks in advance
Regards
Jags
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
When I see TEMP tables and Oracle I get nervous. Many times these are used by people coming from non-Oracle databases. Oracle rarely needs them.
Why not just make your changes in the main table?
How you create the temp table shouldn't matter. Tables are tables are tables.
How you "merge" the data back in should also not really matter. A lot depends on your exact situation. I put "merge" in quotes because that is a specific SQL function. I wasn't sure if you meant you were going to use that of perform some other kind of merge operation.
Will you me modifying data that would cause rows to switch partitions?
I suppose that since you have the partition name where the row came from you can perform the DML on that specific partition and save some overhead. I would test that DML on the base table and let Oracle figure out the partition to see how much time it might save you. If it isn't that much, just do the DML on the base table and you don't have to worry about preserving what partition the row came from.
If you have to use the captured partition name to put the rows back then you likely need to use dynamic SQL to build the statement and that has its own overhead with the parse on every statement.
If you can provide more specifics we can probably offer more specific advice.