Re: Little competition

[ryan_oracle]

hopefully i wont sound like a complete idiot, but what is wrong with wanting to be 
able to handle your own pctfree and pctused. Ok oracle handles the next and initial 
extent sizes...(which causes fragmentation). 

I use transportable tablespaces and in order to increase the time it takes to copy 
these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can 
cut 45 minutes off my load times by shrinking the data file? 
> 
> From: "Richard Foote" <[EMAIL PROTECTED]>
> Date: 2003/12/11 Thu AM 06:39:26 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Little competition
> 
> Little competition for you all :)
> 
> It's a two part question:
>   a..     What's wrong with the following piece of expert analysis ?
>   b..     Which well know "Oracle Guru" published this (and continues to display it 
> on his web-page) ?
> 
> 
> "Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using 
> automatic space management. This is a serious limitation because Oracle9i can't know 
> in advance about the amount of VARCHAR expansion in a table row, leading to 
> excessive row chaining and poor access performance."
> 
> SQL> create table
>   2   test_table
>   3   (c1 number)
>   4  tablespace
>   5   asm_test
>   6  storage
>   7   ( pctfree 20 pctused 30 )
>   8  ;
>  
>    ( pctfree 20 pctused 30 )
>      *
> ERROR at line 7:
> ORA-02143: invalid STORAGE option
> 
> However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED 
> parameters with locally managed tablespaces with automatic space management, it does 
> allow you to enter invalid settings for NEXT and FREELISTS settings"
> 
> 
> 
> You've gotta love it !!
> 
> Sorry no clues ....
> 
> Cheers ;)
> 
> Richard
> 
> 

Little competition for you all :)

 

It's a two part question:

•     What's wrong with the following piece of expert analysis ?
•     Which well know "Oracle Guru" published this (and continues to display it on his web-page) ?

 

 

"Sadly, Oracle9i doesn’t allow you to specify the value for PCTFREE if you’re using automatic space management. This is a serious limitation because Oracle9i can’t know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance."

 

SQL> create table
  2   test_table
  3   (c1 number)
  4  tablespace
  5   asm_test
  6  storage
  7   ( pctfree 20 pctused 30 )
  8  ;
 
   ( pctfree 20 pctused 30 )
     *
ERROR at line 7:
ORA-02143: invalid STORAGE option

However, here’s an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings"

 

 

 

You've gotta love it !!

 

Sorry no clues ....

 

Cheers ;)

 

Richard