pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBest practice to load a huge table from ORACLE to PG
12+ messages / 9 participants
[nested] [flat]
* Best practice to load a huge table from ORACLE to PG
@ 2008-04-26 13:25 Adonias Malosso <malosso@gmail.com>
2008-04-26 15:10 ` Re: Best practice to load a huge table from ORACLE to PG Joshua D. Drake <jd@commandprompt.com>
2008-04-26 16:13 ` Re: Best practice to load a huge table from ORACLE to PG Luke Lonergan <llonergan@greenplum.com>
2008-04-27 01:14 ` Re: Best practice to load a huge table from ORACLE to PG Jonah H. Harris <jonah.harris@gmail.com>
2008-04-27 13:01 ` Re: Best practice to load a huge table from ORACLE to PG Greg Smith <gsmith@gregsmith.com>
2008-04-28 19:59 ` Re: Best practice to load a huge table from ORACLE to PG Tino Wildenhain <tino@wildenhain.de>
0 siblings, 5 replies; 12+ messages in thread
From: Adonias Malosso @ 2008-04-26 13:25 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
columns table
from ORACLE to PGSQL.
The current approach is to dump the data in CSV and than COPY it to
Postgresql.
Anyone has a better idea.
Regards
Adonias Malosso
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
@ 2008-04-26 15:10 ` Joshua D. Drake <jd@commandprompt.com>
4 siblings, 0 replies; 12+ messages in thread
From: Joshua D. Drake @ 2008-04-26 15:10 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
Adonias Malosso wrote:
> Hi All,
>
> I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
> columns table
> from ORACLE to PGSQL.
>
> The current approach is to dump the data in CSV and than COPY it to
> Postgresql.
>
> Anyone has a better idea.
Write a java trigger in Oracle that notes when a row has been
added/delete/updated and does the exact same thing in postgresql.
Joshua D. Drake
>
>
> Regards
> Adonias Malosso
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
@ 2008-04-26 16:13 ` Luke Lonergan <llonergan@greenplum.com>
4 siblings, 0 replies; 12+ messages in thread
From: Luke Lonergan @ 2008-04-26 16:13 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; pgsql-performance
Yep just do something like this within sqlplus (from
http://www.dbforums.com/showthread.php?t=350614):
set termout off
set hea off
set pagesize 0
spool c:\whatever.csv
select a.a||','||a.b||','||a.c
from a
where a.a="whatever";
spool off
COPY is the fastest approach to get it into PG.
- Luke
On 4/26/08 6:25 AM, "Adonias Malosso" <malosso@gmail.com> wrote:
> Hi All,
>
> I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
> columns table
> from ORACLE to PGSQL.
>
> The current approach is to dump the data in CSV and than COPY it to
> Postgresql.
>
> Anyone has a better idea.
>
>
> Regards
> Adonias Malosso
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
@ 2008-04-27 01:14 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-04-28 21:37 ` Re: Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
4 siblings, 1 reply; 12+ messages in thread
From: Jonah H. Harris @ 2008-04-27 01:14 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
On Sat, Apr 26, 2008 at 9:25 AM, Adonias Malosso <malosso@gmail.com> wrote:
> I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
> columns table
> from ORACLE to PGSQL.
The fastest and easiest method would be to dump the data from Oracle
into CSV/delimited format using something like ociuldr
(http://www.anysql.net/en/ociuldr.html) and load it back into PG using
pg_bulkload (which is a helluva lot faster than COPY). Of course, you
could try other things as well... such as setting up generic
connectivity to PG and inserting the data to a PG table over the
database link.
Similarly, while I hate to see shameless self-plugs in the community,
the *fastest* method you could use is dblink_ora_copy, contained in
EnterpriseDB's PG+ Advanced Server; it uses an optimized OCI
connection to COPY the data directly from Oracle into Postgres, which
also saves you the intermediate step of dumping the data.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
2008-04-27 01:14 ` Re: Best practice to load a huge table from ORACLE to PG Jonah H. Harris <jonah.harris@gmail.com>
@ 2008-04-28 21:37 ` Adonias Malosso <malosso@gmail.com>
2008-04-29 03:31 ` Re: Best practice to load a huge table from ORACLE to PG Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Adonias Malosso @ 2008-04-28 21:37 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: pgsql-performance
Jonah,
Thank you for the answer. Good to know about this enterprise DB feature.
I´ll follow using pgloader.
Regards.
Adonias Malosso
On Sat, Apr 26, 2008 at 10:14 PM, Jonah H. Harris <jonah.harris@gmail.com>
wrote:
> On Sat, Apr 26, 2008 at 9:25 AM, Adonias Malosso <malosso@gmail.com>
> wrote:
> > I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
> > columns table
> > from ORACLE to PGSQL.
>
> The fastest and easiest method would be to dump the data from Oracle
> into CSV/delimited format using something like ociuldr
> (http://www.anysql.net/en/ociuldr.html) and load it back into PG using
> pg_bulkload (which is a helluva lot faster than COPY). Of course, you
> could try other things as well... such as setting up generic
> connectivity to PG and inserting the data to a PG table over the
> database link.
>
> Similarly, while I hate to see shameless self-plugs in the community,
> the *fastest* method you could use is dblink_ora_copy, contained in
> EnterpriseDB's PG+ Advanced Server; it uses an optimized OCI
> connection to COPY the data directly from Oracle into Postgres, which
> also saves you the intermediate step of dumping the data.
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
> Edison, NJ 08837 | http://www.enterprisedb.com/
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
2008-04-27 01:14 ` Re: Best practice to load a huge table from ORACLE to PG Jonah H. Harris <jonah.harris@gmail.com>
2008-04-28 21:37 ` Re: Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
@ 2008-04-29 03:31 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Jonah H. Harris @ 2008-04-29 03:31 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
On Mon, Apr 28, 2008 at 5:37 PM, Adonias Malosso <malosso@gmail.com> wrote:
> Thank you for the answer. Good to know about this enterprise DB feature.
No problem.
> I´ll follow using pgloader.
That's fine. Though, I'd really suggest pg_bulkload, it's quite a bit faster.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
@ 2008-04-27 13:01 ` Greg Smith <gsmith@gregsmith.com>
2008-04-28 07:49 ` Re: Best practice to load a huge table from ORACLE to PG Dimitri Fontaine <dfontaine@hi-media.com>
4 siblings, 1 reply; 12+ messages in thread
From: Greg Smith @ 2008-04-27 13:01 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
On Sat, 26 Apr 2008, Adonias Malosso wrote:
> The current approach is to dump the data in CSV and than COPY it to
> Postgresql.
You would have to comment on what you don't like about what you're doing
now, what parts need to be improved for your priorities, to get a properly
targeted answer here.
> I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
> columns table from ORACLE to PGSQL.
There is no one best practice. There's a wide variety of techniques on
both the Oracle and PostgreSQL side in this area that might be used
depending on what trade-offs are important to you.
For example, if the goal was to accelerate a dump of a single table to run
as fast as possible because you need , you'd want to look into techniques
that dumped that table with multiple sessions going at once, each handling
a section of that table. Typically you'd use one session per CPU on the
server, and you'd use something with a much more direct path into the data
than SQL*PLUS. Then on the PostgreSQL side, you could run multiple COPY
sessions importing at once to read this data all back in, because COPY
will bottleneck at the CPU level before the disks will if you've got
reasonable storage hardware.
There's a list of utilities in this are at
http://www.orafaq.com/wiki/SQL*Loader_FAQ#Is_there_a_SQL.2AUnloader_to_download_data_to_a_flat_file....
you might look for inspiration in that area, I know the WisdomForce
FastReader handles simultaneous multi-section dumps via a very direct path
to the data.
...but that's just one example based on one set of priorities, and it will
be expensive in terms of dollars and complexity.
As another example of something that changes things considerably, if
there's any data with errors that will cause COPY to abort you might
consider a different approach on the PG side.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
2008-04-27 13:01 ` Re: Best practice to load a huge table from ORACLE to PG Greg Smith <gsmith@gregsmith.com>
@ 2008-04-28 07:49 ` Dimitri Fontaine <dfontaine@hi-media.com>
0 siblings, 0 replies; 12+ messages in thread
From: Dimitri Fontaine @ 2008-04-28 07:49 UTC (permalink / raw)
To: pgsql-performance; +Cc: Greg Smith <gsmith@gregsmith.com>; Adonias Malosso <malosso@gmail.com>
Hi,
Le dimanche 27 avril 2008, Greg Smith a écrit :
> than SQL*PLUS. Then on the PostgreSQL side, you could run multiple COPY
> sessions importing at once to read this data all back in, because COPY
> will bottleneck at the CPU level before the disks will if you've got
> reasonable storage hardware.
Latest pgloader version has been made to handle this exact case, so if you
want to take this route, please consider pgloader 2.3.0:
http://pgloader.projects.postgresql.org/#_parallel_loading
http://pgfoundry.org/projects/pgloader/
Another good reason to consider using pgloader is when the datafile contains
erroneous input lines and you don't want the COPY transaction to abort. Those
error lines will get rejected out by pgloader while the correct ones will get
COPYied in.
Regards,
--
dim
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
@ 2008-04-28 19:59 ` Tino Wildenhain <tino@wildenhain.de>
4 siblings, 0 replies; 12+ messages in thread
From: Tino Wildenhain @ 2008-04-28 19:59 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
Adonias Malosso wrote:
> Hi All,
>
> I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
> columns table
> from ORACLE to PGSQL.
>
> The current approach is to dump the data in CSV and than COPY it to
> Postgresql.
>
Uhm. 101 columns you say? Sounds interesting. There are dataloaders
like: http://pgfoundry.org/projects/pgloader/ which could speed
up loading the data over just copy csv. I wonder how much normalizing
could help.
Tino
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
@ 2008-04-26 15:17 Potluri Srikanth <spotluri@ismartpanache.com>
2008-04-26 15:33 ` Re: Best practice to load a huge table from ORACLE to PG Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 12+ messages in thread
From: Potluri Srikanth @ 2008-04-26 15:17 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
But do we link oracle trigger to postgres trigger ?
i mean :
oracle trigger will take a note of what has been changed .
but then how do we pass those changes to postgres trigger ?
can u suggest any logic or algorithm ?
Regards,
Srikanth k Potluri
+63 9177444783(philippines)
On Sat 26/04/08 8:40 PM , "Joshua D. Drake" jd@commandprompt.com
sent:
Adonias Malosso wrote:
> Hi All,
>
> I�d like to know what�s the best practice to LOAD a 70 milion
rows, 101
> columns table
> from ORACLE to PGSQL.
>
> The current approach is to dump the data in CSV and than COPY it
to
> Postgresql.
>
> Anyone has a better idea.
Write a java trigger in Oracle that notes when a row has been
added/delete/updated and does the exact same thing in postgresql.
Joshua D. Drake
>
>
> Regards
> Adonias Malosso
--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org [1])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Links:
------
[1]
http://sitemail7.hostway.com/javascript:top.opencompose(\'pgsql-performance@postgresql.org\',\'\',\'\',\'\')
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 15:17 Re: Best practice to load a huge table from ORACLE to PG Potluri Srikanth <spotluri@ismartpanache.com>
@ 2008-04-26 15:33 ` Joshua D. Drake <jd@commandprompt.com>
2008-04-26 18:51 ` Re: Best practice to load a huge table from ORACLE to PG Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 12+ messages in thread
From: Joshua D. Drake @ 2008-04-26 15:33 UTC (permalink / raw)
To: spotluri@ismartpanache.com; +Cc: Adonias Malosso <malosso@gmail.com>; pgsql-performance
Potluri Srikanth wrote:
> But do we link oracle trigger to postgres trigger ?
>
> i mean :
>
> oracle trigger will take a note of what has been changed .
> but then how do we pass those changes to postgres trigger ?
I am assuming you can use the java trigger from oracle to load the
postgresql jdbc driver, make a connection to postgresql and perform
whatever statement needed to be done.
Sincerely,
Joshua D. Drake
P.S. It is possible that Oracle can't do this (I don't know)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Best practice to load a huge table from ORACLE to PG
2008-04-26 15:17 Re: Best practice to load a huge table from ORACLE to PG Potluri Srikanth <spotluri@ismartpanache.com>
2008-04-26 15:33 ` Re: Best practice to load a huge table from ORACLE to PG Joshua D. Drake <jd@commandprompt.com>
@ 2008-04-26 18:51 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 12+ messages in thread
From: Craig Ringer @ 2008-04-26 18:51 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: spotluri@ismartpanache.com, Adonias Malosso <malosso@gmail.com>; pgsql-performance
Joshua D. Drake wrote:
> Potluri Srikanth wrote:
>> But do we link oracle trigger to postgres trigger ?
>>
>> i mean :
>>
>> oracle trigger will take a note of what has been changed .
>> but then how do we pass those changes to postgres trigger ?
>
> I am assuming you can use the java trigger from oracle to load the
> postgresql jdbc driver, make a connection to postgresql and perform
> whatever statement needed to be done.
Note that this will be rather inefficient if you're obtaining a new
connection every time. It looks like Oracle's Java stored procedures and
triggers run in an appserver-like environment, though, so you should be
able to use a connection pool, JNDI, or similar.
Some Java stored procedure examples:
http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html
You could also use a Java trigger to send simpler change message, with a
serialized row if required, to an external app that's responsible for
updating the PostgreSQL database. That might cause less load on the DB
server.
The trouble with this approach, though, is that it might be hard to get
right when transactions roll back. An alternative is to use an Oracle
trigger that inserts records in a change tracking / audit table. You can
then periodically read and clear the audit table, using that change
history data to update the PostgreSQL database. This method has the
advantage of being transaction safe, as data will never become visible
in the audit table until the transaction making the changes has committed.
--
Craig Ringer
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-04-29 03:31 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-26 13:25 Best practice to load a huge table from ORACLE to PG Adonias Malosso <malosso@gmail.com>
2008-04-26 15:10 ` Joshua D. Drake <jd@commandprompt.com>
2008-04-26 16:13 ` Luke Lonergan <llonergan@greenplum.com>
2008-04-27 01:14 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-04-28 21:37 ` Adonias Malosso <malosso@gmail.com>
2008-04-29 03:31 ` Jonah H. Harris <jonah.harris@gmail.com>
2008-04-27 13:01 ` Greg Smith <gsmith@gregsmith.com>
2008-04-28 07:49 ` Dimitri Fontaine <dfontaine@hi-media.com>
2008-04-28 19:59 ` Tino Wildenhain <tino@wildenhain.de>
2008-04-26 15:17 Re: Best practice to load a huge table from ORACLE to PG Potluri Srikanth <spotluri@ismartpanache.com>
2008-04-26 15:33 ` Joshua D. Drake <jd@commandprompt.com>
2008-04-26 18:51 ` Craig Ringer <craig@postnewspapers.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox