One Star

[resolved] ORACLE PARTITIONED TABLE

Hi!
I'm new and i want insert in a specific partion of a oracle table is possible with talend?
Thanks in advance
1 ACCEPTED SOLUTION

Accepted Solutions
Seventeen Stars

Re: [resolved] ORACLE PARTITIONED TABLE

No that is not what I am tried to explain. tOracleOutput does not create new partitions - of course not.
You have to declare your partitions by your self, but you can declare as much as you need - also for the fare furture, because a empty partition in Oracle does not allocate space, it is only a configuration nothing else.
I suggest creating - per script - as much as your application lives for the next - let me say - 10 years and the real partition indeed will be created automatically if there are values for it.
7 REPLIES
Moderator

Re: [resolved] ORACLE PARTITIONED TABLE

Hi,
It is possible to use talend to take actions in a specific partition of a oracle table. As long as the JDBC and Oracle support the specific partition of a oracle table, talend support that too.
I think it is same that inserting data in a specific partition of a oracle table or do it in a ordinary table.
You can take a try for it. Pay more attention on your SQL language, as we known that there are some differences between ordinary table and specific partition of table.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] ORACLE PARTITIONED TABLE

Hi,
the only way that I have found is by tOracleRow Component using for example:
insert into t1 select * from tb2 where DT_timestamp >= DATE '2012-0101' and DT_timestamp < DATE '2013-0102'
there is another way?
Thanks,
Gino.
Moderator

Re: [resolved] ORACLE PARTITIONED TABLE

Hi,
Perhaps the component tMap is another way for your job design. Set a filter condition in input source. But there is a concern about the performance.
The tMap will be slower than writing a equivalent SQL . Because Sql query is executed on the database engine directly.
By the way we provide ELT components to community users
For more details, please refer to: UsingSQLtemplates and ELT components. Hope it will help you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: [resolved] ORACLE PARTITIONED TABLE

Oracle it self organize where your data sets are stored. It is not necessary to change anything in your SQL statement - and therefore in the Talend job - to read or write from a partitioned table in Oarcle (unlike in PostgreSQL!).
Oracle handle partitions absolute transparent, speak simply to the table, that it.
One Star

Re: [resolved] ORACLE PARTITIONED TABLE

Hi jlolling,
you are telling me that if I run job without any where conditions the tOracleOutput components assolve his task?!?
For example: I want to insert in partitioned table ( DT_timestamp is the field used for partitionig),
if the data_set are with DT_timestamp='April/2013' and the last partition where I entered rows is March/2013,
tOracleOutput adds a new partition and insert the rows?
Thanks for your help!!!
For Sabrina,
I see the ELT components now i make a test, very helpfull!!!
Seventeen Stars

Re: [resolved] ORACLE PARTITIONED TABLE

No that is not what I am tried to explain. tOracleOutput does not create new partitions - of course not.
You have to declare your partitions by your self, but you can declare as much as you need - also for the fare furture, because a empty partition in Oracle does not allocate space, it is only a configuration nothing else.
I suggest creating - per script - as much as your application lives for the next - let me say - 10 years and the real partition indeed will be created automatically if there are values for it.
One Star

Re: [resolved] ORACLE PARTITIONED TABLE

Ok I understand Smiley Happy!!! Thanks for help very helpfull...
Gino.