Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Questions regarding table partitioning

Posted on 2009-02-10
3
Medium Priority
?
414 Views
Last Modified: 2013-12-18
Question:
1- how do I partition a table that already exists and is not pratitioned
2- if a table is partitioned , for example, each year sits in its own partition, when  a new year occurs, will Oracle create the new partition automatically?
3- If I want to search for a date range that spans two or more partitions, does Oracle handle this for me by my use of the WHERE in a SELECT statement or do I have to specify that partitions I need to access?
4- I have a huge table with millions of rows and plenty of indexes.But my SQL code runs slow. Assuming that the code is efficient, will partitioning help speed up the code excution?
0
Comment
Question by:joekeri
3 Comments
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 300 total points
ID: 23611091
1- how do I partition a table that already exists and is not pratitioned
You must create a new partitioned table and use insert.. select to transfer the data from the old unpartitioned table to the new partitioned table.

2- if a table is partitioned , for example, each year sits in its own partition, when  a new year occurs, will Oracle create the new partition automatically?
No, you must create the partition, but there's nothing to stop you creating all the partitions you will ever need when you create the table or any time after the table has been created.

3- If I want to search for a date range that spans two or more partitions, does Oracle handle this for me by my use of the WHERE in a SELECT statement or do I have to specify that partitions I need to access?
Yes, Oracle will identify the partitions that contain the data you are searching for, you don't specify which partitions need to be accessed.  You can also create partitioned and global indexes to help improve query performance.

4- I have a huge table with millions of rows and plenty of indexes.But my SQL code runs slow. Assuming that the code is efficient, will partitioning help speed up the code excution?
Partitioning itself is not always the way to speed up queries and may actually make them run more slowly.  Partitioning WILL speed up the removal and addition of data providing it is done based on the partition key i.e. removing a year involves removing a partition and adding a year involves adding a partition.  Tuning queries is a huge topic in its own right so its difficult to say what will/will not work for you.  If your queries always using the partition key to filter the rows then your queries should run faster as they will need to access less data.  This will also be dependant on having the correct indexes (on each partition).
0
 

Author Closing Comment

by:joekeri
ID: 31545320
Thank you for your input. It was very helpfull...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23628729
2 - note that when you upgrade to 11g,  you can create partition definitions that WILL automatically create new partitions as the data for them is needed
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month11 days, 10 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question