RE: Limit on number of columns in an index

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@xxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@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
 <mailto:mark.bobak@xxxxxxxxxxxxxxx> mark.bobak@xxxxxxxxxxxxxxx
 <http://www.proquest.com/> www.proquest.com
 <http://www.csa.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: