Re: Limit on number of columns in an index
- From: "Ram Raman" <veeeraman@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 Mar 2008 07:49:37 -0500
To test performance, I defined few global temporary tables with indexes
in the process which was using temp tables. When it runs the temp table gets
created in the user's (sysadm) regular tablespace instead of being created
in the user's temp tablespace. I thought maybe the user lacks quota on temp
tablespace and tried to give unlimited quota, but found out that that does
not apply in 10g. Any idea why the process keeps creating temp
tables/indexes in the default tablespace? What is suspicious is sysadm's
default tablespace is called pstemp.
env: 10g/Psft.
TIA.
> On Wed, Mar 19, 2008 at 9:06 PM, 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:
