Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: index creation fails because temp tablespace is too small
"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 2005Received on Thu Apr 28 2005 - 03:51:22 CDT