Indexes in SQL are kind of like indexes in a book in that they list out where key pieces of data are in the main content. If you query records on a table that does not contain indexes, it may take longer to retrieve results than if it had them. The rule of thumb for creating indexes is to index the columns that are queried the most in a table. Only index the columns that you use most often and don’t index everything. Indexes take up space and as such, you would actually lose efficiency if you index too many things.
To create an index, us the CREATE INDEX keyword pair.
CREATE INDEX <indexname> ON <table1> (<column1>, <column2>, ...);
To create a unique index, where all of the data within the specified column(s) are unique, add the UNIQUE constraint before the INDEX keyword.
CREATE UNIQUE INDEX <indexname> ON <table1> (<column1>, <column2>, ...);
The COMPUTE STATISTICS keyword pair in Oracle gathers information on the specified columns during the index creation process. By doing this, the specified column(s) will route through the Oracle Optimizer that will determine a plan to execute queries in the most efficent way possible.
CREATE INDEX <indexname> ON <table1> (<column1>, <column2>, ...) COMPUTE STATISTICS;
If the index is already created without the COMPUTE STATISTICS, you can add it with the ALTER INDEX REBUILD statement.
ALTER INDEX <indexname> REBUILD COMPUTE STATISTICS;
To remove an object from your database, use DROP INDEX along with the given name of the index.
DROP INDEX <indexname>;
A view is a saved query that can be accessed at a later time by referencing its given name. Views are intended to act as variables that can store complex queries in their own associated tables. To create a view, use CREATE VIEW.
CREATE VIEW <viewname> AS <subquery>;
Once a view is created, you can reference that view as if it was a table of already queried results.
SELECT * FROM <viewname>;
To remove a view object from your schema, use the DROP VIEW keyword pair.
DROP VIEW <viewname>;