Implement Database partitioning for SQL Server and Oracle

One Star

Implement Database partitioning for SQL Server and Oracle

Hello,
We are using Talend for our product. Since the customer database has grown too large, ETL (insert & delete) are taking very long time. So we are thinking of implementing database partitioning.
I would like to learn how can the partitioning implemented through Talend. 
I am looking for solution where the code written in Talend should work for both SQL Server(2008R2 and above) and Oracle databases(11gR2 and above).
Regards
Arun
Sixteen Stars

Re: Implement Database partitioning for SQL Server and Oracle

You can use the tOracleRow component to run DDL statements or maybe build the functionality into an Oracle procedure and call it with the tOracleSP component. Unfortunately there are no components which are built specifically for the purpose of partitioning. 
If you want information on how to write DDL statements to partition your tables, you will need to Google that or speak to an Oracle/SQL Server developer.
One Star

Re: Implement Database partitioning for SQL Server and Oracle

Thanks for quick reply.
I am looking for ways of doing following steps through Talend:
1. Create small table with same structure as of partition table. 
2. Create indexes on small table similar to the indexes of partition table
3. Swap partition from partition table to small table for the date on which data modification is to be performed
4. Insert / Update / Delete the data into/from the small table as per business requirement
5. Swap partition from small table to partition table so that the data is permanently stored in system (partition table)
If there are any components in Talend which can help us to implement above steps, then we can get performance boost. Also we need that the code can work for SQL Server and Oracle. I have good knowledge of writing database procedures. But we can't maintain 2 sets of code which is when we write database procedures. Maintainability would be a big challenge as we have to do this for 20+ tables.
 
Regards
Arun
Sixteen Stars

Re: Implement Database partitioning for SQL Server and Oracle

Creating tables can be done with the tOracleOutput (and SQL Server equivalent) components. Take a look at the documentation for how to do this. 
Creating indexes you will need to do with tOracleRow components.
As for the rest of your requirement, I really think you need to do some research on what want and how Oracle and SQL Server achieve this. They are different databases. You will not be able to use exactly the same code for both I am afraid. 
Talend supply components which allow you to do some DDL work, but only minimal (see the tOracleOutput component for creating and dropping tables). Anything else can be done with Talend, but will require you to write the DDL statements correctly for the environment you want to carry out the work in. 
There is a misconception that Talend is here to make this kind of work possible with no knowledge of the environments you are working in. That may be true in some cases (by accident, not design), but it is generally not the case. Talend is here to make the development of ETL jobs easier (with knowledge of the domain) and faster. For very simple and mundane things, it will do it for you quite easily. For more complex scenarios you need to understand what you are doing without Talend, before trying it with Talend. You can then take advantage of many of the tools that make things easier/quicker in Talend and even use Java (you should learn Java if you don't already know it) to really open the doors of what is possible.