create_index_cost procedure

From: <>
Date: Thu, 2 Jul 2009 13:55:19 -0500
Message-ID: <>

Hello everybody:

I am using create_index_cost procedure (oracle to estimate the size of a future index and find an appropriate tablespace parameters for it.
Here is my code:


   l_used_bytes number;
   l_alloc_bytes number;

   dbms_space.create_index_cost (

      ddl =>

 'create unique index owner.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||

' tablespace TS1 nologging parallel 4',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes); end;

I am seeing a significant difference between used bytes and allocated bytes

Used Bytes = 723481137
Allocated Bytes = 1782579200

I thought that this is due to the tablespace's extent size (my tablespace is locally partitioned). So I tried running the same procedure with different values
of the tablespace name. The tablespaces I used had the extent size from 128K to 50M and yet the difference between used and allocated bytest did not change that much. So I think my reasoning of what is the root of the difference is incorrect. Does any one have a better explanation for this?

thank you

Gene Gurevich

Please consider the environment before printing this email.

Received on Thu Jul 02 2009 - 13:55:19 CDT

Original text of this message