Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a fairly simple question that is proving hard to search for.

I need to create a table that will be partitioned and into which (lots of) data will be loaded.

Is it more efficient to create the table with the partitions or add the partitions after I have INSERTed the data?

ADDITION: To make sure I understand the complete picture, if I was to truncate this table to then load (lots of) data again, should I drop the partitions and then add after the load?

ADDTION #2: If I can INSERT the data in the order of the partitions, I assume that would be faster than not?

share|improve this question
2  
Create the partitions first. Partitioning an existing table is a time-consuming pain! –  Phil Feb 14 '13 at 20:40
add comment

1 Answer 1

up vote 5 down vote accepted

You can't partition an existing non-partitioned table. So if you want a partitioned table, you would need to create a partitioned table before inserting any data.

Assuming that you are not using interval partitioning (in which case you're telling Oracle how to automatically add new partitions based on the data), you would almost certainly want to create the appropriate partitions in advance. If you are range partitioning the table, you could, in theory, create a MAXVALUE partition for the table, load all the data, and then split the MAXVALUE partition into the individual partitions that you want. But that would involve moving all the data around multiple times which would be much less efficient than simply loading the data into the appropriate partition from the outset.

Inserting the data "in the order of the partitions" probably won't make a difference from a performance perspective. Oracle still has to determine for each row which partition to insert it into. Assuming your indexes are local, it may well be more efficient to load a staging table whose structure matches the structure of a partition, create the appropriate indexes on the staging table, and then doing a partition exchange to exchange an empty partition with the staging table.

share|improve this answer
    
Not strictly true. You can use DBMS_REDEFINITION, but that's me being pedantic as it requires double the disk space so that both co-exist simultaneously :) –  Phil Feb 14 '13 at 20:41
    
Not really the point of my question, but - I thought you could use ALTER TABLE to add partitions? –  JHFB Feb 14 '13 at 20:44
    
@Phil - Well, but in that case you're really not partitioning an existing table. You're creating a new partitioned table, moving all the data from the non-partitioned table over, dropping the non-partitioned table, and renaming the new one. DBMS_REDEFINITION is just a pretty wrapper to hide from other users the fact that you're doing all this. –  Justin Cave Feb 14 '13 at 20:45
    
@JHFB - You can ALTER an existing partitioned table to add new partitions, yes. You cannot ALTER an existing non-partitioned table to partition it. –  Justin Cave Feb 14 '13 at 20:46
1  
@JHFB - Updated my answer. –  Justin Cave Feb 14 '13 at 21:11
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.