Re: Reorganize Big Table with BLOB column

Do you mean the application doesn't support partitioning by year - or is your version of Oracle unable to partition a context index?

"Not able to complete" - you said "write complete waits" - there's a lot of data to be written, to several tables, indexes and lobs for a context index: I'd have to check the manuals to see what you can do about getting some of the work done with direct path writes, but I'd guess that your write complete waits relate to the volume of data being generated in the cache by a serial process. (It might be a good idea, though, to check the session stats for work done to achieve read-consistency e.g. undo records applied)

How can you have "nearly no I/O" but suffer from write complete waits? The two symptoms seem contradictory. What is the O/S saying about it?

If you partition the table AND the index can be locally partitioned in your version, then the scale of the problem changes because you could (I assume) create the index unusable then rebuild each partition in turn. Can you extract the parameters that are currently being used so that you can see if there's anything that's been changed from default? Could you copy a couple of million rows from the table and see how Oracle behaves creating an index on the subset.

Manual parallelism: I was thinking of something like: create empty table with local indexes (if it's possible), with all the physical segments required.
write a simple script that does "create table as select data for one year, create indexes, ecxhange partition" - then run as many copies as the machine will allow (vary according to what actually works)

Regards
Jonathan Lewis

On Thu, 4 Mar 2021 at 17:30, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> 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
>
>
>

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