One Star

[resolved] Manage Partition/Truncate Partition

Hi,
I need to insert dat in oracle partitioned table (year-month), suppose we have created all the partitions necessary!
Before insert I need to truncate partition.
The only way I know is run a pre-job with tOracle_Row using variables where I have:
alter table tab_name truncate partition context.yearmonth
There's another simple and better way?
Because my solution suppose to have partition name saved into a variable, but if i have into data flow two months,
I must run pre_job and insert_job two times separatly. I want run one/two jobs only one time.
tOracleOutput have UPDATE/INSERT option but my performance decade so much, and I have around 30 millions of record each months.
Thanks in advance,
Gino.
1 ACCEPTED SOLUTION

Accepted Solutions
Seventeen Stars

Re: [resolved] Manage Partition/Truncate Partition

There is currently no other way to execute database special SQL like yours.
As far as I know, you should not insert into a partition. You can simple insert into the main table and Oracle decided by the given field content in which partition is must be added.
It is always a matter of how fast is your storage. I would recommend to discuss the usage of table spaces with your database administrator. It could be a good thing to have for the most recent partitions dedicated table spaces.
For more performance you should take a look to the Oracle bulk input components.
2 REPLIES
Seventeen Stars

Re: [resolved] Manage Partition/Truncate Partition

There is currently no other way to execute database special SQL like yours.
As far as I know, you should not insert into a partition. You can simple insert into the main table and Oracle decided by the given field content in which partition is must be added.
It is always a matter of how fast is your storage. I would recommend to discuss the usage of table spaces with your database administrator. It could be a good thing to have for the most recent partitions dedicated table spaces.
For more performance you should take a look to the Oracle bulk input components.
One Star

Re: [resolved] Manage Partition/Truncate Partition

Hi jlolling,
thanks for reply, sorry for the late but i work hard in these days.