Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qg2Mc-00EDGc-QC for pgsql-general@arkaria.postgresql.org; Tue, 12 Sep 2023 12:19:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qg2Mb-008Eqm-Bp for pgsql-general@arkaria.postgresql.org; Tue, 12 Sep 2023 12:19:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qg2Ma-008EqX-Uv for pgsql-general@lists.postgresql.org; Tue, 12 Sep 2023 12:19:28 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qg2MW-00494m-E5 for pgsql-general@lists.postgresql.org; Tue, 12 Sep 2023 12:19:27 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id C6B86DDDA; Tue, 12 Sep 2023 14:19:19 +0200 (CEST) Date: Tue, 12 Sep 2023 14:19:19 +0200 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Is data import from Oracle table to Postgres table in Ora2pg consecutive or random? Message-ID: <20230912121919.zo74rbdhflpgp2eq@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="wwohojx726qp74fz" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --wwohojx726qp74fz Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2023-09-08 17:19:01 +0700, duc hiep ha wrote: > I want to migrate a large Oracle table to PostgreSQL, which is approximat= ely > 200GB in size and includes a LOB segment. This table has a primary key co= lumn > named "ID," which increments by one unit (similar to a sequence). During = the > migration of this large table, sometimes the process terminates premature= ly > (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 previou= sly > terminated. >=20 > However, I've noticed that the import process in ora2pg doesn't seem to f= ollow > a consecutive order, starting from a small ID number and progressing to a > larger ID number. Instead, it appears to import data randomly from variou= s 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. >=20 > My questions are: >=20 > - 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 lar= ger > 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 --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --wwohojx726qp74fz Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmUAV0EACgkQ8g5IURL+ KF2SFRAAjimSPVFHKoqWilhH9PVgnMCQBJvv/hIF1BdHBITcElmsoi1Gf9i+UYW3 Oq3B3qQjeA4xTccW7B3a9LFv4x6mIpCMUynHp4mVqAsgJf8xk+UvXcSLPbMn1XbY HvSto+TLZh4MjZoc25BnIUJ+GkS5XPwMHL5AopcntKT3LTAHHcrqBn60I4ibPWqG E579NpfYUNfDho3WaFfUKzzH1TnFTQg0FeOoCIbVtwJTXgSPe6xYqQ4jJciYtpPm 4rX4twXsdxmqz84o5i/eQC897YotxZpM/0asAquJnZhaBV2suwg41cdz+2p5av9h sko6ed9c7Bg6l1d9+cizpf9ywpbDZrg83t8EqL877RfTFk6xyobM5bfzYOIW6epR iE4PcrgT+27U+tpwS+KJjMq/6E+AXF44LDS0uDABSR8hFFM5wqFg86Aupg4dD0RY 0VOb7ZKpYQuKRXtGjvDnZpS+71CocbLMLKrsX+4bmROFYg1Ti3lXy7kNHNDRz2GS BstauqgeBygNT2tT4gZxAZ89pkqxrodDUm1awbuLrY4Hpz5Oq2pdZJoGQBsI9AHC jYJlGodY/Qnl3nekgYQtxQpU0yGbVung3spJnTidHPNasSUyRxOd1ywxCDS9BhDf dfVELg0A8Ugfx9IY9Zgx1g1eCtkU8QX1IepkcF65HdpSPkqbNfE= =Grj9 -----END PGP SIGNATURE----- --wwohojx726qp74fz--