Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Partition - 11g (Oracle 11g Rel 1)
Oracle Partition - 11g [message #572936] Tue, 18 December 2012 15:16 Go to next message
ind9
Messages: 60
Registered: January 2009
Member
Hello All,
I have a question related to partitions and dividing into subpartitions on the existing table.
Situation is as follows:
1. we have an inventory table with a list partition on one column sales_desk_id.
2. This table contains millions of records. Due to concurrency and due to high amount of data inserts, now there is a need to make sub partitions based on sale_date.
Question: is there any way to make the subpartitions without dropping the tables?

Could you please suggest is there any way to do it? Thank you.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Re: Oracle Partition - 11g [message #572939 is a reply to message #572936] Tue, 18 December 2012 15:37 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
> Due to concurrency and due to high amount of data inserts, now there is a need to make sub partitions based on sale_date.
exactly how will having sub-partitions change the performance.
Most likely all new rows will be from today's sales & contend for the same sub-partition.

>is there any way to make the subpartitions without dropping the tables?
no
Re: Oracle Partition - 11g [message #572964 is a reply to message #572936] Wed, 19 December 2012 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_REDEFINITION if you want to do it online (but it neverthless drops the original table).

Regards
Michel
Re: Oracle Partition - 11g [message #572973 is a reply to message #572939] Wed, 19 December 2012 01:48 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
Hello BlackSwan,
Thanks for your reply
> Due to concurrency and due to high amount of data inserts, now there is a need to make sub partitions based on sale_date.
exactly how will having sub-partitions change the performance.
Most likely all new rows will be from today's sales & contend for the same sub-partition.

==> Quote:
There also allot of select statements being fired on the same table by some other users for some other users. so the idea is partition and sub partition might help us solving this issue.
Is there any other way to reduce this concurrency problems?


Thanks in advance.
Re: Oracle Partition - 11g [message #572974 is a reply to message #572973] Wed, 19 December 2012 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not partition in a way that it increases the contention.
Partition in a way that it decreases the contention as this is your purpose.

Regards
Michel
Re: Oracle Partition - 11g [message #573579 is a reply to message #572974] Mon, 31 December 2012 08:00 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
Thanks for your suggestion.
Could you please kindly give some more inputs or clues?

Quote:

Do not partition in a way that it increases the contention.
Partition in a way that it decreases the contention as this is your purpose.


I am trying to find some examples where it helps me to tune my application in a better way.
"partitions can decrease contention for a shared resource."
some demo example could help me to understand better.

Thanks in advance and have a happy new year.
Re: Oracle Partition - 11g [message #573587 is a reply to message #573579] Mon, 31 December 2012 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Data Warehousing Guide
Chapter 5 Partitioning in Data Warehouses

Regards
Michel

Re: Oracle Partition - 11g [message #573588 is a reply to message #573587] Mon, 31 December 2012 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
>"partitions can decrease contention for a shared resource."
above is true if/when you place the partitions on different physical disk volumes.
Re: Oracle Partition - 11g [message #573801 is a reply to message #573588] Thu, 03 January 2013 04:52 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
Dear Experts,
After reading the partition document posted in this forum, I am thinking to partition my tables further. Kindly validate my understanding
As of now table customer_tbl has been list partitioned by sales_dept_id. With this still I have performance issues and related AWR reports are attached.
./fa/10582/0/

How about if I do a composite partition like

Quote:
on partition by list(sales_dept_id)
subpartition by hash (customer_id) subpartitions 8


This is the same with other tables also.
Please provide me some hints if I will have to consider any other facts.


Thanks in advance
  • Attachment: awr.JPG
    (Size: 118.31KB, Downloaded 183 times)
Re: Oracle Partition - 11g [message #574159 is a reply to message #573588] Mon, 07 January 2013 08:48 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
Hello Experts,
Any suggestions for me?

Thanks in advance.
Re: Oracle Partition - 11g [message #574160 is a reply to message #574159] Mon, 07 January 2013 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+latch+cache+buffers+chains
Re: Oracle Partition - 11g [message #574171 is a reply to message #573801] Mon, 07 January 2013 10:27 Go to previous messageGo to next message
John Watson
Messages: 4410
Registered: January 2010
Location: Global Village
Senior Member
That snippet of an AWR report does not suggest that partitioning would be relevant.
You need to identify a problem before choosing a solution. No end user has ever telephoned the help desk to say "there is too much contention for the cache buffers chains latch". They call to say things like "this screen doesn't refresh fast enough",or "my report takes too long", or "the batch jobs were still running this morning". Do you know what SQL has problems? If you post the complete report, perhaps it will become clear how to address it.
Re: Oracle Partition - 11g [message #574175 is a reply to message #574159] Mon, 07 January 2013 10:39 Go to previous message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 31 December 2012 15:46
Database Data Warehousing Guide
Chapter 5 Partitioning in Data Warehouses

Regards
Michel

Previous Topic: ref cursor with oracle procedure
Next Topic: CREATING FUNCTION FOR "CASE STATEMENT"
Goto Forum:
  


Current Time: Thu Jul 31 23:13:05 CDT 2014

Total time taken to generate the page: 0.10024 seconds