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;

ItemMeaning
UNIQUEmeans that the values in the indexed columns must be unique.
index_namethe name of the index.
table_namea table.
column_namethe indexed column.
tab_spacethe 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>
Home »
Oracle »
Table » 

Index:
  1. Creating a B-tree Index
  2. Composite Index
  3. Retrieving Information on Indexes
  4. Modifying an Index
  5. Dropping an Index
  6. Creating a Bitmap Index
Related: