Oracle Index

Oracle Index
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

Indexes are automatically maintained and used by Oracle.


SQL>create index test on company (cname);

Index created.

SQL>drop index test;

Index dropped.

Altering Oracle Index
SQL>ALTER INDEX ix_emp_01 REBUILD ;

Synonym

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

SQL>create public synonym suppliers for app.suppliers;

Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app..

SQL>select * from suppliers;



Drop public synonym suppliers;

SQL>drop public synonym suppliers;
This drop statement would drop the synonym called suppliers that we defined earlier.

Create View

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

SQL> CREATE OR REPLACE VIEW myView (First_Name, Last_Name) AS
select First_Name,Last_Name
from Employee
where Salary > 2000;

View created.


SQL> drop view myView;

View dropped.