What is Views

A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.
For example, the employees table has several columns and numerous rows of information. If you want users to see only five of these columns or only specific rows, then you can create a view of that table for other users to access.

View shows only few selected columns from base table.

Why Views Are Used

Views provide a means to present a different representation of the data that resides within the base tables. Views are very powerful because they let you tailor the presentation of data to different types of users. Views are often used to:

Provide an additional level of table security by restricting access to a
predetermined set of rows or columns of a table For example, Figure 5–5 shows how the STAFF view does not show the salary or commission_pct columns of the base table employees.

Hide data complexity
For example, a single view can be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.

Simplify statements for the user
For example, views allow users to select information from multiple tables without actually knowing how to perform a join.

Present the data in a different perspective from that of the base table
For example, the columns of a view can be renamed without affecting the tables
on which the view is based.

Isolate applications from changes in definitions of base tables
For example, if a view’s defining query references three columns of a four column table, and a fifth column is added to the table, then the view’s definition is not affected, and all applications using the view are not affected.

Express a query that cannot be expressed without using a view
For example, a view can be defined that joins a GROUP BY view with a table, or a view can be defined that joins a UNION view with a table.

Save complex queries
For example, a query can perform extensive calculations with table information. By saving this query as a view, you can perform the calculations each time the view is queried.

How to Define Constraints on Views?

Data warehousing applications recognize multidimensional data in the Oracle database by identifying Referential Integrity (RI) constraints in the relational schema. RI constraints represent primary and foreign key relationships among tables. By querying the Oracle data dictionary, applications can recognize RI constraints and therefore recognize the multidimensional data in the database. In some environments, database administrators, for schema complexity or security
reasons, define views on fact and dimension tables. Oracle provides the ability to constrain views. By allowing constraint definitions between views, database administrators can propagate base table constraints to the views, thereby allowing applications to recognize multidimensional data even in a restricted environment. Only logical constraints, that is, constraints that are declarative and not enforced by Oracle, can be defined on views. The purpose of these constraints is not to enforce
any business rules but to identify multidimensional data. The following constraints can be defined on views:

Primary key constraint
Unique constraint
Referential Integrity constraint

Given that view constraints are declarative, DISABLE, NOVALIDATE is the only valid state for a view constraint. However, the RELY or NORELY state is also allowed, because constraints on views may be used to enable more sophisticated query rewrites; a view constraint in the RELY state allows query rewrites to occur when the rewrite integrity level is set to trusted mode.

What is Indexes

Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. Indexes are the primary means of reducing disk I/O when properly used. You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns
if you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:

CREATE INDEX employees_idx1 ON employees (last_name, job_id);
CREATE INDEX employees_idx2 ON employees (job_id, last_name);

Oracle provides several indexing schemes, which provide complementary performance functionality:

B-tree indexes
B-tree cluster indexes
Hash cluster indexes
Reverse key indexes
Bitmap indexes
Bitmap join indexes

Oracle also provides support for function-based indexes and domain indexes specific to an application or cartridge.

The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value. Indexes are logically and physically independent of the data in the associated table. You can create or drop an index at any time without affecting the base tables or
other indexes. If you drop an index, all applications continue to work. However, access of previously indexed data can be slower. Indexes, as independent structures, require storage space. Oracle automatically maintains and uses indexes after they are created. Oracle automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users. Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the
performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table. The optimizer can use an existing index to build another index. This results in a much faster index build.

What is SQL Joins

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Different SQL JOINs

List the types of JOIN you can use, and the differences between them.

* JOIN: Return rows when there is at least one match in both tables
* LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
* RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
* FULL JOIN: Return rows when there is a match in one of the tables