Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index creation fails because temp tablespace is too small

Re: index creation fails because temp tablespace is too small

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 28 Apr 2005 08:51:22 +0000 (UTC)
Message-ID: <d4q86a$3uk$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com>

"chao_ping" <zhuchao_at_gmail.com> wrote in message news:1114677437.006194.239460_at_l41g2000cwc.googlegroups.com...
> Thanks Lewis.
> As for the build index parallel for local index on partitioned table,
> I remember oracle does not do necessary use one PQ slave to read one
> partition, sort one partition, and then build the index for one partition.

I think this is version dependent, but I believe Oracle always has the to run parallel execution against partitioned tables on the basis of 'one slave = one (sub) partition', or on the basis of rowid ranges. I have a test case somewhere where one variant of 9.2.0.1did a partition-wise join, and another just blasted its way through by rowid ranges and hash distribution - when both data bases had the same set of spfile parameters and the data was generated by a script.

> THat is, it does not split the workload to different PQ
> slave according to partition range, it split the work load according to
> the rowid, is it right? I don't find any document talking about this.
> Can you give document/test result about this? Maybe we have to trace pq
> coordinator to find how actually oracle does?
>

One option for tracing is to enable event 10391. Level 64 will show you how the co-ordinator is generate data "granules".

> So if the above theory is correct, using partition to build the index
> won't save temp space, unless we use the trick you mentioned above.
> Right?

If Oracle works by rowid ranges in this case you would be correct. However, if it works by one-slave = one-partition, then (and I hadn't thought of this in my original post, maybe it's what DA Morgan had in mind) then perhaps you could set a low degree of parallelism and find that you are building only a few index partitions at a time, and can survive with the smaller TEMP.

As I say, the option to choose may be version dependent. On the other hand it may be present in all recent versions, but driven by a cost calculation whose parameters have changed so that the break point between the options occurs at a different volume of data.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005
Received on Thu Apr 28 2005 - 03:51:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US