Oracle Create Index

Oracle database indexes are very important for SQL performance tuning and they are crucial for SQL query optimization. SQL Performance Tuning recommend that you create Oracle index or indexes on some table fields which are used in where clause. We recommends also to do some tests, before you do code to production environment.

EXAMPLE:

SQL query (note field priority in WHERE clause):

SELECT *
FROM detail
WHERE priority = 2;

Create index:

For SQL query performance optimization we recommends to create oracle index on field priority.

CREATE INDEX TEST_USER.DETAIL_IDX ON TEST_USER.DETAIL
(PRIORITY)
LOGGING
TABLESPACE TEST_USER
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;