Re: poor man's partitioning

Howard J. Rogers wrote:
>
> 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
I think the OP was referring to a single view on top of 'n' tables to simulate range partitioning, ala 'partitioned views' of the v7.3 ilk.

In terms of simulating hash partitioning, you could have an API in which you can pass every primary key through dbms_utility.get_hash_value, but then every DML needs to know this API, every query needs to know the API, etc etc etc...

... might be cheaper to buy the partitioning option :-)

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------