select Establishment_id, opening_date, name, nl.phone, notes, est_business_name, is_seasonal, season_open_date, season_close_date, 
attn, (Street1 || ' ' || Street2) Street, city, state_code state, zip_code zip, a.email from VW_NOLICENSE_VK NL
join address A
on NL.address_id = A.address_id (+)

select Establishment_id, opening_date, name, nl.phone, notes, est_business_name, is_seasonal, season_open_date, season_close_date, 
attn, (Street1 || ' ' || Street2) Street, city, state_code state, zip_code zip, a.email from VW_NOLICENSE_VK NL
join address A
on NL.address_id = A.address_id 

select Establishment_id, opening_date, name, nl.phone, notes, est_business_name, is_seasonal, season_open_date, season_close_date, 
attn, (Street1 || ' ' || Street2) Street, city, state_code state, zip_code zip, a.email, inspector_id from VW_NOLICENSE_VK NL 
left join address A
on NL.address_id = A.address_id 

Plan
SELECT STATEMENT  ALL_ROWSCost: 534  Bytes: 58,580  Cardinality: 116  									
	14 HASH JOIN  Cost: 534  Bytes: 58,580  Cardinality: 116  								
		12 NESTED LOOPS  Cost: 534  Bytes: 58,580  Cardinality: 116  							
			10 NESTED LOOPS  Cost: 534  Bytes: 58,580  Cardinality: 116  						
				8 STATISTICS COLLECTOR  					
					7 VIEW VIEW SYS.VW_FOJ_0 Cost: 418  Bytes: 52,896  Cardinality: 116  				
						6 FILTER  			
							5 HASH JOIN OUTER  Cost: 418  Bytes: 11,252  Cardinality: 116  		
								2 JOIN FILTER CREATE SYS.:BF0000 Cost: 128  Bytes: 1,007,373  Cardinality: 11,579  	
									1 TABLE ACCESS STORAGE FULL TABLE MDHE.ESTABLISHMENT Cost: 128  Bytes: 1,007,373  Cardinality: 11,579  
								4 JOIN FILTER USE SYS.:BF0000 Cost: 290  Bytes: 1,773,880  Cardinality: 177,388  	
									3 TABLE ACCESS STORAGE FULL TABLE MDHE.ESTABLISHMENT_LICENSE Cost: 290  Bytes: 1,773,880  Cardinality: 177,388  
				9 INDEX UNIQUE SCAN INDEX (UNIQUE) MDHE.PK_ADDRESS Cost: 0  Cardinality: 1  					
			11 TABLE ACCESS BY INDEX ROWID TABLE MDHE.ADDRESS Cost: 1  Bytes: 49  Cardinality: 1  						
		13 TABLE ACCESS STORAGE FULL TABLE MDHE.ADDRESS Cost: 1  Bytes: 49  Cardinality: 1  							

Plan
SELECT STATEMENT  ALL_ROWSCost: 3,890  Bytes: 90,027,865  Cardinality: 178,273  				
	6 HASH JOIN RIGHT OUTER  Cost: 3,890  Bytes: 90,027,865  Cardinality: 178,273  			
		1 TABLE ACCESS STORAGE FULL TABLE MDHE.ADDRESS Cost: 218  Bytes: 4,972,716  Cardinality: 101,484  		
		5 VIEW VIEW SYS.VW_FOJ_0 Cost: 418  Bytes: 81,292,488  Cardinality: 178,273  		
			4 HASH JOIN FULL OUTER  Cost: 418  Bytes: 17,292,481  Cardinality: 178,273  	
				2 TABLE ACCESS STORAGE FULL TABLE MDHE.ESTABLISHMENT Cost: 128  Bytes: 2,440,959  Cardinality: 28,057  
				3 TABLE ACCESS STORAGE FULL TABLE MDHE.ESTABLISHMENT_LICENSE Cost: 290  Bytes: 1,773,880  Cardinality: 177,388  

