Alias and Synonyms: An alias or synonym is a new name given to table for avoiding complexity involved with writing names as qualified table names. It is used to simplify SQL statements.
CREATE ALIAS EMPS FOR MODERATORS_ADMIN.EMPLOYEES
In the above query EMPS is the alias created for table name MODERATORS_ADMIN.EMPLOYEES.
The above query can also be written by using CREATE SYNONYM statement.
CREATE SYNONYM EMPS FOR MODERATORS_ADMIN.EMPLOYEES
After an alias or synonym is defined it can be used in other SQL statements.
SELECT NAME, EMPS.SALARY, OFFICE, FROM EMPS, OFFICES
For dropping an alias, DROP ALIAS statement is used.
DROP ALIAS EMPS
For a synonym:
DROP SYNONYM EMPS
Index: Index is used in SQL for fast access of rows of a table based on the values of one or more columns. Index stores the data values and pointers to rows sorted in ascending or descending order. Sorting is done for the DBMS to find an entry quickly in the index. A SQL user will not know that there is an index used in the database. Index is just used to locate and find items quickly. Index is generally used for columns where you anticipate regular search or columns where there are many inserts and updates.
Disadvantages of index:
1) Index takes additional space in the database.
2) Index has to be updated whenever a row is added to the table. Also, it has to be updates every time the indexed column is updated in an existing row.
To create an index, CREATE INDEX statement is used.
CREATE UNIQUE INDEX EMPL_SAL_NUM ON EMPLOYEES (EMPL_NUM, SALARY)
The above query creates an index for EMPLOYEES table according to EMPL_NUM, and SALARY columns.
UNIQUE keyword is used to specify that columns being indexed must contain a unique value for every row in the table.
To drop an index, DROP INDEX statement is used.
DROP INDEX INDEX EMPL_SAL_NUM