--------------------------------------------------------------------
--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 emp10RES;
CREATE TABLE emp10RES (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 emp10;
  DBMS_OUTPUT.PUT_LINE('# of Records to Operate on : ' || resultCount);

  last_timing := DBMS_UTILITY.GET_TIME;

  INSERT INTO emp10RES
  SELECT E1.EID,
         GREATEST (E1.StartDate, E2.StartDate),
         LEAST    (E1.EndDate, E2.EndDate)
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND 
         GREATEST (E1.StartDate, E2.StartDate) <
         LEAST (E1.EndDate, E2.EndDate);


  DBMS_OUTPUT.PUT_LINE('[emp10 TJOIN GREATEST/LEAST] Elapsed Time  : ' || TO_CHAR(DBMS_UTILITY.GET_TIME - last_timing) ||
                       ' hundredths of a second.');

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

quit




