Re: Reorganize Big Table with BLOB column

Here's a cute trick for partitioning - though I know you've said it's not relevant - I've done a quick test that uses a varchar2() to store a date as YYYYMMDD, and with (automatic) list partitoning on a virtual column year defined as subsrt(date_col,1,4) I get partition pruning on: date_col between 'date_val1' and 'date_val2'

Regards
Jonathan Lewis

On Wed, 10 Mar 2021 at 12:50, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

>
> Yes. The partition key won't be used for partition pruning. The table is
> rather simple: id, BLOB , timestamp created and last modified. (Timestamps
> are stored as Varchar2, which practise I dislike.)
> I can construct something like partition key extracting year from
> timestamp created. The application would be totally unaware of the
> partition key.
> Effectively that would mean looping over partitions each and every time
> the table is accessed via the context index.
> I remember that even looping over empty partitions can slow queries. Can
> you guesstimate the effect? We are talking 11 yearly partitions right now.
>
> The table is accessed mainly by the id.
> Hash Partitioning on the id would be an option . However that partitions
> would be constantly growing in size.
>
> The Version is 19.0. It does allow partition a context index.
> In this case a global index would make more sense. However we might still
> have an issue due its size.
>
> "Not able to complete" - you said "write complete waits"
>
> Actually there waits happened when my colleague used alter table move. It
> turned out the reason for these waits were that the BLOB was set to cache.
> I changed the property to nocache and the waits are gone for good. We are
> having "direct path read"/"direct path write" waits right now.
>
> Thanks
>
> Lothar
>
>
> Am 06.03.2021 um 09:53 schrieb Jonathan Lewis:
>
>
> 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.
>
>
>

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