Because of some reason, yesterday was re-creating / arranging my resume again. I was amazed when I came across certification section. Between Jan 2007 to Sep 2009 , in 2 years I had given SCJP, Oracle SQL, SCWCD, ITIL, , Oracle Pl-SQL, Oracle Fundamental-1, and lastly Oracle Fundamental - II, and the thing by which I amazed is, in year 2010 and 2011 I have not done any. :-( Reason was simple, I became satisfied, which kept me apart from learning and achieving more.
Today, went at Zensar Circle again. Met Sir (Ashish Jaiswal) and Sikku Bhai (Sikandar Shaikh), sir explained some things related to Partition Table and as always Sikku Bhai told, its 10 months over now, looking for job change..:) With sir's 30min lecture on Partition table and backup, I got motivated and thought about old days. Around 1, 1.5 years back, had tried number of things on Materialized view, and thought will do something on that before I go to bed...
taking the stuff from below link of "data warehousing guide 10g"-- give this words as search at google.
http://docs.oracle.com/cd/B19306_01/server.102/b14223.pdf
The basic difference I saw in data modeling of Oracle and Teradata is, Oracle advice to create Start Or SnowFlex schema structure and Teradata prefers 3NF. We do not have to bother about advances and dis-advances, both gives us job and money, so both are good.
Oracle uses Bit Map indexes which are mostly created on Fact tables. Below stuff is simply copied from page number 96 [6 - 2] of above link.
"The advantages of using bitmap indexes are greatest for columns in which the ratio of
the number of distinct values to the number of rows in the table is small. We refer to
this ratio as the degree of cardinality. A gender column, which has only two distinct
values (male and female), is optimal for a bitmap index. However, data warehouse
administrators also build bitmap indexes on columns with higher cardinalities.
For example, on a table with one million rows, a column with 10,000 distinct values is
a candidate for a bitmap index. A bitmap index on this column can outperform a
B-tree index, particularly when this column is often queried in conjunction with other
indexed columns. In fact, in a typical data warehouse environments, a bitmap index
can be considered for any non-unique column.
B-tree indexes are most effective for high-cardinality data: that is, for data with many
possible values, such as customer_name or phone_number. In a data warehouse,
B-tree indexes should be used only for unique columns or other columns with very
high cardinalities (that is, columns that are almost unique). The majority of indexes in
a data warehouse should be bitmap indexes.
In ad hoc queries and similar situations, bitmap indexes can dramatically improve
query performance. AND and OR conditions in the WHERE clause of a query can be
resolved quickly by performing the corresponding Boolean operations directly on the
bitmaps before converting the resulting bitmap to rowids. If the resulting number of
rows is small, the query can be answered quickly without resorting to a full table scan." I do not know, why in my old project not a single index was bit-map, thought the structure was Snow Flex..
Lets start with Materialized View now....
What is Materialized View and Normal View ?
Materiazlied view is just like normal view, only thing is it eats its own space at DB which normal view do not. Materialized view keeps its data at its own space wherein normal view always fetches the data from its base tables. Because of Query-Re-write, thought you fire select query on base tables, oracle will fetch the data from materialized view[ If its defined on base tables] and many more...we are not preparing for interview here, so its not important to find out anything more. If needed, go through Basic MV chapter from this book.
Creating materialized view using DB Link, i.e. base tables are in another db, and your MV is in different one -- I had tried it earlier and it was worked that time.. While reading this book came across the line
"If a materialized view is to be used by query rewrite, it must be stored in the same database as the detail tables on which it relies".. this means you can create it, but query re-write will not work, Will try this at end as I do not have another db created at home. Seems Query Re-Write will not work if we are using db link at select query of materialized view, need to check.
Note:- Only thing by which you can not define MV is
"You cannot, however, define a materialized view with a subquery in the
SELECT list of the defining query. You can, however, include subqueries elsewhere in
the defining query, such as in the WHERE clause."
e.g. Query should not be like
select id, name, (select address from myTable), age
from employee
This will not work... All tables has to be at where clause or from clause.
As per book there are 3 types of MVs
■ Materialized Views with Aggregates
■ Materialized Views Containing Only Joins
■ Nested Materialized Views
Some thing imp to read before we start creating MV --
If you specify REFRESH FAST, Oracle performs further verification of the query
definition to ensure that fast refresh can be performed if any of the detail tables
change. These additional checks are:
■ A materialized view log must be present for each detail table unless the table
supports PCT. Also, when a materialized view log is required, the ROWID column
must be present in each materialized view log.
■ The rowids of all the detail tables must appear in the SELECT list of the
materialized view query definition.
If some of these restrictions are not met, you can create the materialized view as
REFRESH FORCE to take advantage of fast refresh when it is possible. If one of the
tables did not meet all of the criteria, but the other tables did, the materialized view
would still be fast refreshable with respect to the other tables for which all the criteria
are met.
Materialized Join Views FROM Clause Considerations
If the materialized view contains only joins, the ROWID columns for each table (and
each instance of a table that occurs multiple times in the FROM list) must be present in
the SELECT list of the materialized view.
If the materialized view has remote tables in the FROM clause, all tables in the FROM
clause must be located on that same site. Further, ON COMMIT refresh is not supported
for materialized view with remote tables. Materialized view logs must be present on
the remote site for each detail table of the materialized view and ROWID columns must
be present in the SELECT list of the materialized view, as shown in the following
example.
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
Alternatively, if the previous example did not include the columns times_rid and
customers_rid, and if the refresh method was REFRESH FORCE, then this
materialized view would be fast refreshable only if the sales table was updated but not
if the tables times or customers were updated.
CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE AS
SELECT s.rowid "sales_rid", c.cust_id, c.cust_last_name, s.amount_sold,
s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
You can create a nested materialized view on materialized views, but all parent and
base materialized views must contain joins or aggregates. If the defining queries for a
materialized view do not contain joins or aggregates, it cannot be nested. All the
underlying objects (materialized views or tables) on which the materialized view is
defined must have a materialized view log. All the underlying objects are treated as if
they were tables. In addition, you can use all the existing options for materialized
views.
Using the tables and their columns from the sh sample schema, the following
materialized views illustrate how nested materialized views can be created.
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
/*create materialized view join_sales_cust_time as fast refreshable at
COMMIT time */
CREATE MATERIALIZED VIEW join_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid
FROM sales s, customers c, times t
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;
To create a nested materialized view on the table join_sales_cust_time, you
would have to create a materialized view log on the table. Because this will be a
single-table aggregate materialized view on join_sales_cust_time, you need to
log all the necessary columns and use the INCLUDING NEW VALUES clause.
/* create materialized view log on join_sales_cust_time */
CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time
WITH ROWID (cust_last_name, day_number_in_week, amount_sold)
INCLUDING NEW VALUES;
/* create the single-table aggregate materialized view sum_sales_cust_time
on join_sales_cust_time as fast refreshable at COMMIT time */
CREATE MATERIALIZED VIEW sum_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold)
cnt_sales, cust_last_name, day_number_in_week
FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;
Nesting Materialized Views with Joins and Aggregates
Some types of nested materialized views cannot be fast refreshed. Use EXPLAIN_
MVIEW to identify those types of materialized views. You can refresh a tree of nested
materialized views in the appropriate dependency order by specifying the nested =
TRUE parameter with the DBMS_MVIEW.REFRESH parameter. For example, if you call
DBMS_MVIEW.REFRESH ('SUM_SALES_CUST_TIME', nested => TRUE), the
REFRESH procedure will first refresh the join_sales_cust_time materialized
view, and then refresh the sum_sales_cust_time materialized view.
================ First MV =======================
CREATE MATERIALIZED VIEW cust_sales_mv
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE AS
select sum(salary),count(emp.department_id), department_name
from hr.employees emp, hr.departments dep
where emp.department_id = dep.department_id
group by department_name
I fired above query, on hr schema and got an error
"insufficient privileges"
So, logged in as SYS, fired again and got an error
"table space demo does not exists"
What table space do sys user/schema has ?
Checked it at
select *
from DBA_TABLESPACES
or
CREATE MATERIALIZED VIEW cust_sales_mv
PCTFREE 0 TABLESPACE users--demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE AS
select sum(salary),count(emp.department_id), department_name
from hr.employees emp, hr.departments dep
where emp.department_id = dep.department_id
group by department_name
MV got created in 10 sec.
Fired select statement of MV and checked the Explain plan
It seems, Query re-wite worked and now data is getting fetched from MV and not from Base Tables.
Today, went at Zensar Circle again. Met Sir (Ashish Jaiswal) and Sikku Bhai (Sikandar Shaikh), sir explained some things related to Partition Table and as always Sikku Bhai told, its 10 months over now, looking for job change..:) With sir's 30min lecture on Partition table and backup, I got motivated and thought about old days. Around 1, 1.5 years back, had tried number of things on Materialized view, and thought will do something on that before I go to bed...
taking the stuff from below link of "data warehousing guide 10g"-- give this words as search at google.
http://docs.oracle.com/cd/B19306_01/server.102/b14223.pdf
The basic difference I saw in data modeling of Oracle and Teradata is, Oracle advice to create Start Or SnowFlex schema structure and Teradata prefers 3NF. We do not have to bother about advances and dis-advances, both gives us job and money, so both are good.
Oracle uses Bit Map indexes which are mostly created on Fact tables. Below stuff is simply copied from page number 96 [6 - 2] of above link.
"The advantages of using bitmap indexes are greatest for columns in which the ratio of
the number of distinct values to the number of rows in the table is small. We refer to
this ratio as the degree of cardinality. A gender column, which has only two distinct
values (male and female), is optimal for a bitmap index. However, data warehouse
administrators also build bitmap indexes on columns with higher cardinalities.
For example, on a table with one million rows, a column with 10,000 distinct values is
a candidate for a bitmap index. A bitmap index on this column can outperform a
B-tree index, particularly when this column is often queried in conjunction with other
indexed columns. In fact, in a typical data warehouse environments, a bitmap index
can be considered for any non-unique column.
B-tree indexes are most effective for high-cardinality data: that is, for data with many
possible values, such as customer_name or phone_number. In a data warehouse,
B-tree indexes should be used only for unique columns or other columns with very
high cardinalities (that is, columns that are almost unique). The majority of indexes in
a data warehouse should be bitmap indexes.
In ad hoc queries and similar situations, bitmap indexes can dramatically improve
query performance. AND and OR conditions in the WHERE clause of a query can be
resolved quickly by performing the corresponding Boolean operations directly on the
bitmaps before converting the resulting bitmap to rowids. If the resulting number of
rows is small, the query can be answered quickly without resorting to a full table scan." I do not know, why in my old project not a single index was bit-map, thought the structure was Snow Flex..
Lets start with Materialized View now....
What is Materialized View and Normal View ?
Materiazlied view is just like normal view, only thing is it eats its own space at DB which normal view do not. Materialized view keeps its data at its own space wherein normal view always fetches the data from its base tables. Because of Query-Re-write, thought you fire select query on base tables, oracle will fetch the data from materialized view[ If its defined on base tables] and many more...we are not preparing for interview here, so its not important to find out anything more. If needed, go through Basic MV chapter from this book.
Creating materialized view using DB Link, i.e. base tables are in another db, and your MV is in different one -- I had tried it earlier and it was worked that time.. While reading this book came across the line
"If a materialized view is to be used by query rewrite, it must be stored in the same database as the detail tables on which it relies".. this means you can create it, but query re-write will not work, Will try this at end as I do not have another db created at home. Seems Query Re-Write will not work if we are using db link at select query of materialized view, need to check.
Note:- Only thing by which you can not define MV is
"You cannot, however, define a materialized view with a subquery in the
SELECT list of the defining query. You can, however, include subqueries elsewhere in
the defining query, such as in the WHERE clause."
e.g. Query should not be like
select id, name, (select address from myTable), age
from employee
This will not work... All tables has to be at where clause or from clause.
As per book there are 3 types of MVs
■ Materialized Views with Aggregates
■ Materialized Views Containing Only Joins
■ Nested Materialized Views
Some thing imp to read before we start creating MV --
If you specify REFRESH FAST, Oracle performs further verification of the query
definition to ensure that fast refresh can be performed if any of the detail tables
change. These additional checks are:
■ A materialized view log must be present for each detail table unless the table
supports PCT. Also, when a materialized view log is required, the ROWID column
must be present in each materialized view log.
■ The rowids of all the detail tables must appear in the SELECT list of the
materialized view query definition.
If some of these restrictions are not met, you can create the materialized view as
REFRESH FORCE to take advantage of fast refresh when it is possible. If one of the
tables did not meet all of the criteria, but the other tables did, the materialized view
would still be fast refreshable with respect to the other tables for which all the criteria
are met.
Materialized Join Views FROM Clause Considerations
If the materialized view contains only joins, the ROWID columns for each table (and
each instance of a table that occurs multiple times in the FROM list) must be present in
the SELECT list of the materialized view.
If the materialized view has remote tables in the FROM clause, all tables in the FROM
clause must be located on that same site. Further, ON COMMIT refresh is not supported
for materialized view with remote tables. Materialized view logs must be present on
the remote site for each detail table of the materialized view and ROWID columns must
be present in the SELECT list of the materialized view, as shown in the following
example.
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
Alternatively, if the previous example did not include the columns times_rid and
customers_rid, and if the refresh method was REFRESH FORCE, then this
materialized view would be fast refreshable only if the sales table was updated but not
if the tables times or customers were updated.
CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE AS
SELECT s.rowid "sales_rid", c.cust_id, c.cust_last_name, s.amount_sold,
s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
You can create a nested materialized view on materialized views, but all parent and
base materialized views must contain joins or aggregates. If the defining queries for a
materialized view do not contain joins or aggregates, it cannot be nested. All the
underlying objects (materialized views or tables) on which the materialized view is
defined must have a materialized view log. All the underlying objects are treated as if
they were tables. In addition, you can use all the existing options for materialized
views.
Using the tables and their columns from the sh sample schema, the following
materialized views illustrate how nested materialized views can be created.
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
/*create materialized view join_sales_cust_time as fast refreshable at
COMMIT time */
CREATE MATERIALIZED VIEW join_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid
FROM sales s, customers c, times t
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;
To create a nested materialized view on the table join_sales_cust_time, you
would have to create a materialized view log on the table. Because this will be a
single-table aggregate materialized view on join_sales_cust_time, you need to
log all the necessary columns and use the INCLUDING NEW VALUES clause.
/* create materialized view log on join_sales_cust_time */
CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time
WITH ROWID (cust_last_name, day_number_in_week, amount_sold)
INCLUDING NEW VALUES;
/* create the single-table aggregate materialized view sum_sales_cust_time
on join_sales_cust_time as fast refreshable at COMMIT time */
CREATE MATERIALIZED VIEW sum_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold)
cnt_sales, cust_last_name, day_number_in_week
FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;
Nesting Materialized Views with Joins and Aggregates
Some types of nested materialized views cannot be fast refreshed. Use EXPLAIN_
MVIEW to identify those types of materialized views. You can refresh a tree of nested
materialized views in the appropriate dependency order by specifying the nested =
TRUE parameter with the DBMS_MVIEW.REFRESH parameter. For example, if you call
DBMS_MVIEW.REFRESH ('SUM_SALES_CUST_TIME', nested => TRUE), the
REFRESH procedure will first refresh the join_sales_cust_time materialized
view, and then refresh the sum_sales_cust_time materialized view.
================ First MV =======================
CREATE MATERIALIZED VIEW cust_sales_mv
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE AS
select sum(salary),count(emp.department_id), department_name
from hr.employees emp, hr.departments dep
where emp.department_id = dep.department_id
group by department_name
I fired above query, on hr schema and got an error
"insufficient privileges"
So, logged in as SYS, fired again and got an error
"table space demo does not exists"
What table space do sys user/schema has ?
Checked it at
select *
from DBA_TABLESPACES
or
-
USER_TABLESPACES
-
V$TABLESPACE
CREATE MATERIALIZED VIEW cust_sales_mv
PCTFREE 0 TABLESPACE users--demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE AS
select sum(salary),count(emp.department_id), department_name
from hr.employees emp, hr.departments dep
where emp.department_id = dep.department_id
group by department_name
MV got created in 10 sec.
Fired select statement of MV and checked the Explain plan
