Questions regarding table partitioning

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?
joekeriAsked:
Who is Participating?
 
MilleniumaireCommented:
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
 
joekeriAuthor Commented:
Thank you for your input. It was very helpfull...
0
 
sdstuberCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.