Re: Reorganize Big Table with BLOB column

Sounds like DBMS_REDEFINITION with partitioning, securefile and perhaps bigfile tablespace is in order. Everything can be pre-done before the cutover.

---

Thanks,


Shane Borden
sborden76_at_yahoo.com



> On Feb 24, 2021, at 9:00 AM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:
> 
> At a billion rows and growing, please convince the customer to partition the table, especially if a move/rebuild is already planned?  Such a table should have data lifecycle requirements (i.e purge, etc) upcoming, if not already known, so it makes sense to build these into the structure of the table, rather than relying on massive DML operations.  Range partitioning on a DATE column is appropriate as a starting consideration.
> 
> 
> 
> On 2/24/2021 4:59 AM, 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: 

>> 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 <http://mareklall.blogspot.com/2015_06_01_archive.html>)

>> 

>> Reagards

>> 

>> Lothar

>> 

>> 

>> 

> 


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