All about using INDEXES

Indexes are used to improve the performance of data retrieval. They are automatically created when primary key or unique key is created. You can manually create it using CREATE INDEX statement and CREATE TABLE statement.

CREATE INDEX with CREATE TABLE
SQL> create table test
(tno number(3) primary key using index (create index tno_idx on test(tno)), tname varchar(20));

SQL> select index_name, index_type from user_indexes where table_name='TEST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TNO_IDX NORMAL

[OR]

SQL> create table test (tno number(3), tname varchar(20));

Table created.

SQL> create index tno_idx on test(tno);

Index created.

SQL> alter table test add primary key (tno)
using index tno_idx;

Table altered.

NOTE that index is automatically created when you specify a column as primary key.