Index in oracle
An index is database object used primarily to improve the performance of SQL queries.
Following point taken care for creating index:-
- Type index
- Tables column to include
- feature like compression, invisible or Parnellism
- 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.
