--------------------------------------------------------------------
--Performance Measurement Version
--------------------------------------------------------------------
--Description : New Non Expanded Complex Query
--              Replaces the previous query, by a more efficient
--              Coalescing Operation
--Author      : Jose Alvin Gendrano
--Date        : Mar 7, 1997
--Update      : August 6, 1997 
--------------------------------------------------------------------


SET SERVEROUTPUT ON;
DROP TABLE emp10RES;
CREATE TABLE emp10RES (EID       INTEGER,
                       StartDate DATE,
                       EndDate   DATE);


DECLARE 
  last_timing NUMBER := NULL; 

BEGIN
  -- Enable printing Put_Line Messages 
  DBMS_OUTPUT.ENABLE(10000);
  DBMS_OUTPUT.PUT_LINE('Start');

  last_timing := DBMS_UTILITY.GET_TIME;

  INSERT INTO emp10RES
  SELECT E1.EID, E1.StartDate, E1.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate <= E1.StartDate AND
         E1.EndDate <= E2.EndDate
  UNION 
  SELECT E1.EID, E1.StartDate, E2.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E1.StartDate > E2.StartDate AND
         E2.EndDate < E1.EndDate AND
         E1.StartDate < E2.EndDate
  UNION
  SELECT E1.EID, E2.StartDate, E1.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate > E1.StartDate AND
         E1.EndDate < E2.EndDate AND
         E2.StartDate < E1.EndDate
  UNION 
  SELECT E1.EID, E2.StartDate, E2.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate >= E1.StartDate AND
         E2.EndDate <= E1.EndDate AND
         NOT (E2.Startdate = E1.StartDate AND
              E2.EndDate = E1.EndDate);
END;
/

-- ---------------------------------------------------------------------------------------------------------- 

DROP TABLE emp10COA;
CREATE TABLE emp10COA (EID       INTEGER,
                        StartDate DATE,
                        EndDate   DATE);

DECLARE 
  resultCount NUMBER := NULL;
  CURSOR emp10res_cur is
    SELECT EID, StartDate, EndDate
    FROM emp10res;
  StartRec      emp10res_cur%ROWTYPE;  -- The start of a Group 
  PrevRec       emp10res_cur%ROWTYPE;  -- Previous Record 
  CurrRec       emp10res_cur%ROWTYPE;  -- Current Record 
  last_timing   NUMBER := NULL; 
BEGIN

  -- ASSUMES THAT THE TUPLES ARE SORTED IN THE TABLE,
  -- Which is accomplished by the UNION operator
  -- instead of UNION ALL

  -- Enable printing Put_Line Messages 
  DBMS_OUTPUT.PUT_LINE('Start');

  SELECT COUNT(*) INTO resultCount FROM emp10res;
  DBMS_OUTPUT.PUT_LINE('# of Records to Operate on : ' || resultCount);


  last_timing := DBMS_UTILITY.GET_TIME;

  IF NOT emp10res_cur%ISOPEN
  THEN
    OPEN emp10res_cur;
  END IF;

  FETCH emp10res_cur INTO PrevRec;
  StartRec := PrevRec;   

  FETCH emp10res_cur INTO CurrRec;

  WHILE emp10Res_cur%FOUND
  LOOP
    IF (StartRec.EID <> CurrRec.EID) OR 
       (PrevRec.EndDate + 1 < CurrRec.StartDate)
    THEN
      INSERT INTO emp10COA 
             VALUES (StartRec.EID, StartRec.StartDate, PrevRec.EndDate);
      StartRec := CurrRec;
	  PrevRec  := StartRec;
    END IF;
	
	IF (CurrRec.EndDate > PrevRec.EndDate) THEN
       PrevRec := CurrRec;
	END IF;

    FETCH emp10res_cur INTO CurrRec;
  END LOOP;

  INSERT INTO emp10COA 
         VALUES (StartRec.EID, StartRec.StartDate, PrevRec.EndDate);

  CLOSE emp10res_cur;    
  DBMS_OUTPUT.PUT_LINE('[emp10 COALESCE CURSORS] Elapsed Time  : ' || TO_CHAR(DBMS_UTILITY.GET_TIME - last_timing) ||
                       ' hundredths of a second.');

  SELECT COUNT(*) INTO resultCount FROM emp10COA;
  DBMS_OUTPUT.PUT_LINE('Resulting Records : ' || resultCount);

END;
/

quit
