Use of Partitioning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Use of Partitioning

  1. #1
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    I read somewhere that partitioning the tables will only be effective in improving performance if parallel query operations are performed. Otherwise, they might be a bottleneck.
    In what situations should one partition tables?
    Parallel queies are set up by inserting hints in the queries or is there any other way?
    What are the OS requirements for enabling parallel queries?
    In what situations would partitioning be a bad choice?


  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Thumbs up


    I am sure Jeff hunter can answer you very precisely regarding all of your questions since he has used partitions frequently.

    From what I understand you partition tables for better performance if they are large and have many rows (over 1mil?). You can also partition rows by groups if let's say you need to delete a certain amount of rows (by group) on a very large table during certain times of the month or year or whatever.
    I can't imagine it hurting performance on a very large table if you partitioned it, but let's see what some of the experts say ...

    - magnus

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Oracle Parallel Processing is not something that can be explained with a couple of sentances.

    I would suggest that you buy and read "Oracle Parallel Processing" (O'Reilly, ISBN 1-56592-701-X).


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You don't have to use parallel query to utilize the performance benefits of partitioned tables. Just using partitions can spread your I/O over multiple physical devices. Also, the optimizer has advantages when doing FTS on partitions only. Parallel query definitely does help, but it is not a requirement.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I read somewhere that partitioning the tables will only be effective in improving performance if parallel query operations are performed. Otherwise, they might be a bottleneck.
    Where did you read that? That is not true.


  6. #6
    Join Date
    Apr 2001
    Posts
    219
    The system I inherited has poor partition purning. The developer overcame this by specifing the partition in the SQL and iterated through the partitions they needed. This iteration design increased performance greatly, but has a flaw. The flaw has to do with the columns of the tables that are partitioned. The partitioning columns are all dates, but the dates come many different sources. So, when you query across multiple tables and specify partitions you can end up with data missing in your result set. But hey, they just found out that they are losing about 33% of there data in the software, Ops!

    Here is a link to partitioning:
    http://otn.oracle.com/docs/products/...94/parpart.htm
    ______________________
    Applications come and go,
    but the data remains!

  7. #7
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Thanks for the link Zaggy. It was indeed educative and cleared lot of doubts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width