Re: index creation fails because temp tablespace is too small

"Dutch_alien" <arian_at_bearen.demon.nl> wrote in message news:1114624431.499060.209170_at_l41g2000cwc.googlegroups.com...
> Hi,
>
> Jonathan.
> Would you mind explaining the formula? What do the numbers mean. And
> are they dependent on block-size, or init.ora parameters?
>
> Thanks for your reply.
>
> Regards,
>
> Arian
>
>

Very roughly:

When sorting to produce an index, each row of the dump to temp includes the indexed columns, plus extended rowids plus overhead.

Oracle uses 2 length bytes for each column

The extended rowid uses two length bytes to say that it is 10 bytes long - hence the 12.

I've just realised that I managed to forget the 4 extra bytes that Oracle seems to use for the length of the whole entry - so add 4 bytes to each row in the previous post.

Then each entry is written on a 4-byte boundary, hence the rounding up to a multiple of 4.

There's also a little block overhead - about 60 bytes per block lost (from memory) - but the whole calculation is just a rough indicator anyway, so I wouldn't worry too much about that - there are likely to be too many small errors with blank columns and missing index entries to be able to claim any greate degree of accuracy.

-- 
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