Oracle- A View about various Views in oracle

A VIEW is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace. Basically a view does not contain data on its own but it is like a window, when we select from the view, it executes the statement which is stored in system tablespace.
Use of views
Restrict data access
Make complex query easy
Provide data independence
Present different views of the same data.

Types of Views
There are four types of basic views.
Simplex views
Complex views
Inline views
Materialized views
Simplex view means, which derives data from only one table, contains no functions or groups of data, Can perform DML operations through the view.
Complex view means, which derives data from more than one table, contains functions or groups of data, does not always allow DML operations through the view.
We can update the base table, by updating the view, these views we can call updatable views. If we perform the bellow operations in the view, we can not update the base table, these views we can call Non-Updatable views.
Note: Updatable Views can not include:
Set Operators (INTERSECT, MINUS, UNION, UNION ALL) ,
DISTINCT,
Group Aggregate Functions (AVG, COUNT, MAX, MIN, SUM, etc.) ,
GROUP BY Clause,
ORDER BY Clause,
CONNECT BY Clause,
START WITH Clause,
Collection Expression in a Select List,
Subquery in a Select List,
Join Query.

If pseudo columns are present they can not be included in an update statement.

Inline View is a select statement in the FROM clause of another select Statement. Inline views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query, this feature was introduced in oracle 7.2, in the MSSQl community called it as derived table in the postgresql community simply refers to it as a subselect.

Materialized view is a different approach in which the query result is cached as a concrete table that may be updated from the original base tables from time to time. It is most useful in data warehousing scenarios, where frequent queries of the actual base tables can be extremely expensive. Materialized view was first introduced in Oracle8i and they are part of a component known as summary management.

Loading & Refreshing the Materialized view
We can refresh the materialized views frequently by the following methods.
1. Full refresh the data.
2. Perform a fast refresh that is add/merge only the changes.
3. Automatically update a materialized view when ever changes are made.
For the Full refresh the data in MV we have three options they are complete refresh, fast refresh (Only Changes are applied) and force refresh. Full refresh can be performed by ONDEMAND, ONCOMMIT.
The ONDEMAND refresh is achieved by calling one of the procedures listed bellow, thus giving the DBA total control over when a materialized view is update.
DBMS_MVIEW.REFRESH
DBMS_MVIEW.REFRESH_DEPENDENT
DBMS_MVIEW.REFRESH_ALL_MVIEWS
The ONCOMMIT refresh method is chosen, whenever a materialized view is affected by changes made to the source data, the materialized view will automatically be updated to reflect this data.
Complete refresh of a materialized view occurs, it is first truncated and then all the data is loaded. Depending on the size of the materialized view, this could be a time consuming operation. Complete refresh is a good technique to use when
1. Number of new rows to be inserted is more than 50% of the cardinality of the tables on which the materialized view is based
2. There is no index on the materialized view that is usable for merging
3. The time required to perform a fast refresh is longer than a complete refresh.

