RE: Reorganize Big Table with BLOB column

First, +42 to Tim's message.  

Second, it probably makes sense to store your blobs in the new incarnation out of line in a different tablespace.  

Third, if you do "Second" and have enough time of quiescence (and especially if there is some sort of date column that relates to purge frequency as well as when a row becomes static [if not actually born static]), then this might be one of the rare cases where filling in an attribute after the initial insert might be helpful. Might is an intentional weasel word. It also might not help, but I think the odds are in your favor of it then working or at least bifurcating the problem between the non-blob and blob data.  

So if you rename the old, create the new (partitioned cleverly, I hope), set up parallel by partition predicate streams, perhaps in ten parallel sets of jobs (as opposed to Oracle parallelism), but completely leave the blob out of the initial insertion. Then walk through each of your incomplete partitions (again as separate jobs at what you find to be the useful level of by job parallelism for your machine and the amount of its throughput you are allowed to swallow) copying over the BLOBs by matching primary at a reasonable commit size.  

When one of Oracle's automatic, on-line (or not), handy-dandy I'll do all the thinking for you tools fails, it is often less calendar time to completion to just do it the old fashioned semi-manual way.  

And if you do this and have additional indexes, you *may* want to physically order the queries feeding the insert so that your most frequently used index has the minimum possible cluster factor.  

Good luck.  

Lastly, +42 to Tim's message.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lothar Flatz
Sent: Wednesday, February 24, 2021 11:41 AM To: oracle-l_at_freelists.org
Subject: Re: Reorganize Big Table with BLOB column  

I would guess we will be running into that same issue, won't we?

Regards

Lothar

Am 24.02.2021 um 15:02 schrieb Shane Borden (Redacted sender sborden76 for DMARC): 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 wrote:

Hi,

 

we have to move a completely fragmented table in an other Tablespace.

The table contains About a billion rows (not partitioned
<https://rich-v02.bluewin.ch/cp/ge/images/2.gif> ). 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