Home » RDBMS Server » Performance Tuning » Does partitioning need constraints to be copied (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Does partitioning need constraints to be copied [message #583795] Sat, 04 May 2013 04:29 Go to next message
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 #583797 is a reply to message #583795] Sat, 04 May 2013 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Also have a look at DBMS_REDEFINITION package.

Regards
Michel
Re: Does partitioning need constraints to be copied [message #583799 is a reply to message #583795] Sat, 04 May 2013 06:07 Go to previous messageGo to next message
John Watson
Messages: 4611
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 #583800 is a reply to message #583799] Sat, 04 May 2013 06:17 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Thanks john

Then what should i do to make my table work faster.because ap_invoices_all contains so much of data.and i did the range partitioning by invoice date and i did it month wise.
if u want i can show u the whole process.please ,suggest me some best solutions.


Regards.

bhawna kamra
Re: Does partitioning need constraints to be copied [message #583802 is a reply to message #583800] Sat, 04 May 2013 06:24 Go to previous messageGo to next message
John Watson
Messages: 4611
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 #583804 is a reply to message #583802] Sat, 04 May 2013 06:34 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Please find the attachment enclosed.

i have some oracle reports.where i am getting output in nearly 4 hours to 5 hours.and 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.so i am doing partitioning and i have read that article so i m following this

http://www.runningoracle.com/product_info.php?manufacturers_id=12&products_id=372

so thought they have done so much research on that on the basis of that i am doing the partitioning.

thanks
bhawna
Re: Does partitioning need constraints to be copied [message #583806 is a reply to message #583804] Sat, 04 May 2013 07:36 Go to previous messageGo to next message
John Watson
Messages: 4611
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.
Re: Does partitioning need constraints to be copied [message #583907 is a reply to message #583806] Mon, 06 May 2013 23:10 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Hello John.

I ve asked my DBA about the criteria on the basis of what they want the partitioning.

Segment name owner bytes segment_type
MRP_SCHEDULE_CONSUMPTIONS MRP 19.95GB TABLE
GL_JE_LINES GL 19.01GB TABLE

they found the data is in a large amount in the table which is making the queries related to it,slow in the oracle reports .so we want the partitioning.

i have 10 tables with me of which i have to do the partitioning.

GL_JE_LINES
MTL_MATERIAL_TRANSACTIONS
MTL_TRANSACTION_ACCOUNTS
GL_IMPORT_REFERENCES
ZX_LINES_DET_FACTORS
MTL_CST_ACTUAL_COST_DETAILS
WIP_BIS_PERF_TO_PLAN
JAI_RCV_TRANSACTIONS
JAI_AP_TDS_INV_TAXES
XLA_AE_SEGMENT_VALUES
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
MRP_RELIEF_INTERFACE
WIP_REQUIREMENT_OPERATIONS
RCV_TRANSACTIONS
MRP_NET_RESOURCE_AVAIL
GL_BC_PACKET_HISTS

i am very new to this partitioning thing.so kindly help me in this.
Re: Does partitioning need constraints to be copied [message #583910 is a reply to message #583907] Mon, 06 May 2013 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Partitioning does NOT solve every & all performance problems.
It may have NO impact on your application.

[Updated on: Mon, 06 May 2013 23:53]

Report message to a moderator

Re: Does partitioning need constraints to be copied [message #583912 is a reply to message #583907] Mon, 06 May 2013 23:58 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
Then what else can we do with the tables?
Re: Does partitioning need constraints to be copied [message #583913 is a reply to message #583912] Tue, 07 May 2013 00:02 Go to previous message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>Then what else can we do with the tables?
You don't tune table.
You tune SQL!
Previous Topic: Performance for Table analysis
Next Topic: Query performance tuning for ETL
Goto Forum:
  


Current Time: Thu Oct 02 09:40:36 CDT 2014

Total time taken to generate the page: 0.14555 seconds