CREATE OR REPLACE VIEW CODE_LOOKUP
(CODE_TYPE, CODE_NAME, CODE_DESC)
AS 
SELECT 'CODE_TYPE1' CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE1
UNION ALL
SELECT 'CODE_TYPE2' CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE2;

SELECT * FROM CODE_LOOKUP WHERE CODE_TYPE = 'CODE_TYPE2';

CREATE TABLE CODE_TYPE1
(
  CODE_NAME  VARCHAR2(10 BYTE),
  CODE_DESC  VARCHAR2(10 BYTE)
);


CREATE UNIQUE INDEX PK_CODE_TYPE1 ON CODE_TYPE1
(CODE_NAME);


ALTER TABLE CODE_TYPE1 ADD (
  CONSTRAINT PK_CODE_TYPE1
 PRIMARY KEY
 (CODE_NAME));

INSERT INTO CODE_TYPE1
SELECT 'CD'||TO_CHAR(ROWNUM+5), 'CD'||TO_CHAR(ROWNUM+5)
FROM
(SELECT 1 C1 FROM DBA_OBJECTS
UNION ALL
SELECT 1 C1 FROM DBA_OBJECTS
UNION ALL
SELECT 1 C1 FROM DBA_OBJECTS);
COMMIT;

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'A410'
     ,TabName        => 'CODE_TYPE1'
    ,Method_Opt        => 'FOR ALL INDEXED COLUMNS SIZE AUTO '
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;
/


CREATE TABLE CODE_TYPE2
(
  CODE_NAME  VARCHAR2(10 BYTE),
  CODE_DESC  VARCHAR2(10 BYTE)
);


CREATE UNIQUE INDEX PK_CODE_TYPE2 ON CODE_TYPE2
(CODE_NAME);


ALTER TABLE CODE_TYPE2 ADD (
  CONSTRAINT PK_CODE_TYPE2
 PRIMARY KEY
 (CODE_NAME));

INSERT INTO CODE_TYPE2 VALUES('CD1','CODE1');
INSERT INTO CODE_TYPE2 VALUES('CD2','CODE2');
COMMIT;

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'A410'
     ,TabName        => 'CODE_TYPE2'
    ,Method_Opt        => 'FOR ALL INDEXED COLUMNS SIZE AUTO '
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;
/

CREATE OR REPLACE VIEW CODE_LOOKUP
(CODE_TYPE, CODE_NAME, CODE_DESC)
AS 
SELECT 'CODE_TYPE1' CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE1
UNION ALL
SELECT 'CODE_TYPE2' CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE2;

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

SELECT * FROM CODE_LOOKUP WHERE CODE_TYPE = 'CODE_TYPE2' AND CODE_NAME = 'CD5';

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		2  	 	1.00124917907057  	 	      	             	 
  VIEW	A410.CODE_LOOKUP	2  	52  	1.00124917907057  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	A410.CODE_TYPE1	1  	16  	3.00246672876649  	 	      	             	 
          INDEX UNIQUE SCAN	A410.PK_CODE_TYPE1	1  	 	2.00184342150316  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	A410.CODE_TYPE2	1  	7  	1.00124917907057  	 	      	             	 
        INDEX UNIQUE SCAN	A410.PK_CODE_TYPE2	1  	 	.000625871807228915  	

CREATE TABLE CODE_TYPE1
(
  CODE_NAME  VARCHAR2(10 BYTE),
  CODE_DESC  VARCHAR2(10 BYTE),
  CODE_TYPE  VARCHAR2(20 BYTE)                  DEFAULT 'CODE_TYPE1'
);


CREATE UNIQUE INDEX PK_CODE_TYPE1 ON CODE_TYPE1
(CODE_NAME);


ALTER TABLE CODE_TYPE1 ADD (
  CONSTRAINT CK_CODE_TYPE1
 CHECK (CODE_TYPE = 'CODE_TYPE1'));

ALTER TABLE CODE_TYPE1 ADD (
  CONSTRAINT PK_CODE_TYPE1
 PRIMARY KEY
 (CODE_NAME));

CREATE OR REPLACE VIEW CODE_LOOKUP
(CODE_TYPE, CODE_NAME, CODE_DESC)
AS 
SELECT CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE1
UNION ALL
SELECT CODE_TYPE, CODE_NAME, CODE_DESC FROM CODE_TYPE2;

CODE_TYPE = 'CODE_TYPE2'