----------------------------------------------------------------------
--Performance Measurement Version
----------------------------------------------------------------------
--Description : Second COMPLEX SQL Query for Non Expanded Tables
--              Uses the PURE SQL version of the COALESCING operation
--
--Note        : Was not tried with all tables (because of performance
--              issues). And timing could not be derived without using
--              PL/SQL. This approach was abandoned in favor of
--              a cursor-based approach.
--
--Author      : Jose Alvin Gendrano
--Date        : Mar 7, 1997
--UpDate      : Aug 6, 1997
-----------------------------------------------------------------------

SET SERVEROUTPUT ON;

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

DROP INDEX TempIndex1;
DROP INDEX TempIndex2;
DROP INDEX TempIndex3;

CREATE INDEX TempIndex1 ON emp10RES(EID) tablespace jagindex;
CREATE INDEX TempIndex2 ON emp10RES(StartDate) tablespace jagindex;
CREATE INDEX TempIndex3 ON emp10RES(EndDate) tablespace jagindex;


DROP TABLE emp10COA;
CREATE TABLE emp10COA (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');

  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 ALL
  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 ALL
  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 ALL
  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);


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


  last_timing := DBMS_UTILITY.GET_TIME;

-- Note :
-- The EndDate is INCREMENTED by 1 in each comparison
-- to make the date ranges exactly 1 day apart to be 
-- coalesced. Original coalesce SQL code DID NOT
-- coalesce date ranges 1 day apart. (eg. range 1 = 01/01/95 to 02/01/95, 
--                                        range 2 = 02/02/95 to 02/27/95)

  INSERT INTO emp10coa
	SELECT DISTINCT F.EID, F.StartDate, L.EndDate
	FROM   emp10RES F, emp10RES L
	WHERE  F.StartDate < L.EndDate AND
		   F.EID = L.EID AND
		   NOT EXISTS (SELECT *
					   FROM   emp10RES M
					   WHERE  M.EID = F.EID AND
							  F.StartDate < M.StartDate AND
							  M.StartDate < (L.EndDate + 1) AND
							  NOT EXISTS (SELECT *
										  FROM   emp10RES T1
										  WHERE  T1.EID = F.EID AND
												 T1.StartDate < M.StartDate AND
												 M.StartDate <= (T1.EndDate + 1))) AND
							  NOT EXISTS (SELECT *
										  FROM   emp10RES T2
										  WHERE  T2.EID = F.EID AND
												 ((T2.StartDate < F.StartDate AND
												   F.StartDate <= (T2.EndDate + 1)) OR
												  (T2.StartDate <= (L.EndDate + 1) AND
												   L.EndDate < T2.EndDate)
												 )
					   );

  DBMS_OUTPUT.PUT_LINE('[emp10 PureSQL Coalesce] 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;
/

DROP TABLE emp10RES;

quit



