MERGE,NESTED LOOPS,MERGE SORT

Merge Statement :-

ØThe MERGE statement reduces table scans and can perform the operation in parallel if required.

ØIt helps us to conditionally insert or update data depending on the condition.

ØIt is also known as an "upsert". 

Syntax :

MERGE INTO target_table 

USING source_table 

ON search_condition

     WHEN MATCHED THEN

        UPDATE SET col1 = value1, col2 = value2,...

        WHERE <update_condition>

        [DELETE WHERE <delete_condition>]

    WHEN NOT MATCHED THEN

        INSERT (col1,col2,...)

        values(value1,value2,...)

        WHERE <insert_condition>; 

Syntax in Detail :

ØSpecify the target table (target_table) which you want to update or insert into in INTO clause.

ØSpecify the source of data (source_table) to be updated or inserted in the USING clause. (It might me a table or a select query)

ØSpecify the search condition upon which the merge operation either updates or inserts in the ON clause.(Might be your join conditions)

Øor each row in the target table, Oracle evaluates the search condition.

ØIf the result is true, then it updates the row with the corresponding data from the source table or Query.

ØIn case the result is false for any rows, then it inserts the corresponding row from the source table into the target table.

Advantage :

ØThe MERGE statement becomes convenient when you want to combine multiple INSERT, UPDATE, and DELETE statements in a single operation.

ØTo execute the MERGE statement, you must have the INSERT and UPDATE object privileges on the source tables.

Example :

MERGE INTO employees e

    USING hr_records h    --(It can be a select Query also)

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

Nested Loops:-

ØThe "Nested" term means that something is performed iteratively.

ØIt is like For each A do B.

ØFor a Nested Sub-Query, for each row in the outer query, execute the sub-query.

ØIt is fine when we perform few loops, but is generally not scalable to large volumes.

ØThe much faster way to handle large data volumes is to use  a Sort-Merge join.

When optimizer uses nested loops?

ØOptimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving condition.

ØIt is important to have an index on column of inner join table as this table is called every time for a new value from outer table.

Optimizer may not use nested loop in case:

1. No of rows of both the table is quite high.

2. Inner query always results in same set of records.

3. The access path of inner table is independent of data coming from outer table.

Merge Sort :

ØIn a SORT-MERGE join, Oracle sorts the first row source by its join columns.

ØAnd sorts the second row source by its join columns and then merges the sorted row sources together.

ØAs matches are found, they are put into the result set.

ØSORT-MERGE joins can be effective when lack of data selectivity or useful indexes render a NESTED LOOPS join or when both of the row sources are quite large.

ØHowever, SORT-MERGE joins can be used only for equijoins (WHERE D.deptno = E.deptno, as opposed to WHERE D.deptno >= E.deptno).

ØSORT-MERGE joins require temporary segments for sorting.

ØThis can lead to extra memory utilization and/or extra disk I/O in the temporary tablespace.

          

         select /*+ ordered */ ename, dept.deptno from emp, dept where 

        dept.deptno=emp.deptno;

  

ØBelow table illustrates the method of executing the query shown next when a SORT-MERGE join is performed.