Fast Refresh Some materialized views could be very large and the time required to regularly perform a complete refresh may not be available. Then alternative method is fast refresh, where only the changes to the fact table are applied against the materialized view.
In order to perform a fast refresh operation, changes made to the data must be recorded and this is achieved in one of two ways. If your data is only ever inserted into the database using SQL*Loader direct path, then the refresh mechanism will detect this and identify the new data to be loaded.
Query Re-write Can answer queries quickly, because rather than reading all of the actual data to obtain the answer, it instead transparently reads a materialized view which contains the required information. Materialized views were introduced in Oracle8i, and they contain the results of a pre-defined query, such as the sum of sales by region over a period of time. You may define as many materialized views as there is available storage space.
Query rewrite is enabled using the session/system parameter QUERY_REWRITE_ENABLED set to TRUE. When query rewrite is enabled, all SQL queries will be checked to see if a materialized view can be used. If this is possible, then your SQL query will be transparently rewritten by Oracle to use the materialized view. Therefore, no application change is required to take advantage of materialized views, and you should see the results of the query considerably faster. Since there is no effect upon the application code, the materialized views can be continually changed to reflect your current query workload.
In order to use query rewrite, you must create materialized views like the one shown below, which is pre-computing the sales for each month.
A SQL query does not have to exactly match the materialized view definition in order for it to be used, because query rewrite uses a number of different types of rewrite methods which are listed below. Therefore query rewrite is possible for the following scenarios:
Exact Match - where the join conditions and grouping columns in the query and materialized view match exactly
Aggregation to All - the materialized view is grouped by product and month, for example, but the query is by products
Rollup - query is by quarter, materialized view is at month level, but a dimension exists which defines how to rollup data from month to quarter
Join Back - column in materialized view is used to join back to dimension, e.g. mat view grouped on store_id, but query groups by store name
Filtered data - materialized view only contains part of the data, e.g. countries UK and USA and query requests data for the UK only
Since query results are being returned by using a materialized view, instead of reading the actual data, query rewrite also offers an integrity mode to give you the option of whether you want to read stale data. The session or system wide parameter QUERY_REWRITE_INTEGRITY can take one of three values:
STALE_TOLERATED - uses both fresh and stale materialized views to return query results and trusts that all declared relationships via constraints are good
TRUSTED - only uses a fresh materialized view and trusts that all declared relationships via constraints are good
ENFORCED - only uses fresh materialized views, and it will only use relationships that are based on enabled and validated primary/unique/foreign key constraints
In Oracle9i, significant improvements have been made to query rewrite which include:
A materialized view can be used by query rewrite when it only contains some of the data because its definition contains clauses such as BETWEEN 100 AND 250, region_cd >=10 AND <=56, IN ('UK','USA',Canada'), store_id = 100 OR store_id =200. This allows the creation of smaller and more compact materialized views which contain only the data of interest.
An API called DBMS_MVIEW.Explain_Rewrite which given a SQL statement will advise:
what query rewrite will do
which materialized view it will use
when query rewrite is not possible, why query rewrite will not occur
support for SQL queries which contain self-joins and a sub-query within the FROM list in the SQL query
The query rewrite enhancements in Oracle9i now mean that even more queries are eligible for query rewrite, using fewer and smaller materialized views. Oracle9i thus widens the scope of queries whose performance can be improved by defining and maintaining materialized views.

DWH- Types of Dimensions and Facts

Dimensions

1. Degenerate Dimension
In a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn't have its own dimension table. Degenerate dimensions are often used when a fact table's grain represents transactional level data and one wishes to maintain system specific identifiers such as order numbers, invoice numbers and the like without forcing their inclusion in their own dimension. The decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.
Degenerated Dimension is achieved through a gradual modeling approach following Dimensional Modeling standards. Let's take example of a Star Schema representing Sales Invoices. The FACT would have the "Invoiced Amount" as primary measure. Now when we look at the source of the Invoice, it is the body if the Paper Invoice that gives us the following particulars about each Invoice:
Invoice DateCustomer IDProducts within the InvoiceReference to Order Number(s)Invoice NumberInvoice Line Numbers (which are multiple lines in single Invoice)Invoice Line AmountInvoice Total Amount
When we model the above following Dimensional Modeling standards, we get following distinct Dimensions:
Calendar Dimension - representing the Invoice DateCustomer Dimension - representing Customer IDProduct Dimension - representing Products within the InvoiceOrder Dimension - representing OrdersInvoice Dimension – representing Invoice Number & Invoice Line Numbers
Question comes - what attributes would be left to be part of the INVOICE DIMENSION, if at all we decide to have one! Only candidate attributes are Invoice Number and Invoice Line Numbers. But, this is at the granularity of the FACT, which stores references to all above said Dimensions as well as the measures i.e. Invoice Line Amount, Invoice Total Amount (Derived by aggregation).
It is at this situation, we may decide to degenerate the attributes Invoice Number & Invoice Line Number into the Fact and avoid having a distinct entity to represent Invoice Number / Line Numbers as a Dimension. What we achieve by this:
1. avoiding a huge join as both Fact and this Dimension would have the same granularity,2. still able to query with Invoice Number as the entry point
So, when such a scenario appears, we make the left out attributes (i.e. Invoice Number & Invoice Line Number in our case) part of the Fact and part of the Primary Key in the Fact. This is why and how we model Degenerated Dimensions.
2. Junk Dimension
A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. By creating an abstract dimension, these flags and indicators are removed from the fact table while placing them into a useful dimensional framework.
3. Conformed Dimension
They are dimension tables in a star schema data mart that adhere to a common structure and therefore allow queries to be executed across star schemas. For example the Calendar dimension is commonly needed in most data marts. By making this Calendar dimension adhere to a single structure regardless of what data mart it is used in your organization you can query by date/time from one data mart to another to another.
4. Role Playing Dimension
Role Playing dimension refers to a dimension can play different roles in a fact table depending on the context. For example, the Date dimension can be used for the ordered date, scheduled shipping date, shipment date, and invoice date in an order line fact.
In the data warehouse, you will have a single dimension table for the dates. You will have multiple warehouse foreign key from the fact table to the same dimension.
In Oracle BI Server, the role playing dimension can be created as multiple alias in the physical layer of the repository file (RPD). You can create the foreign key relationship from the fact table to the different alias of the dimension table.
5. Shrunken Dimensions
A shrunken dimension is a subset of another dimension. For example, the Orders fact table may include a foreign key for Product, but the Target fact table may include a foreign key only for Product Category, which is in the Product table, but much less granular. Creating a smaller dimension table, with Product Category as its primary key, is one way of dealing with this situation of heterogeneous grain. If the Product dimension is snow flaked, there is probably already a separate table for Product Category, which can serve as the Shrunken Dimension.
6. Static Dimensions
Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually — for example with Status codes — or it can be generated by a procedure, such as a Date or Time dimension.
7. Inferred Dimension
While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes. This should technically be called an inferred member, but is often called an inferred dimension.
Facts

Types of Facts
1. Additive
Additive facts are facts that can be summed up through all of the dimensions in the fact table.
For example assumes that we are a retailer and we have fact table with the following columns.

Date
Store
Product
Sales_Amount
The purpose of this table is to record the sales amount for each product in each store on a daily basis, Sales_Amount is the fact. So if we want to know sales amount for a particular product in particular date , in particular store, we need to sum up all these three columns, so we can say it is additive fact.
2. Semi-Additive
Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
Say we are a bank with the following fact table:
Date
Account
Current_Balance
Profit_Margin
The purpose of this table is to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day. Current_Balance and Profit_Margin are the facts. Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information).
3. Non-Additive
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
If we found the Profit_Margin fact in the above table Profit_Margin is a non-additive fact, for it does not make sense to add them up for the account level or the day level.
Types of Fact Tables
Based on the above classifications, there are two types of fact tables:
1. Cumulative
This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.
2. Snapshot
This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.