| Does partitioning need constraints to be copied [message #583795] |
Sat, 04 May 2013 04:29  |
 |
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
|
Member |
|
|
I am doing partitioning in the table ap_invoices_all
and i have gone through all the process of
i am making a script for a table and process
1. Create the new partitioned table with the same column structure as the original and with the partitions.
2. Insert data from the original table to the partitioned. Use parallel DML.
3. Rename the indexes of the original table
4. Create indexes to the partition table with the same columns as the original indexes.
5. Save the source code of the original table triggers
6. Rename the triggers of the original table to OLD
7. Do the table renaming. Rename original table to OLD and the partitioned table to original.
8. Drop the synonyms for the OLD table and recreate to point to the new partitioned
9. Grant the appropriate privileges to new partitioned table.
10. Create the triggers to the partitioned table
i want to know do i need to copy the constraints of the original table to the partitioned table?
[Updated on: Sat, 04 May 2013 04:43] Report message to a moderator |
|
|
|
|
|
| Re: Does partitioning need constraints to be copied [message #583799 is a reply to message #583795] |
Sat, 04 May 2013 06:07  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You appear to be following the instructions you found earlier, http://www.runningoracle.com/product_info.php?cPath=63_89&products_id=373 in your previous post, http://www.orafaq.com/forum/mv/msg/187363/583196/148813/#msg_583196 Apart from not using dbms_redefinition, I think the author has not considered some critical points.
First, that table has about 20 indexes. Are going to make them local or global? If you partition by date, and then create local indexes the performance may degrade terribly.
Secondly, joins. That table is the parent in foreign key relationships with about 40 other tables. Are you going to get partitionwise joins to them? No way, unless you use reference partitioning throughout the whole environment.
If your objective is to improve performance, be very careful. Your plan might work, but it might be a disaster. I believe that with partitioning you need to be absolutely clear on the problem you are facing, and why your chosen strategy will fix it. Don't do it just because you can.
|
|
|
|
|
|
| Re: Does partitioning need constraints to be copied [message #583802 is a reply to message #583800] |
Sat, 04 May 2013 06:24 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've already told you: you have to define the problem you are facing. You say Quote:make my table work faster.because ap_invoices_all contains so much of data. which I think means (please can you try to use the best English you can, and no IM speak?) that you believe some queries are running slowly because the table is large. You have to prove this. What are the queries? What are the statistics on the table? What are the execution plans? Why do you think the size of the table has any impact? Why would partitioning help? How did you choose that partitioning strategy? What strategy are you applying to the indexes and related tables?
--update: fixed quote tags
[Updated on: Sat, 04 May 2013 06:25] Report message to a moderator |
|
|
|
|
|
| Re: Does partitioning need constraints to be copied [message #583806 is a reply to message #583804] |
Sat, 04 May 2013 07:36 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:my DBA told me to do the partitions.as he has seen some statistics and execution plans which is making the query to fetch the output in ages
You might want to ask your DBA why he has given you this instruction.
You seem determined to ignore what Michel and I say, so all I can add is "good luck to all your end users". They may need it.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
