Reorganize Big Table with BLOB column
From: <l.flatz_at_bluewin.ch>
Date: Wed, 24 Feb 2021 13:59:26 +0100 (CET)
Message-ID: <1706810946.21270.1614171566227_at_bluewin.ch>
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:
I believe even alter table move will rebuild the index behind the Scenes anyway. True? Will the LOB Locator Change if the LOb is relocted? (So that means does the lob locator have a physical charateristics as a rowid) Is there a way to rebuild the text index but avoid reparsing? 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
Date: Wed, 24 Feb 2021 13:59:26 +0100 (CET)
Message-ID: <1706810946.21270.1614171566227_at_bluewin.ch>
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:
I believe even alter table move will rebuild the index behind the Scenes anyway. True? Will the LOB Locator Change if the LOb is relocted? (So that means does the lob locator have a physical charateristics as a rowid) Is there a way to rebuild the text index but avoid reparsing? 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-lReceived on Wed Feb 24 2021 - 13:59:26 CET