RE: Limit on number of columns in an index

From: David Kurtz <info_at_go-faster.co.uk>
Date: Fri, 28 Mar 2008 21:34:11 -0000
Message-ID: <007801c8911b$77bcb720$0a0a0a0a@GOFASTER4>

I think you might be confusing Oracle's Global Temporary tables, with what PeopleSoft calls a temporary table or record.  

PeopleSoft creates multiple copies of working storage tables it terms 'temporary records'. Different instances of the same Application Engine batch program are allocated to different tables. Thus preventing processes from contending on shared tables. This allows the programs to use TRUNCATE instead of DELETE, and it also allows different versions of the table to have different CBO statistics  

see
http://blog.psftdba.com/2008/01/global-temporary-tables-and-peoplesoft.html  

You could replace these tables with Global Temp Tables - but you would have to disable the restart capability on the App Engines (which counts as a customisation) and you would have to create the tables manually, rather than use the PeopleSoft Application Designer.  

If you want to make the tables into IOTs, you are going to have to create the table manually. App Designer won't generate the DDL to create IOTs. Also PeopleSoft does not create primary keys.  

PeopleSoft often truncates and repopulates working storage tables. Have you considered use of the APPEND hint. Though again, this would be a customisation of the application code.  

regards

---

David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com <http://www.psftdba.com/>
DBA Blogs: PeopleSoft: http://blog.psftdba.com <http://blog.psftdba.com/> , Oracle: http://blog.go-faster.co.uk <http://blog.go-faster.co.uk/> PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman
Sent: Monday, March 24, 2008 12:50 PM
To: oracle-l
Subject: Re: Limit on number of columns in an index Importance: High  

   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_at_gmail.com> 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_at_proquest.com> 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


 <mailto:mark.bobak_at_il.proquest.com> mark.bobak_at_il.proquest.com

 <http://www.proquest.com/> www.proquest.com

 <http://www.csa.com/> www.csa.com


ProQuest...Start here. 

 

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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


 





--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 28 2008 - 16:34:11 CDT