Re: Reorganize Big Table with BLOB column

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 <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)
>
>
> Reagards
>
> Lothar
>
>
>

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