RE: Partition

I agree with Dennis Williams. To know what is the right answer for you, first you should determine why you want the table partitioned in the first place. Is it so that you can easily "archive" old data by using the "alter table .... drop partition" command? Is it so that your queries will benefit from partition pruning? Or is it only to split the table across several tablespaces or have different storage parameters for each partition? (In the last case you can use hash partitions.)

Have you tried "alter table ... split partition ..." see the SQL Reference manual) to divide your "high" partition into smaller subsets??

What do you do with the table once it is exported? Is it for a backup of the data or for another reason?

To avoid snapshot too old on the export, you can try to export one partition at a time (if you are using range or list partitioning). For example if partition 1 is specified with "values less than (100)": exp scott/tiger tables=partner query=\"where partner_id \< 100\"

To change a partitioned table into a subpartitioned table, I think the only way is to do a create table ... as select ... or a create table ... / insert ... select ...

> -----Original Message-----
> From: Ishwar Tewari [mailto:[EMAIL PROTECTED]
>
> Jacques, the version is 9.0.1.1.1.
> Dennis, Thanks for the confidence I totally agree with you
> about designing
> and everything else.
> Could there be a temporary solution to get successful exports until we
> embark on this project.
> we are in a 24x 7 environment.
>
> -----Original Message-----
> DENNIS WILLIAMS
>
> I am fairly new to partitions myself and must relearn the
> commands each
> time I need to work with the partitions. So I will speak more to the
> philosophy of a DBA that doesn't like to end up in a jam.
> I get a bad feeling about hashing to get around an
> immediate problem. I
> also question the value of an export this large. Have you timed the
> re-import? It might take so long as to not be of practical value.
> My approach in this situation would be to first design how
> I want my
> system to be. Lay out the partitions I would like to have,
> etc. Then create
> a plan to get to the situation I want. Conduct tests to see
> what changes can
> be made in a reasonable time. First create a new partition
> that will take
> the newly arriving data so your problem doesn't continue to
> get worse. Then
> aim at creating new partitions one at a time that will split
> the existing
> too-large partition into smaller partitions in a manageable effort per
> partition. You may end up creating one each weekend for awhile.
> My theory is being DBA of a really large database is
> similar to being
> captain of a supertanker. You can't turn a large ship or a
> large database
> quickly. You must anticipate problems a long distance off.
>
> -----Original Message-----
> From: Ishwar Tewari [mailto:[EMAIL PROTECTED]
>
> I created a range partition table with the last high_value
> being maxvalue.
> The partition key is partner_id (an incremental sequence).
> Now pmax (the last partition) has become very big and gives
> "snapshot too
> old.." while exporting.
>
> Ultimate Plan
> =============
> I realize that the ultimate solution will be the split the
> partition pmax,
> into enough segments so that
> the new pmax has no data. Then at this point I will drop the
> pmax partition.
> Therefore when new partners come I will add
> partitions accordingly.
>
> Maybe
> =====
> But I was thinking If I can make pmax into a hashed
> subpartition, it will
> also distribute the data reasonably.
> I dont have to have the segments separated by the partner_id
> because the
> data are not evenly distributed to the partners.
> partner_id , count(*)
> 1 4646545542
> 2 11164546466
> ... and so on..

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).