Estimate The Cost Of Create A New Index

Within Oracle We have the DBMS_SPACE package. One function of this package is estimate the cost of create a new indexes on an existing table. See an example;

 SET SERVEROUTPUT ON

DECLARE
  used_bytes NUMBER(38);
  alloc_Bytes NUMBER(38);
BEGIN

DBMS_SPACE.CREATE_INDEX_COST (
   'CREATE INDEX ZETA_INX ON siebel.movimiento(date_movement) ',
   used_bytes,
   alloc_bytes
);

DBMS_OUTPUT.PUT_LINE('Used Bytes (MBs): '       || round (used_bytes/1048576, 2));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes (MBs): '  || round (alloc_Bytes/1048576, 2));

END;
/

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s