Optimizing Queries Against UNIONed View

From: <artmt_at_hotmail.com>
Date: 22 Jan 2007 12:51:57 -0800
Message-ID: <1169499117.751324.13630@11g2000cwr.googlegroups.com>

I have two tables - CODE_TYPE1 and CODE_TYPE2 - containing CODE_NAME and CODE_DESC columns.

I create the following view:
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; When I issue the following query:
SELECT * FROM CODE_LOOKUP WHERE CODE_TYPE = 'CODE_TYPE2'; the optimizer chooses to scan both tables.

Is there a way (other than a meterialized view) to make sure it goes directly to the CODE_TYPE2 table?
Please see the complete scripts and explain plan below.

Thanks
Art

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

Received on Mon Jan 22 2007 - 14:51:57 CST