Date: Sun, 27 Apr 2008 09:01:46 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Adonias Malosso <malosso@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Best practice to load a huge table from ORACLE to PG
In-Reply-To: <8a5d3c890804260625o7ad0700fp79f951c58c242720@mail.gmail.com>
Message-ID: <Pine.GSO.4.64.0804270833430.11601@westnet.com>
References: <8a5d3c890804260625o7ad0700fp79f951c58c242720@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; CHARSET=X-UNKNOWN; FORMAT=flowed
Content-Transfer-Encoding: QUOTED-PRINTABLE
Content-ID: <Pine.GSO.4.64.0804270836432.11601@westnet.com>

On Sat, 26 Apr 2008, Adonias Malosso wrote:

> The current approach is to dump the data in CSV and than COPY it to=20
> Postgresql.

You would have to comment on what you don't like about what you're doing=20
now, what parts need to be improved for your priorities, to get a properly=
=20
targeted answer here.

> I=B4d like to know what=B4s the best practice to LOAD a 70 milion rows, 1=
01=20
> columns table from ORACLE to PGSQL.

There is no one best practice.  There's a wide variety of techniques on=20
both the Oracle and PostgreSQL side in this area that might be used=20
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=
=20
as fast as possible because you need , you'd want to look into techniques=
=20
that dumped that table with multiple sessions going at once, each handling=
=20
a section of that table.  Typically you'd use one session per CPU on the=20
server, and you'd use something with a much more direct path into the data=
=20
than SQL*PLUS.  Then on the PostgreSQL side, you could run multiple COPY=20
sessions importing at once to read this data all back in, because COPY=20
will bottleneck at the CPU level before the disks will if you've got=20
reasonable storage hardware.

There's a list of utilities in this are at=20
http://www.orafaq.com/wiki/SQL*Loader_FAQ#Is_there_a_SQL.2AUnloader_to_down=
load_data_to_a_flat_file.3F=20
you might look for inspiration in that area, I know the WisdomForce=20
FastReader handles simultaneous multi-section dumps via a very direct path=
=20
to the data.

...but that's just one example based on one set of priorities, and it will=
=20
be expensive in terms of dollars and complexity.

As another example of something that changes things considerably, if=20
there's any data with errors that will cause COPY to abort you might=20
consider a different approach on the PG side.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD=