Creating, Altering, and Dropping an Index With SQL

Creating, Modifying, and Dropping an Index Using SQL

-- create an index on a single column to make queries faster on that column
CREATE INDEX emp_hiredate_idx ON employees (hire_date);

-- rename the index
ALTER INDEX emp_hiredate_idx 
  RENAME TO emp_hire_date_idx;

-- drop the index
DROP INDEX emp_hire_date_idx;

-- create an index on two columns to make queries faster on the first column
-- or both columns
CREATE INDEX emp_mgr_id_ix ON employees (employee_id, manager_id);
DROP INDEX emp_mgr_id_ix;

-- a function-based index precalculates the result and speeds up queries that
-- use the function for searching or sorting, in this case UPPER(last_name)
CREATE INDEX emp_upper_last_name_ix ON employees (UPPER(last_name));
DROP INDEX emp_upper_last_name_ix;