Re: Q: local managed tablespace and tempfile

Ted Chyn wrote:
> questions:
>
> 1. would storage parameter in table definition override locally
> managed tablespace in 8.1.7.

No. But there's a subtlety. Suppose your tablespace has uniform extent sizes of 1MB, and your create table statement includes a storage clause that says "INITIAL 10MB NEXT 15MB".

Then your table will be created with 10 1MB extents. So, in a sense, the storage clause at the table level has had some effect on the tablespace. But no, all extents must be of the uniform size.

If your tablespace is autoallocate, the subtelty gets even subtler. Oracle allocates whatever size extents it deems appropriate. Usually, these start at 64K, then increase to 1M, 8M and 64M. But if your storage clause for the table starts out wanting 10M, it is likely that Oracle will skip giving you any 64K extents, and move straight on up to the 1M ones. Likewise, if you'd asked for an INITIAL of 200M, the tablespace would probably have allocated 4 64M ones.

In any case, the NEXT extent is totally ignored. Going back to the first example, you've acquired 10 1M extents. If you now 'alter table blah allocate extent', then you will be given an 11th 1M extent. The fact that your storage clause asked for 15M is neither here nor there.

>
> 2. what are advantage and disadvantage of specifying storage parameter
> when creating temp tbs using tempfile ?

The big advantage is efficiency of swaps to temporary tablespace. What gets swapped there in the first place? PGAs that have run out of sort_area_size when a user does an order by or a group by etc. So how much gets swapped to TEMP? Er, your sort_area_size. Now, let's say you have a 1M sort_area_size, it would be a bit daft to only have 64K extents because you let the tablespace do autoallocate. It would work, though.

But suppose your sort_area_size was (weirdly) 350K. Now if the tablespce only allocates 64K or 1M or 8M extents, you're not going to fit 350K of data into any extent without there being wasted space. If you've got 64K extents being allocated, then your swap to TEMP is actually requesting 5.4 extents. So you'll be allocated 6, and that means you've actually chewed through 384K of space for 350K of data... and 34K of disk space just gets wasted.

Regards
HJR
>
> thnx a lot;
> ted chyn
Received on Thu Jan 23 2003 - 16:55:32 CST