Creating a B-tree Index

You create an index using CREATE INDEX:

CREATE [UNIQUE] INDEX index_name ON
table_name(column_name[, column_name ...])
TABLESPACE tab_space;

Item	Meaning
UNIQUE	means that the values in the indexed columns must be unique.
index_name	the name of the index.
table_name	a table.
column_name	the indexed column.
tab_space	the tablespace for the index.

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));



SQL> CREATE INDEX i_emp_name ON emp(ename);

Index created.

SQL>

You can enforce uniqueness of column values using a unique index.

SQL> CREATE UNIQUE INDEX i_employee_no ON emp(empno);

Index created.

SQL>

You can also create a composite index on multiple columns.

SQL> CREATE INDEX i_employees_no_name ON emp(ename, empno);

Index created.

SQL>

Creating a Function-Based Index

In the previous section you saw the index i_customers_last_name.

SQL> CREATE INDEX i_func_employee_last_name
  2  ON emp(UPPER(ename));

Index created.

SQL>

QUERY_REWRITE_ENABLED has been set to true in order to use the function-based indexes.

SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;

System altered.

SQL>