Sunday, 19 July 2015

Estimating the Size of an Index before creating it

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEE');

DECLARE
  ALLOC_BYTES NUMBER;
  USED_BYTES  NUMBER;
BEGIN
  DBMS_SPACE.CREATE_INDEX_COST('CREATE INDEX IDX_EMPLOYEE_ENAME ON SCOTT.EMPLOYEE(ENAME)' , USED_BYTES ,ALLOC_BYTES );
  DBMS_OUTPUT.PUT_LINE(' USED MEMORY ' || USED_BYTES      /1024/1024 || ' MB ' );
  DBMS_OUTPUT.PUT_LINE(' ALLOCATED MEMORY ' || ALLOC_BYTES/1024/1024 || ' MB ' );
END ;
/

No comments:

Post a Comment