From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Is data import from Oracle table to Postgres table in Ora2pg consecutive or random? |
Date: | 2023-09-12 12:19:19 |
Message-ID: | 20230912121919.zo74rbdhflpgp2eq@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Matthias Apitz | 2023-09-12 12:26:23 | Re: Is data import from Oracle table to Postgres table in Ora2pg consecutive or random? |
Previous Message | Graeme | 2023-09-12 11:49:57 | pg_update and encoding |