Home » RDBMS Server » Performance Tuning » Optimizing Queries Against UNIONed View
Optimizing Queries Against UNIONed View [message #215744] Tue, 23 January 2007 14:37 Go to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
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?
Please see the complete scripts and explain plan below.
I am running Oracle 10.1.0.5.0

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  	

[Updated on: Tue, 23 January 2007 14:50]

Report message to a moderator

Re: Optimizing Queries Against UNIONed View [message #215759 is a reply to message #215744] Tue, 23 January 2007 15:20 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
I have made the following changes:

I made CODE_TYPE a real column in both tables and put CHECK constraint on it.
I also updated the view to reflect that change.

None of this worked.


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;
Re: Optimizing Queries Against UNIONed View [message #215772 is a reply to message #215759] Tue, 23 January 2007 18:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Believe it or not, it's not actually scanning the other table. You can tell by the FILTER step in you Explain Plan. Remove the
CODE_TYPE = 'CODE_TYPE2'
predicate and check the plan again. Now change it to CODE_TYPE = 'CODE_TYPE1' - note how the FILTER moves to the other table.

If you are still sceptical, trace it and run it through TK*Prof - you'll see no data processed against the Explain Plan steps for the filtered table.

Ross Leishman
Re: Optimizing Queries Against UNIONed View [message #215982 is a reply to message #215772] Wed, 24 January 2007 09:15 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
Thank you Ross.
You rock!
Re: Optimizing Queries Against UNIONed View [message #216027 is a reply to message #215772] Wed, 24 January 2007 13:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
mmmm....
I was working on this thread for a while (Excellent DDL scripts by Artmt made it easier to reproduce) and was really reaching nowhere!. Embarassed
Ross is cool Smile

Re: Optimizing Queries Against UNIONed View [message #216055 is a reply to message #216027] Wed, 24 January 2007 20:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Mahesh Rajendran wrote on Thu, 25 January 2007 06:10
mmmm....
I was working on this thread for a while (Excellent DDL scripts by Artmt made it easier to reproduce) and was really reaching nowhere!. Embarassed
Ross is cool Smile




Finally we agree on something... Very Happy
Re: Optimizing Queries Against UNIONed View [message #216350 is a reply to message #215744] Fri, 26 January 2007 14:50 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member

Hey Ross, just happened to browse through the site and could not stop logging in ...just to say ...you're amazing!

You rock dude!


Thanks,
Srinivas

Re: Optimizing Queries Against UNIONed View [message #216406 is a reply to message #216350] Sat, 27 January 2007 03:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are you talking about OraFAQ (I can't take any credit for that), or my homepage. Whatever, glad I could help.
Re: Optimizing Queries Against UNIONed View [message #216594 is a reply to message #215744] Mon, 29 January 2007 11:02 Go to previous message
srinivas4u2
Messages: 66
Registered: June 2005
Member

I meant your helping folks in Orafaq, especially your amazing sql tuning....I like the way you guess the execution plan so correctly so often!

Didn't get a chance to fully browse through your site but I sure will when I get a chance.


Cheers!

Srinivas
Previous Topic: Which one best ?
Next Topic: Performance Implications of a Generic Lookup Table Design
Goto Forum:
  


Current Time: Tue Dec 06 16:10:02 CST 2016

Total time taken to generate the page: 0.09736 seconds