·  SQL – Index

 

- Info:

  - Idexes improve the speed of queries when indexed columns are part of search criteria defined in WHERE clause.

 

- Create:

  - Index can be created from one or more columns.

    CREATE INDEX INDX_PEOPLE

    ON PEOPLE (NAME, AGE);

 

- Create – Complex:

  - There are may parameters that can be set while creating index.

    CREATE INDEX INDX_PEOPLE

    ON           PEOPLE(NAME)

    TABLESPACE   TBL_IDX

    COMPRESS     1

    ONLINE

 

- Delete:

  - Index can be deleted simply by using DROP statement.

    DROP INDEX  INDX_PEOPLE;

 

- Use:

  - Indexes are implicitly used whenever indexed columns are part of WHERE clause.

    SELECT *

    FROM   PEOPLE

    WHERE  AGE > 50;