Re: Optimizing Queries Against UNIONed View

From: <artmt_at_hotmail.com>
Date: 23 Jan 2007 11:25:19 -0800
Message-ID: <1169580319.246461.90150@m58g2000cwm.googlegroups.com>

Valentin Minzatu wrote:
> You now know for sure the same row cannot repeat among the tables so
> you can remove the ALL from the UNION clause. I think that should help
> you getting rid of the full table scan on both tables. (btw, I assume
> CODE_TYPE column is indexed)

Replacing UNION ALL with UNION had no effect. The value of the CODE_TYPE column is the same for all rows and therefore is not indexd. It is the same as the table name.

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)); Received on Tue Jan 23 2007 - 13:25:19 CST