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;