Informatica Finally Does Direct Path Inserts
Well the title really says it all -- for a long time (well, since Informatica was born, really) you had to use its External Loader interface to work with SQL*Loader in order to get direct path inserts. The "bulk" switch was just bulk-binding.
So I heard from the excellent Informatica forum at Empowered Holdings that from version 7.1.2 direct path Oracle loads were native, and invoked by the "bulk" switch on the target. As it happens I got hold of 7.1.3 this afternoon. I tested it by reading from a copy of DBA_OBJECTS and inserting in both bulk and conventional mode into a table with the COMPRESS attribute set -- "Bulk" gives compression, the other does not, therefore "Bulk" is generating direct path inserts.
I'm so happy! Well, sort of.
Unfortunately we had another use for SQL*Loader, with was to load data in zoned decimal format, and unless I've lost my powers of documentation searching this is stil an alien concept to Informatica.
So there's some good news and some bad, but on the whole, this is a step forward.
posted by David Aldridge @ 7:21 PM
8 Comments:
Do you know if Informatica supports partition exchange?
SQL*Loader? You're so retro. Get with the program and use external tables :)
How are the eyes?
Cheers
Tim...
Anon ..
Not directly, but it does of course interface with stored procedures and from there the possibilities are pretty much endless.
You could just pass the name of the fact table on which the exchange is to take place and then let the procedure work out the name of the non-partitioned table (assuming that you have a god naming convention in place), and read the first row of that table to deduce which partition is to be exchanged, for example.
Tim...
Alas, source data files are on a different machine to the database ... as is the Informatica server, so as to reduce the impact of ETL on the database.
David,
IT seems like Informatica has so many limitations and you have to write stored procedures and call it from Informatica in many cases. In that case why should one use Informatica (or a similar ETL tool) instead of hand-coding?.
An excellent question, and I'll just mention that I hold no particular love of the Informatica product suite -- I could hand-code in Oracle just as happily, and probably with a faster and more robust execution.
My two cents is that tools like Informatica come into their own in larger organizations where multiple platforms are used and need to be consolidated. Because the logic of all of the transformations is abstracted to its own syntax, in this case Informatica's, you can consolidate 99% of your ETL/ELT logic in a single environment that one set of trained personel can understand.
The alternative is to have Oracle experts, DB2 experts, SQL Server experts, XML experts all working together to try and produce a cohesive system.
In a single RDBMS environment then the benefits become much more murky -- I would dearly love to be able to perform a MERGE statement in Informatica in the same way that I can in Oracle, but it isn't there (although I suppose wizards are avaialble to set up such a functionality).
I guess that there will always be these particular features of DBMSs that will never be native to a tool like Informatica -- just as there are features of SQL Server that will never be readily invoked by an ETL system hosted and hand-coded on Oracle without some interface to T-SQL code on the other database.
I will be OK with an ETL tool even if it does not support MERGE or Partition exchange or parition merging as long as they have their ways of doing the upsert opertion as fast as PL/SQL MERGE or ways of loading millions of records without impacting the data warehouse users as partition exchange can do. But I hear Informatica or data stage don't have any such features other than calling a stored procedure. Is that true?
I can't speak for Data Stage, but with Informatica you execute DDL statements bycalling stored procedures -- IMHO not a big deal, because if I was coding the ETL in pure PL/SQL I'd encapsulate the partition exchange (or partition creation, or calls to DBMS_Stats etc) in their own procedures anyway.
Post a Comment
<< Home