Re: Limit on number of columns in an index
- From: "Ram Raman" <veeeraman@xxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- Date: Wed, 19 Mar 2008 15:13:27 -0500
Initially, I created indexes with 12 columns and 14 columns, but the queries
were doing index range scan, I thought it might be faster to include all the
query columns in the index. I am testing different combinations.
On 3/19/08, Ram Raman <veeeraman@xxxxxxxxx> wrote:
>
> The table has 60 columns. The problem is that this is a temporary table
> which gets used only when the process is run and gets truncated afterwards.
> While running, currenlty it gets populated with 1.4 million rows. The rows
> are inserted and then updated based on several criteria. Towards the end of
> the process, the values from this table are inserted to another permanent
> table. The process is taking more than 2x longer now (2+ hrs now) than
> before because of increased volumes of data.
>
> The insertion was slow, I tuned it. Many update statements are issued
> against the temp table. I am trying to make things faster. I tried creating
> few indexes ont he temp table(!). Hope they dont slow down the insertions.
> I did not try parallelizing the queries.
>
> Env: Pplsoft, 10g
>
>
>
> On 3/19/08, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx> wrote:
> >
> > Well, as with all things, there's no hard rule…but 30 does seem like a
> > lot…..
> >
> >
> >
> > How many total columns in the base table? Have you considered making
> > the table an IOT instead?
> >
> >
> >
> > -Mark
> >
> >
> >
> > *--
> > Mark J. Bobak*
> > *Senior Database Administrator, System & Product Technologies*
> > ProQuest
> > 789 E. Eisenhower, Parkway, P.O. Box 1346
> > Ann Arbor MI 48106-1346
> > +1.734.997.4059 or +1.800.521.0600 x 4059
> > mark.bobak@xxxxxxxxxxxxxxx
> > www.proquest.com
> > www.csa.com
> >
> > *ProQuest...*Start here.
> >
> >
> >
> > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Ram Raman
> > *Sent:* Wednesday, March 19, 2008 1:59 PM
> > *To:* oracle-l
> > *Subject:* Limit on number of columns in an index
> >
> >
> >
> > Listers,
> >
> >
> >
> > Is there any limit on the number of columns a BTree index can have,
> > before it is considered a bad design. I see a need to build an index with
> > almost 30 columns.
> >
> >
> >
> > Thanks
> >
>
>
Other related posts:
