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 im****tant 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 im****ting 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@[EMAIL PROTECTED]
http://www.gregsmith.com
Baltimore, MD
--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
