Re: partitioning table

From your description of the problem I don't think Oracle 8 partitioning will help.

If you hash partition on 2 columns, then you only get performance benefit on queries with:

             col1 = const1
    and col2 = const2

If you range partition on (area, d_locked) you can't isolate all the d_locked = 0 rows into a single partition.

If you range partition on (d_locked, area) you don't get elimination on

        area = const1
and d_locked != 0

Composite partitioning might be the best bet.

    range on area
    hash on d_locked
you might get some benefit, but the sub-partitions may vary randomly in size.

On the other hand, Oracle 9 promises list partitions, which sound much more suitable for data partitioned in a very specific, discrete way.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



steve wrote in message <1ev1exv.rmk54p4p5we2N%E_at_E.COM>...


>HI,

>thanks,

>

>o.k

>next question.

>

>I have a huge address database (details), holding company name etc.

>

>2 fields I want to partition on.

>

>1.d_locked

>2. Area.

>

>Area ranges from 1 to n and holds the group that the data is in.

>

>1.=customer

>2.=dead customer

>3.=future customer

>4.=prospects

>

>D_locked is a  numeric field that is a binary break down for and item

>linked to details , invoices, special requirements etc.

>

>If it is 0 then there are no items linked to the details record.

>

>

>what i want to do is partition my table to speed up selects, fo

>applications using the details table.

>

>so I want to partition by area and D_locked.

>

>so lets say details

>

>area =1 with a non zero value of d_locked goes into the 1st partition

>area =2 with a non zero value of d_locked goes into the 2nd partition

>etc

>until the last partition

>  where any area that has d_locked = 0 goes.

>

>when records are added does oracle start at the first partition  and if

>the reword fits does it go in there ?, the oracle notes are non-too

>clear.

>

>steve

>

>

>

>

>

>

>

>

>

>Brian Pulcine <bpulcine_at_bellatlantic.net> wrote:

>

>> You have to create a new partition object, and load the data into it from

>> the original table.  There is no way to alter an existing table into

>> partitions.

>>

>>

>> "steve" <E_at_E.COM> wrote in message news:1ev08v6.ingj94royqxgN%E_at_E.COM...

>> > Hi,

>> > I have a table under 8i that is currently not partitioned, I want to

>> > partition it.

>> > can I  use the alter table command or do I have to re-create the table,

>> > but partitioned.

>> >

>> > the table is also replicated, and I do nto want to have to re-build the

>> > replication if I can help it.

>> >

>> > steve