Index in oracle

                                 Index in oracle

  An index is database object used primarily to improve the performance of SQL queries.

Following point taken care for creating index:-

1.    Type index
2. Tables column to include 
3. feature like compression, invisible or Parnellism
4. Uniqueness

Estimating the space an index will require

SQL> SET SERVEROUTPUT ON

DECLARE

v_used_bytes NUMBER(10);

v_Allocated_Bytes NUMBER(10);

BEGIN

DBMS_SPACE.CREATE_INDEX_COST

(

'CREATE INDEX IDX ON test(ID) ',

v_used_Bytes,

v_Allocated_Bytes

);

DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(v_used_Bytes));

DBMS_OUTPUT.PUT_LINE('Allocated Bytes: ' || TO_CHAR(v_Allocated_Bytes));

END;

/SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  

Used Bytes: 12

Allocated Bytes: 65536

PL/SQL procedure successfully completed.