select Establishment_id, opening_date, name, nl.phone, notes, est_business_name, is_seasonal, season_open_date, season_close_date, 
attn, (Street1 || ' ' || Street2) Street, city, state_code state, zip_code zip, a.email, inspector_id from VW_NOLICENSE_VK NL 
left join address A
on NL.address_id = A.address_id 

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows   | Bytes    | Cost | Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       | 178273 | 90027865 | 3890 | 00:00:01 |
| * 1 |   HASH JOIN RIGHT OUTER        |                       | 178273 | 90027865 | 3890 | 00:00:01 |
|   2 |    TABLE ACCESS STORAGE FULL   | ADDRESS               | 101484 |  4972716 |  218 | 00:00:01 |
| * 3 |    VIEW                        | VW_FOJ_0              | 178273 | 81292488 |  418 | 00:00:01 |
| * 4 |     HASH JOIN FULL OUTER       |                       | 178273 | 17292481 |  418 | 00:00:01 |
|   5 |      TABLE ACCESS STORAGE FULL | ESTABLISHMENT         |  28057 |  2440959 |  128 | 00:00:01 |
|   6 |      TABLE ACCESS STORAGE FULL | ESTABLISHMENT_LICENSE | 177388 |  1773880 |  290 | 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("ADDRESS_ID"="A"."ADDRESS_ID"(+))
* 3 - filter("ESTABLISHMENT_LICENSE_ID" IS NULL AND "E"."EST_CLOSED" IS NULL)
* 4 - access("E"."ESTABLISHMENT_ID"="EL"."ESTABLISHMENT_ID")

select Establishment_id, opening_date, name, nl.phone, notes, est_business_name, is_seasonal, season_open_date, season_close_date, 
attn, (Street1 || ' ' || Street2) Street, city, state_code state, zip_code zip, a.email, inspector_id from VW_NOLICENSE_VK NL 
join address A
on NL.address_id = A.address_id 

 Plan Hash Value  : 4025364740 

---------------------------------------------------------------------------------------------------------
| Id   | Operation                         | Name                  | Rows   | Bytes   | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                  |                       |    116 |   58580 |  534 | 00:00:01 |
|    1 |   NESTED LOOPS                    |                       |    116 |   58580 |  534 | 00:00:01 |
|    2 |    NESTED LOOPS                   |                       |    116 |   58580 |  534 | 00:00:01 |
|    3 |     VIEW                          | VW_FOJ_0              |    116 |   52896 |  418 | 00:00:01 |
|  * 4 |      FILTER                       |                       |        |         |      |          |
|  * 5 |       HASH JOIN OUTER             |                       |    116 |   11252 |  418 | 00:00:01 |
|    6 |        JOIN FILTER CREATE         | :BF0000               |  11579 | 1007373 |  128 | 00:00:01 |
|  * 7 |         TABLE ACCESS STORAGE FULL | ESTABLISHMENT         |  11579 | 1007373 |  128 | 00:00:01 |
|    8 |        JOIN FILTER USE            | :BF0000               | 177388 | 1773880 |  290 | 00:00:01 |
|  * 9 |         TABLE ACCESS STORAGE FULL | ESTABLISHMENT_LICENSE | 177388 | 1773880 |  290 | 00:00:01 |
| * 10 |     INDEX UNIQUE SCAN             | PK_ADDRESS            |      1 |         |    0 | 00:00:01 |
|   11 |    TABLE ACCESS BY INDEX ROWID    | ADDRESS               |      1 |      49 |    1 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("EL"."ESTABLISHMENT_LICENSE_ID" IS NULL)
* 5 - access("E"."ESTABLISHMENT_ID"="EL"."ESTABLISHMENT_ID"(+))
* 7 - storage("E"."EST_CLOSED" IS NULL)
* 7 - filter("E"."EST_CLOSED" IS NULL)
* 9 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"EL"."ESTABLISHMENT_ID"(+)))
* 9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"EL"."ESTABLISHMENT_ID"(+)))
* 10 - access("ADDRESS_ID"="A"."ADDRESS_ID")


Notes
-----
- This is an adaptive plan

where establishment_LICENSE_id is null;

