MERGE
CREATE EMP1 TABLE WITH FOLLOWING ROWS AND COLUMNS.
CREATE TABLE EMP1
(
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(10,5),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7566,'JONES','sr_MANGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,60);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7788,'SCOTT','accountt',7566,to_date('09-DEC-82','DD-MON-RR'),3000,null,60);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7902,'FORD','accountt',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,60);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7369,'SMITH','SR_CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,60);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7782,'CLARK','sr_MANGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,50);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7934,'MILLER','SR_CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,50);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7698,'BLAKE','sr_MANGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,70);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7521,'WARD','accountt',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,70);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7654,'MARTIN','accountt',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,70);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7844,'TURNER','accountt',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,70);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7900,'JAMES','SR_CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,70);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7876,'ADAMS','SR_CLERK',7788,to_date('12-JAN-83','DD-MON-RR'),1100,null,60);
Insert into EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7499,'ALLEN','accountt',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,70);
INSERT INTO EMP1 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-NOV-81','DD-MON-RR'),0.5,NULL,10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------------------------------------------------------------------------
7566 JONES sr_MANGER 7839 02-APR-81 2975 60
7788 SCOTT accountt 7566 09-DEC-82 3000 60
7902 FORD accountt 7566 03-DEC-81 3000 60
7369 SMITH SR_CLERK 7902 17-DEC-80 800 60
7782 CLARK sr_MANGER 7839 09-JUN-81 2450 50
7934 MILLER SR_CLERK 7782 23-JAN-82 1300 50
7698 BLAKE sr_MANGER 7839 01-MAY-81 2850 70
7521 WARD accountt 7698 22-FEB-81 1250 500 70
7654 MARTIN accountt 7698 28-SEP-81 1250 1400 70
7844 TURNER accountt 7698 08-SEP-81 1500 0 70
7900 JAMES SR_CLERK 7698 03-DEC-81 950 70
7876 ADAMS SR_CLERK 7788 12-JAN-83 1100 60
7499 ALLEN accountt 7698 20-FEB-81 1600 300 70
7839 KING PRESIDENT 17-NOV-81 0.5 10
--firstly create the table mybonus with given syntax
CREATE TABLE MYBONUS
(
EMPNO NUMBER,
BONUS NUMBER DEFAULT 100
);
--insert the data into the table
insert into mybonus(empno,sal) select e.empno,e.sal from emp1 e where job='SR_CLERK';
After insert our MYBONUS table becomes like.....
empno bonus sal
---------------------
7369 100 800
7934 100 1300
7900 100 950
7876 100 1100
NOTE:= Bonus has value 100 because it is define default at the time of table creation.
Now when we are fire the below merge query then the result like that
EXAMPLE 1
-------------
MERGE INTO MYBONUS B
USING (SELECT EMPNO,SAL,DEPTNO FROM EMP1 WHERE DEPTNO=70) S
ON (B.EMPNO=S.EMPNO)
WHEN MATCHED THEN
UPDATE SET B.BONUS=B.BONUS+200
WHEN NOT MATCHED THEN
INSERT (B.EMPNO,B.BONUS,B.SAL) VALUES (S.EMPNO,S.SAL*.10,S.SAL) WHERE (S.SAL<=4000);
then the result like that
OUTPUT:= 6 rows merged.
-------
and result become like that in MYBONUS Table....
EMPNO BONUS SAL
--------------------
7369 100 800
7934 100 1300
7900 300 950
7876 100 1100
7521 125 1250
7844 150 1500
7698 285 2850
7654 125 1250
7499 160 1600
EXPLANATION:=
when merge will fire then using clause will fire that contain the query....
SQL>SELECT EMPNO,SAL,DEPTNO FROM EMP1 WHERE DEPTNO=70;
and result like.......
EMPNO SAL DEPTNO
--------------------
7521 1250 70
7698 2850 70
7654 1250 70
7844 1500 70
7900 950 70
7499 1600 70
then it will check the on(B.EMPNO=S.EMPNO) condition.
if the ON condtion will meet, then it will update the existing data in MYBONUS table. Other wise insert.
In given example EMPNO (7369,7934,7876) are not matched any condition of output of second table (means S table which is
output of >>SELECT EMPNO,SAL,DEPTNO FROM EMP1 WHERE DEPTNO=70;).
Only EMPNO 7900 is satisfying the condition, that means only 7900 empno is updated in MYBONUS table.
Apart of this all the data of second table S (result of query >>SELECT EMPNO,SAL,DEPTNO FROM EMP1 WHERE DEPTNO=70;)
will insert into the MYBONUS table.
and result become such as....
EMPNO BONUS SAL
--------------------
7369 100 800
7934 100 1300
7900 300 950
7876 100 1100
7521 125 1250
7844 150 1500
7698 285 2850
7654 125 1250
7499 160 1600
EXAMPLE 2
-----------
MERGE INTO MYBONUS B
USING (SELECT EMPNO,SAL,DEPTNO FROM EMP1 WHERE DEPTNO=70) S
ON (B.EMPNO=S.EMPNO)
WHEN MATCHED THEN
UPDATE SET B.BONUS=B.BONUS+200
DELETE WHERE b.SAL in (800,950)
WHEN NOT MATCHED THEN
INSERT (B.EMPNO,B.BONUS,B.SAL) VALUES (S.EMPNO,S.SAL*.10,S.SAL) WHERE (S.SAL<=4000);
The difference Between first merge and second merge is only that, the DELETE statement is added in second one.
and the result become like that...
EMPNO BONUS SAL
--------------------
7369 100 800
7934 100 1300
7876 100 1100
7521 125 1250
7844 150 1500
7698 285 2850
7654 125 1250
7499 160 1600
EXPLANATION:=
--------------
The Delete statement is delete the record which satisfy the ON condition as well as DELETE condition.
For example, merge query delete the EMPNO 7900 (has sal 950) because if we notice in first merge query
only EMPNO 7900 is satisfied the on condition as well as DELETE condition here.EMPNO 7369 which has sal 800
is not deleted from the table bescause empno 7369 is not satisfied the on condition of the merge query.
NOTE:= If your EMP1 table contain the duplicate row in table then it will not merge and give the error
-------
ERROR: IT WILL COME WHEN WE ARE FIRE THE QUERY AND WHEN WE ARE GETTING THE DUPLICATE RECORD IN SOURCE TABLE for above query.
here if emp1 table contain the duplicate records.
ORA-30926: unable to get a stable set of rows in the source tables
30926. 00000 - "unable to get a stable set of rows in the source tables"
*Cause: A stable set of rows could not be got because of large dml
ACTIVITY OR A NON-DETERMINISTIC WHERE CLAUSE.
*ACTION: REMOVE ANY NON-DETERMINISTIC WHERE CLAUSES AND REISSUE THE DML.
SOLUTION OF ERROR:
-------------------
Only we have to write the distinct keywords in using clause after the.
and our merge query become like that.....
MERGE INTO MYBONUS B
USING (SELECT distinct EMPNO,SAL,DEPTNO FROM EMP1 WHERE DEPTNO=70) S
ON (B.EMPNO=S.EMPNO)
WHEN MATCHED THEN
UPDATE SET B.BONUS=B.BONUS+200
DELETE WHERE b.SAL>2000
WHEN NOT MATCHED THEN
INSERT (B.EMPNO,B.BONUS,B.SAL) VALUES (S.EMPNO,S.SAL*.10,S.SAL) WHERE (S.SAL<=4000);
--create table by following syntax
CREATE TABLE EXAMTIMETABLE(EXAMNAME VARCHAR2(40),EXAMTIME VARCHAR2(12),CONSTRAINT EXAMNAMEPK PRIMARY KEY(EXAMNAME));
insert into EXAMTIMETABLE values('PHYSICAL SCIENCE','9:00 AM');
--merge the table one table TO itself.
MERGE INTO EXAMTIMETABLE E1
USING EXAMTIMETABLE E2
ON (E2.EXAMNAME=E1.EXAMNAME AND E1.EXAMNAME='PHYSICAL SCIENCE')
WHEN MATCHED THEN
UPDATE SET E1.EXAMTIME='10:30 AM'
WHEN NOT MATCHED THEN
insert (e1.EXAMNAME,e1.EXAMTIME) values ('PHYSICAL SCIENCE','10:00 AM');
Inline view:=
--------------
An inline view is term given to sub query in FROM clause of query which can be used as table.
Inline view effectively is a named sub query.
EXAMPLE:=
---------
SELECT DNAME,ENAME,SAL FROM EMP,
(SELECT DNAME, DEPTNO FROM DEPT) D
WHERE A.DEPTNO = B.DEPTNO;
In the above query (SELECT DNAME, DEPTNO FROM DEPT) D is the inline view.
Inline views are determined at runtime, and in contrast to normal view they are not stored in the data dictionary.
Advantage of using inline views:
--------------------------------
1. Better query performance
2. Better visibility of code
EXAMPLE 2:
-----------
select emp.empno,
EMP.ENAME,
DEPT.DEPTNO
from
(SELECT DEPTNO FROM DEPT) DEPT,
(SELECT EMPNO, ENAME,DEPTNO FROM EMP) EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO;
In the above example,first the compiler forms 2 inline views emp1 and dept and then
joins these 2 in the where clause.
Hashing Algorithm:
-------------------
Difference Between Hash Join & Merge Join
-------------------------------------------
Merge Join :
-------------
Oracle performs a join between two sets of row data using the merge
join algorithm. The inputs are two separate sets of row data. Output is
the results of the join. Oracle reads rows from both inputs in an
alternating fashion and merges together matching rows in order to
generate output. The two inputs are sorted on join column.
Hash Join :
-------------
Oracle performs a join between two sets of row data using hash join
algorithm. Input and Output same as Merge Join. Oracle reads all rows
from the second input and builds a hash structure (like has table in
java), before reading each row from the first input one at a time. For
each row from the first input, the hash structure is probed and matching
rows generate output.
Hashing and Hash table
Before seeing java’s Hashtable in detail you should understand hashing in general.
Assume that, v is a value to be stored and k is the key used for storage / retrieval,
then h is a hash function where v is stored at h(k) of table. To retrieve a value compute
h(k) so that you can directly get the position of v. So in a key-value pair table, you need
not sequentially scan through the keys to identify a value.
h(k) is the hashing function and it is used to find the location to store the corresponding value v.
h(k) cannot compute to a indefinite space. Storage allocated for a Hashtable is limited within a
program. So, the hasing function h(k) should return a
number within that allocated spectrum (logical address space).
1. Definition of a Hash Table
Before we get into the definition of Hash Tables, it is good to introduce WHY to use Hash tables.
Hash tables are good for doing a quick search on things.
For instance if we have an array full of data (say 100 items). If we knew the position that a specific item is stored in an array, then we could quickly access it. For instance, we just happen to know that the item we want it is at position 3; I can apply:
myitem=myarray[3];
With this, we don't have to search through each element in the array, we just access position 3.
The question is, how do we know that position 3 stores the data that we are interested in?
This is where hashing comes in handy. Given some key, we can apply a hash function to it to find an index or position that we want to access.
1.1 What is the hash function?
There are many different hash functions. Some hash functions will take an integer key and turn it into an index. A common one is the division method.
Let's learn through an example:
1.2 Division method (one hash method for integers)
Let's say you had the following numbers or keys that you wanted to map into an array of 10 elements:
123456
123467
123450
To apply the division method, you could divide the number by 10 (or the maximum number of elements in the array) and use the remainder (the modulo) as an index. The following would result:
123456 % 10 = 6 (the remainder is 6 when dividing by 10)
123467 % 10 = 7 (the remainder is 7)
123450 % 10 = 0 (the remainder is 0)
These numbers would be inserted into the array at positions 6, 7, and 0 respectively. It might look something like this:
The important thing with the division method is that the keys are integers.
1.3 What happens when the keys aren't integers?
You have to apply another hash function to turn them into integers. Effectively, you get two hash functions in one:
function to get an integer
function to apply a hash method from above to get an index to an array
What do we mean that the keys aren't integers? Well, let's say that the keys are people's names. Such as:
Sarah Jones
Tony Balognie
Tom Katz
The goal is to type in one of these names and get an index to an array in order to access that information. How do we do this?
The hash function has to do two things:
Convert the names into integers
For instance, we have a function which turns a string into an integer. The results will be as follows:
Sarah Jones --> 1038
Tony Balognie --> 1259
Tom Katz --> 746
Apply a hash method to get an index
We can now apply the division method to get an index for an array of 10 elements
Sarah Jones --> 1038 % 10 --> 8
Tony Balognie --> 1259 % 10 --> 9
Tom Katz --> 746 % 10 --> 6
1.4 What would that look like in the array?
The array is known as a hash table. It stores the key (used to find the index) along with associated values. In the above example, we might have a hash table that looked something like this:
Again, the idea is that we will insert items into the hash table using the key and applying the hash function(s) to get the index.
A problem occurs when two keys yield the same index. For Instance, say we wanted to include:
John Smith --> 948 % 10 --> 8
We have a collision because Sarah Jones is already stored at array index 8.
We need a method to resolve this. The resolution comes in how you create your hash table. There two major approaches given in the book:
Linear Probing
Chaining
The approach used in this lab is referred to as chaining.
The details are left as class material, but recognize that in chaining you have an array of linked lists. All the data in the "same link", have colliding index values.
Consider a diagram of the above example. Remember, there was a collision with Sarah Jones and John Smith. Notice that John Smith is "chained" or "linked" after Sarah Jones.
