--------------------------------------------------------------------
--Description : UnionAll for TJOIN performance measurements
--Author      : Jose Alvin Gendrano
--Date        : Mar 3, 1997
--            : Assumes that the Generated tables have been 
--              properly loaded
--------------------------------------------------------------------

SET SERVEROUTPUT ON;

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



DECLARE 
  resultCount NUMBER := NULL;
  last_timing NUMBER := NULL; 
BEGIN
  /* Enable printing Put_Line Messages */
  DBMS_OUTPUT.ENABLE(10000);
  DBMS_OUTPUT.PUT_LINE('Start');


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

  last_timing := DBMS_UTILITY.GET_TIME;

  INSERT INTO Emp4000RES
  SELECT E1.EID, E1.StartDate, E1.EndDate
  FROM   Emp4000 E1, Emp4000 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate <= E1.StartDate AND
         E1.EndDate <= E2.EndDate
  UNION ALL
  SELECT E1.EID, E1.StartDate, E2.EndDate
  FROM   Emp4000 E1, Emp4000 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 ALL
  SELECT E1.EID, E2.StartDate, E1.EndDate
  FROM   Emp4000 E1, Emp4000 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 ALL
  SELECT E1.EID, E2.StartDate, E2.EndDate
  FROM   Emp4000 E1, Emp4000 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);

  DBMS_OUTPUT.PUT_LINE('[Emp4000 TJOIN UNIONALL] Elapsed Time  : ' || TO_CHAR(DBMS_UTILITY.GET_TIME - last_timing) ||
                       ' hundredths of a second.');

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

END;
/

quit
