From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Is data import from Oracle table to Postgres table in Ora2pg consecutive or random?
Date: Tue, 12 Sep 2023 14:19:19 +0200
Message-ID: <20230912121919.zo74rbdhflpgp2eq@hjp.at> (raw)
In-Reply-To: <CAM2ukh+iP-bFOm=5YpZQka9uyZ6JHtmnYojPCvCD1Vavv5_kaA@mail.gmail.com>
References: <CAM2ukh+iP-bFOm=5YpZQka9uyZ6JHtmnYojPCvCD1Vavv5_kaA@mail.gmail.com>
On 2023-09-08 17:19:01 +0700, duc hiep ha wrote:
> I want to migrate a large Oracle table to PostgreSQL, which is approximately
> 200GB in size and includes a LOB segment. This table has a primary key column
> named "ID," which increments by one unit (similar to a sequence). During the
> migration of this large table, sometimes the process terminates prematurely
> (due to network issues, etc.). As a result, I have to restart the import
> process either from the beginning or by using a WHERE clause in the ora2pg
> configuration file to import the remaining data from where it was previously
> terminated.
>
> However, I've noticed that the import process in ora2pg doesn't seem to follow
> a consecutive order, starting from a small ID number and progressing to a
> larger ID number. Instead, it appears to import data randomly from various rows
> of the table. This makes it impossible to continue importing data from the
> point where it was terminated using a WHERE clause in ora2pg.
>
> My questions are:
>
> - Is it correct that ora2pg imports data from the Oracle table to the
> PostgreSQL table randomly and not sequentially from a smaller ID to a larger
> ID?
Oracle doesn't in general order tables by primary key (it does offer
"index organized tables", but the default is "heap organized"), so a
simple "select * from tablename" will return rows in semi-random order.
I think the rowid is in ascending order (but I can't test that at the
moment) so you may be able to use the rowid in your where clause.
> - Or can we add additional parameters to the ora2pg.conf file to control this
> process and ensure that the data is imported sequentially, following the
> primary key from smallest to largest?
I can't answer that.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: hjp-pgsql@hjp.at
Subject: Re: Is data import from Oracle table to Postgres table in Ora2pg consecutive or random?
In-Reply-To: <20230912121919.zo74rbdhflpgp2eq@hjp.at>
References: <CAM2ukh+iP-bFOm=5YpZQka9uyZ6JHtmnYojPCvCD1Vavv5_kaA@mail.gmail.com> <20230912121919.zo74rbdhflpgp2eq@hjp.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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