Re: poor man's partitioning

NetComrade wrote:

> I should've explained better.
> The purpose is to have separate physical 'structures' in the
> background.

Well, views on top of a table doesn't produce separate physical structures. So you can't do it for range partitioning without true range partitions. And you can't do it for hash partitioning, either, without true hash partitioning.

> The table stores LOBs, and grows very large. It is backed
> up via exp. Sometimes when a few LOBs get corrupted, we like to
> restore only part of the table (e.g. partition) to a separate schema,
> and 'restore' the needed LOBs from there (instead of restoring 500G,
> we'd restore X/500G where X is the number of partitions)
>
> if only imp had a WHERE clause :)
>
> So not so much looking for performance improvement..

Fair enough. Motive is always important for questions like this. Hash clusters *are* poor man's hash partitioning, in terms of performance. But if you want it in terms of manageability, there's nothing but the full-blown product to turn to. That is, after all, why they charge an arm and a leg for such a desirable feature!

In terms of backup and recovery, have you looked at RMAN? And did you mention an Oracle version? Because if it's 9i or better, you can restore and recover individual blocks of a table. You can't get much more partitioned than that for recovery purposes! So if you're talking about genuine corruption, and not just user stuff-ups which you find inconvenient, the real issue here is not partitioning but your recovery mechanism.

Regards
HJR Received on Thu Oct 21 2004 - 16:58:26 CDT