create_index_cost procedure

From: <genegurevich_at_discover.com>
Date: Thu, 2 Jul 2009 13:55:19 -0500
Message-ID: <OFA8001DEA.28046EB9-ON862575E7.00676BB5-862575E7.0067F4F2_at_discover.com>

Hello everybody:

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

declare

   l_used_bytes number;
   l_alloc_bytes number;
begin

   dbms_space.create_index_cost (

      ddl =>

 'create unique index owner.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||

' on owner.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' ||
' 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.

--
http://www.freelists.org/webpage/oracle-l

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