CREATE OR REPLACE FORCE VIEW MDHE.VW_NOLICENSE_VK
(ESTABLISHMENT_ID, OPENING_DATE, NAME, PHONE, NOTES, 
 EST_BUSINESS_NAME, IS_SEASONAL, SEASON_OPEN_DATE, SEASON_CLOSE_DATE, ADDRESS_ID, 
 INSPECTOR_ID)
BEQUEATH DEFINER
AS 
select Establishment_id, opening_date, name, phone, notes, est_business_name, is_seasonal, season_open_date, season_close_date, 
address_id, inspector_id from (
select e.Establishment_id, opening_date, name, phone, e.notes, est_business_name, is_seasonal, season_open_date, season_close_date, 
address_id, inspector_id, establishment_LICENSE_id   from 
establishment e
full join 
establishment_license el
on e.establishment_id  = el.establishment_id 
where e.est_closed is NULL 
)
where establishment_LICENSE_id is null;

DROP TABLE MDHE.ESTABLISHMENT_LICENSE CASCADE CONSTRAINTS;

CREATE TABLE MDHE.ESTABLISHMENT_LICENSE
(
  ESTABLISHMENT_ID          NUMBER              NOT NULL,
  APPROVED_BY               NUMBER,
  NOTES                     VARCHAR2(500 BYTE),
  LICENSE_TYPE_CODE         VARCHAR2(30 BYTE),
  ESTABLISHMENT_LICENSE_ID  NUMBER              NOT NULL,
  BEGIN_DATE                DATE,
  EXPIRATION_DATE           DATE,
  STATUS_CODE               VARCHAR2(30 BYTE)   DEFAULT 'VALID'               NOT NULL,
  IS_CONDITIONAL            CHAR(1 BYTE)        DEFAULT 'N'                   NOT NULL,
  LICENSE_FEE               NUMBER              DEFAULT 0                     NOT NULL,
  CONDITIONAL_REASON_ID     NUMBER(4),
  CONDITIONAL_DAYS_ID       NUMBER(4)
)
TABLESPACE DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

COMMENT ON TABLE MDHE.ESTABLISHMENT_LICENSE IS 'This table contains all the liceses that an establishment has had.
It will generate a history OF licenses AS they EXPIRE AND get renew.';

COMMENT ON COLUMN MDHE.ESTABLISHMENT_LICENSE.STATUS_CODE IS 'To determine the licese status (VALID, CANCEL, ON_HOLD, Etc)
NOTE: No ACTIVE status IS necesary, the Expiration DATE will determine IF license IS active OR NOT.';

COMMENT ON COLUMN MDHE.ESTABLISHMENT_LICENSE.IS_CONDITIONAL IS 'IF "N" then the license is a full license, other wise is a conditional (not to be renewed) license';


CREATE INDEX MDHE.ESTABLISHMENT_LICENSE_ID2 ON MDHE.ESTABLISHMENT_LICENSE
(LICENSE_TYPE_CODE)
LOGGING
TABLESPACE DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX MDHE.ESTABLISHMENT_LICENSE_IX1 ON MDHE.ESTABLISHMENT_LICENSE
(ESTABLISHMENT_ID)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE UNIQUE INDEX MDHE.PK_ESTABLISHMENT_LICENSE ON MDHE.ESTABLISHMENT_LICENSE
(ESTABLISHMENT_LICENSE_ID)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE OR REPLACE TRIGGER MDHE.SET_LICENSE_STATUS
BEFORE UPDATE OR INSERT ON MDHE.ESTABLISHMENT_LICENSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN


  /* first check to see if the exp date has changed, if it has and if the status is valid then check to see if its a valid end date */
  IF ((:NEW.STATUS_CODE = 'VALID') AND (TRUNC(:NEW.Expiration_date) < TRUNC(SYSDATE))) THEN
    -- IF  LicenseEndMonthDayOK(:new.expiration_date, :new.license_type_code) = 0 THEN
   -- IF LicenseEndMonthDayOK(to_date('07/28/2009','mm/dd/yyyy'), 32) = 0 THEN

             RAISE_APPLICATION_ERROR (-20999,'Invalid Expiration Date');
   --  END IF;
  END IF;
  IF :OLD.license_type_code IN  ('08','17','32','39','51','59','81')
     AND :OLD.Expiration_date < TRUNC(SYSDATE)
     AND :OLD.STATUS_CODE = 'VALID'
     AND :OLD.STATUS_CODE = :NEW.STATUS_CODE THEN
         :NEW.STATUS_CODE := 'CANCEL';
  END IF;
  IF :OLD.license_type_code NOT IN  ('08','17','32','39','51','59','81')
     AND :OLD.Expiration_date < TRUNC(SYSDATE)
     AND :OLD.STATUS_CODE = 'VALID'
     AND :OLD.STATUS_CODE = :NEW.STATUS_CODE THEN
       :NEW.STATUS_CODE := 'EXPIRED';
  END IF;

