Wouldn't you still want some kind of range? Your hash plan just says
"cut this one physical table into X tables". Adding a new physical
back end table would be difficult because you would need to rebalance
the data. If you have any kind of date field, you can put together a
form of range partitioning based around your current insert volume.
This would give you the advantage of possibly being able to add
partitions without re balancing and to possibly have queries prune via
the view. If you still want to go the hash route, you could just use
the MOD() function on a sequence generated value to get your buckets.
netcomradeNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<4178271e.878829389_at_localhost>...
> I should've explained better.
> The purpose is to have separate physical 'structures' in the
> background. 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..
>
> Thnx.
>
> On Fri, 22 Oct 2004 06:19:25 +1000, "Howard J. Rogers"
> <hjr_at_dizwell.com> wrote:
>
> >NetComrade wrote:
> >
> >> All,
> >>
> >> How would you do hash partitioning on a table (e.g. you were running
> >> Standard edition).
> >>
> >> With range partitioning, I assume it's easy, just create a view on top
> >> of X tables (e.g. for every month).
> >>
> >> Thanks
> >> .......
> >> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> >> remove NSPAM to email
> >
> >Use a hash cluster (are they EE or SE?? I can't recall. But you certainly
> >don't need the partitioning option to make them work). A Hash Cluster chops
> >up a single table into multiple internal 'ranges' ( or 'hash buckets'), and
> >a 'select where ID=763' gets optimised into 'ah, 763 would be stored in
> >that part of the segment, so I won't bother looking at the other parts'.
> >Whilst you are still eliminating bits of a segment from being
> >scanned/searched, which is a bit like partition elimination, you are
> >actually working with just one segment.
> >
> >They can work extremely well.
> >
> >Regards
> >HJR
>
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email
Received on Mon Oct 25 2004 - 17:39:01 CDT
