JOINS , VIEWS and MATERIALIZED VIEW

SUPPLIER TABLE-

SQL> create table suppliers (supplier_id int,supplier_name varchar2(20));

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:
   

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

An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

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.