SUPPLIER TABLE-
SQL> create table suppliers (supplier_id int,supplier_name varchar2(20));
Table created.
Table created.
SQL> alter table suppliers add constraint pk_constraint_supplier primary key (supplier_id);
Table altered.
SQL> insert into suppliers values(&supplier_id,'&supplier_name');
SQL> SELECT * FROM SUPPLIERS;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
ORDER TABLE-
SQL> create table orders (order_id int, supplier_id int, order_date date);
Table created.
SQL> alter table orders add constraint fk_constraint_order foreign key (supplier_id) references suppliers(supplier_id);
Table altered.
SQL> insert into orders values(&order_id,&supplier_id,TO_DATE('&date','yyyy/mm/dd'));
SQL> alter table orders disable constraint FK_CONSTRAINT_ORDER;
Table altered.
SQL> SELECT * FROM ORDERS;
ORDER_ID SUPPLIER_ID ORDER_DAT
---------- ----------- ---------
500125 10000 12-MAY-03
500126 10001 13-MAY-03
200127 10004 14-MAY-03
JOINS
RIGHT OUTER JOIN
This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
SQL> select s.supplier_id,s.supplier_name,o.order_id
2 from suppliers s
3 right outer join orders o
4 on s.supplier_id=o.supplier_id;
SUPPLIER_ID SUPPLIER_NAME ORDER_ID
----------- -------------------- ----------
10000 IBM 500125
10001 Hewlett Packard 500126
null null 200127
LEFT OUTER JOIN
This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
SQL> select s.supplier_id,s.supplier_name,o.order_id
2 from suppliers s
3 left outer join orders o
4 on s.supplier_id=o.supplier_id;
SUPPLIER_ID SUPPLIER_NAME ORDER_ID
----------- -------------------- ----------
10000 IBM 500125
10001 Hewlett Packard 500126
10002 Microsoft null
10003 NVIDIA null
10005 CYBAGE null
10007 HDFC null
6 rows selected.
INNER JOIN
It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.
SQL> select s.supplier_id,s.supplier_name,o.order_id
2 from suppliers s
3 inner join orders o
4 on s.supplier_id=o.supplier_id;
SUPPLIER_ID SUPPLIER_NAME ORDER_ID
----------- -------------------- ----------
10000 IBM 500125
10001 Hewlett Packard 500126
FULL OUTER JOIN
This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
SQL> select s.supplier_id,s.supplier_name,o.order_id
2 from suppliers s
3 full outer join orders o
4 on s.supplier_id=o.supplier_id;
SUPPLIER_ID SUPPLIER_NAME ORDER_ID
----------- -------------------- ----------
10000 IBM 500125
10001 Hewlett Packard 500126
10002 Microsoft null
10003 NVIDIA null
10005 CYBAGE null
10007 HDFC null
null null 200127
7 rows selected.
MATERIALIZED VIEW
IF WE NEED TO UPDATE/INSERT THE DATA OF MATERIALIZED VIEW THEN USE "FOR UPDATE" KEYWORDS.
Oracle materialized views are one of the single most important SQL tuning tools and they are a true silver bullet, allowing you to pre-join complex views and pre-compute summaries for super-fast response time.
Oracle materialized views perform miracles in our goal to reduce repetitive I/O.
Without Oracle materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over:
Oracle materialized views are one of the single most important SQL tuning tools and they are a true silver bullet, allowing you to pre-join complex views and pre-compute summaries for super-fast response time.
Oracle materialized views perform miracles in our goal to reduce repetitive I/O.
Without Oracle materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over:
SQL> create materialized view mv_sup for update as select * from suppliers;
Materialized view created.
SQL> select * from mv_sup;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
SQL> insert into mv_sup values(10006,'ATOS');
1 row created.
BELOW OUTPUT SHOWS ONLY DATA IN MATERIALIZED VIEW GOT UPDATED BUT NOT ACTUAL TABLE.
SQL> select * from mv_sup;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10006 ATOS
6 rows selected.
SQL> select * from suppliers;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
NOW WE INSERT DATA INTO ACTUAL TABLE BUT IT WILL NOT REFLECT DIRECTLY TO MATERIALIZED VIEW UNTIL & UNLESS WE WILL REFRESH THE MATERIALIZED VIEW MANUALLY.
BELOW IS ILLUSTRATION-
SQL> insert into suppliers values(10007,'HDFC');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mv_sup;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10006 ATOS
6 rows selected.
SQL> select * from suppliers;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10007 HDFC
SQL> exec dbms_mview.refresh('mv_sup');
PL/SQL procedure successfully completed.
SQL> select * from mv_sup;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10007 HDFC
6 rows selected.
VIEW
SQL> CREATE OR REPLACE VIEW SUP1 AS SELECT * FROM SUPPLIERS;
View created.
SQL> SELECT * FROM SUP1;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10007 HDFC
6 rows selected.
NOW INSERT DATA INTO VIEW WILL GET AUTOMATICALLY UPDATED TO ACTUAL TABLE.
SQL> INSERT INTO SUP1 VALUES(10008,'ICICI');
1 row created.
SQL> SELECT * FROM SUP1;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10007 HDFC
10008 ICICI
7 rows selected.
SQL> SELECT * FROM SUPPLIERS;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10007 HDFC
10008 ICICI
7 rows selected.
SQL> SELECT * FROM MV_SUP;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10007 HDFC
6 rows selected.
SQL> exec dbms_mview.refresh('mv_sup');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM MV_SUP;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA
10005 CYBAGE
10007 HDFC
10008 ICICI
7 rows selected.
No comments:
Post a Comment