RE: Text Index Create takes forever

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 25 Nov 2019 09:43:39 -0500
Message-ID: <00d601d5a39e$ba5c2960$2f147c20$_at_rsiz.com>


That is a good idea, especially if the contents of the tablespace is no more than about 2x the size of the blob table.

Can text indexes be partitioned? I'd have to look that up. If they can and you're licensed for partitioning and there is a useful partitioning key that might speed things up.

Did you reserve a special temp tablespace and temporarily alter the user to use that temp tablespace for just this index creation, possibly on SSD in the case your other storage is slower?

You might also consider a separate tablespace for the index. IF it is the only object in the destination tablespace for the index and IF the table is the only table in the other new tablespace, and IF the tablespace files are on distinct i/o performance stacks, you will be on an edge case where the readers of the table own the underlying device cache layers and positions, the index writing owns the write positioning, and presumably TEMP is yet somewhere else.

This, of course, is only useful on independently known "stripe sets" which are not routinely configure any more. Elimination of the seeks is not a very big win on large cache depending on layers and whether you're on ssd at the bottom, but if space occupation of the various layers of cache is a competition, separation may optimize i/o including any cpu involved depending on how and where cache is managed.

Whew. Also avoid CTD on this. If you have a test system you can configure as above, that may be the most useful way to see if that configuration increases headroom and/or throughput on whatever your pacing resource is.

(Which is also probably the first thing you should check when you already have a single process of interest to optimize.)

So sorry I have not run performance tests on Oracle's text indexes and I have rarely used them. Sounds interesting.

Good luck,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Monday, November 25, 2019 9:09 AM
To: oracle-l_at_freelists.org
Subject: Re: Text Index Create takes forever

Hi Lothar!

Why not use transportable tablespace mechanism to just move the tablespace files to different storage? The table would stay where it is, but the files would be relocated. Alternatively, how about moving everything except the monster table to different tablespace?

Regards

On 11/25/19 8:17 AM, l.flatz_at_bluewin.ch wrote:
> 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

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

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




--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 25 2019 - 15:43:39 CET

Original text of this message