AW: Text Index Create takes forever

Hi Lothar, having written a couple of Oracle Data Cartridges, and Oracle Text is just another albeit out of the box from Oracle, as a strategy I would explore whether you can migrate over the 800 million row table AND the underlying Oracle Text indexes (that are just ‚tables‘ embodied with a ROWID link to the parent table as required by the ODCI; the names of these tables ‚used to be‘ similar to the base table prefixed by I$, K$ and a few other letters I cannot remember), then scripting a bit of DML to update the new/migrated ROWID’s manually. This way you could avoid the time expensive component, that is the lexical analysis etc., of recreating the index from scratch.

Mike
http://www.strychnine.co.uk <http://www.strychnine.co.uk/>

> Am 25.11.2019 um 14:53 schrieb l.flatz_at_bluewin.ch:
>
> HI Jonathan,
>
> yes, I thought of it too. Of course the index will become less efficient. BTW: was there not any preference to start a sync job on commit?
> I can' t find it ...
>
> Regards
>
> Lthar
> ----Ursprüngliche Nachricht----
> Von : jonathan_at_jlcomp.demon.co.uk
> Datum : 25/11/2019 - 15:37 (MN)
> An : oracle-l_at_freelists.org
> Betreff : Re: Text Index Create takes forever
>
>
> Since it's going to be a killer anyway. hash partition about 16 or 32 ways while you move it.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> Sent: 25 November 2019 13:17
> To: oracle-l_at_freelists.org
> Subject: Text Index Create takes forever
>
> Hi,
>
> we have a 8oo million rows table that we want to migrate to an other Tablespace.
> The key point is the migration of a Text index based on a BLOB (Secure file) that contains PDF.
> The recreation of the Index takes weeks. It is CPU bound and the time is spend parsing the BLOB.
> So far it seems the best Strategy to gfo as high parallel as possible.
> Any idea to speed this up? DB version is 12.2.0.1.
>
> Regards
>
> Lothar
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l