Re: Reorganize Big Table with BLOB column

Hi Jonathan

thanks for answering.  Some does require further explanation:

I can get partitioning by year in place. It is however not supported by the software and would be just a mean of dividing the data into more manageable pieces.
So far I observed, that when trying to text index the complete table progress is dropping after a while. Actually it never finishes. So far my conclusion was that we lacked CPU as there is nearly no I/O. Is it possible that we were rather lacking memory (e.g. swapping) and I was missing the point?
Can I improve my chances to create the text index by partitioning the data resulting is lesser memory demand?

By manual parallelism do you refer to dbms_parallel execute ?

Regards

Lothar

Am 24.02.2021 um 19:05 schrieb Jonathan Lewis:

> 1) The index will have to be rebuilt anyway - one of the tables that
> makes up the "index" will hold a list of rowids from the table and the
> index will become UNUSABLE when you move the table (and you can't move
> the table ONLINE to avoid this).
> ,
> 2) The answer would be yes for basicfiles, but I have to pass on this
> one since you're using securefiles.
>
> 3) No.  Anyway, if there's been so much activity that the table is
> "fragmented" then the LOB segment is going to be a complete mess and
> the index is likely to be a disaster area anyway. Rebuilding the index
> with the largest possibly memory parameter is probably a good idea,
> anyway. (And if the claim about "fragmentation" is due to performance
> rather than anything else then maybe the problem is the way context
> updates indexes anyway.
>
> 4) If you want to do it online you have to use dbms_redefinition. As
> others have said, partitioning the table might be a good idea, and if
> you want to limit the time manual parallelism is probably the fastest
> option.
>
> Regards
> Jonathan Lewis
>
>
> On Wed, 24 Feb 2021 at 12:59, l.flatz_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch> <l.flatz_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch>> wrote:
>
> Hi,
>
> we have to move a completely fragmented table in an other Tablespace.
> The table contains About a billion rows (not partitioned ). It
> contains a BLOB column (securefile) storing a PDF.
> The big issue is the reconstruction of a text Index.
> Alter table move does fail in the sense that after a while it
> stucks with "write complete" waits.
> My collegue wants alter table move because she does not want to
> rebuild the text index. That is a Nightmare because of parsing .
>
> Questions:
>
> 1. I believe even alter table move will rebuild the index behind
> the Scenes anyway. True?
> 2. Will the LOB Locator Change if the LOb is relocted? (So that
> means does the lob locator have a physical charateristics as a
> rowid)
> 3. Is there a way to rebuild the text index but avoid reparsing?
> 4.  what is the best practise to move such a table to a new
> tablespace? ( I have a Code solution that pulls a CLOB over a
> db link using a pipelined table function. I could Combine that
> with this idea :
> http://mareklall.blogspot.com/2015_06_01_archive.html
> <http://mareklall.blogspot.com/2015_06_01_archive.html>)
>
>
> Reagards
>
> Lothar
>
>

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