END;
/


ALTER TABLE MDHE.ESTABLISHMENT_LICENSE ADD (
  CONSTRAINT PK_ESTABLISHMENT_LICENSE
  PRIMARY KEY
  (ESTABLISHMENT_LICENSE_ID)
  USING INDEX MDHE.PK_ESTABLISHMENT_LICENSE
  ENABLE VALIDATE);

ALTER TABLE MDHE.ESTABLISHMENT_LICENSE ADD (
  CONSTRAINT FK_LICENCE_ESTABLISHMENT 
  FOREIGN KEY (ESTABLISHMENT_ID) 
  REFERENCES MDHE.ESTABLISHMENT (ESTABLISHMENT_ID)
  ENABLE VALIDATE
,  CONSTRAINT FK_LICENSE_TYPE 
  FOREIGN KEY (LICENSE_TYPE_CODE) 
  REFERENCES MDHE.LICENSE_TYPE (LICENSE_TYPE_CODE)
  DISABLE NOVALIDATE);

SQL Monitoring Report

SQL Text
------------------------------
select/*+ GATHER_PLAN_STATISTICS MONITOR */ e.establishment_id, el.establishment_id from establishment e full join establishment_license el on e.establishment_id = el.establishment_id

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)                                    
 Instance ID         :  3                                                  
 Session             :  MDHE (113:20617)                                   
 SQL ID              :  5mh955qrd56hb                                      
 SQL Execution ID    :  50331651                                           
 Execution Started   :  07/23/2019 11:52:04                                
 First Refresh Time  :  07/23/2019 11:52:04                                
 Last Refresh Time   :  07/23/2019 11:52:05                                
 Duration            :  1s                                                 
 Module/Action       :  TOAD Freeware 13.1.0.78/34909424,64289200,52344192 
 Service             :  mdhetst_dg                                         
 Program             :  Toad.exe                                           
 Fetch Calls         :  224                                                

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.21 |    0.07 |     0.14 |   224 |    453 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=975423473)
===================================================================================================================================================================
| Id |            Operation             |           Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                  |                           | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
===================================================================================================================================================================
|  0 | SELECT STATEMENT                 |                           |         |      |         2 |     +0 |     1 |     178K |     . |          |                 |
|  1 |   VIEW                           | VW_FOJ_0                  |    178K |   87 |         2 |     +0 |     1 |     178K |     . |          |                 |
|  2 |    HASH JOIN FULL OUTER          |                           |    178K |   87 |         2 |     +0 |     1 |     178K |   7MB |          |                 |
|  3 |     INDEX STORAGE FAST FULL SCAN | PK_ESTABLISHMENT          |   28057 |   13 |         1 |     +0 |     1 |    28057 |     . |          |                 |
|  4 |     INDEX STORAGE FAST FULL SCAN | ESTABLISHMENT_LICENSE_IX1 |    177K |   74 |         2 |     +0 |     1 |     177K |     . |          |                 |
===================================================================================================================================================================

BANNER,CON_ID
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production,0
PL/SQL Release 12.2.0.1.0 - Production,0
CORE	12.2.0.1.0	Production,0
TNS for Linux: Version 12.2.0.1.0 - Production,0
NLSRTL Version 12.2.0.1.0 - Production,0