Re: partitioning table

Hi,
yes I just found out.

I was under tha assumption that i could partition my data on a given col,then if I changed that col oracle would automatically move the data to another partition. But that is not the case.

the problem is that there are over 10k records, and if I do a select on col1=n where n is a number between 1-10, it's taking about 10-14 seconds to return the records that are needed, the users say it ain't fast enougth.

I want to devide the data up into descrete areas so that oracle knows where to find all the col1=n and does nto check the full 10k records. steve

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

> 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
Received on Fri Jun 15 2001 - 05:02:16 CDT