Re: Optimizing Queries Against UNIONed View

From: <artmt_at_hotmail.com>
Date: 24 Jan 2007 07:15:10 -0800
Message-ID: <1169651704.814116.80780@j27g2000cwj.googlegroups.com>

Apparently the FILTER operation on the plan means table elimination rather than row filtering. Depending on whether I use CODE_TYPE = 'CODE_TYPE1' or CODE_TYPE = 'CODE_TYPE2' - the position of the filter operation on the plan moves.

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

SELECT STATEMENT Optimizer Mode=ALL_ROWS 2 3

  VIEW	FI_OPERATIONS.CODE_LOOKUP	2  	52  	3
    SORT UNIQUE		2  	52  	3
      UNION-ALL
        FILTER
          TABLE ACCESS FULL	FI_OPERATIONS.CODE_TYPE1	120 K	2
M	153.884715586093
        TABLE ACCESS BY INDEX ROWID	FI_OPERATIONS.CODE_TYPE2	1  	26  	1

          INDEX RANGE SCAN	FI_OPERATIONS.IDX2	1  	 	1



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

SELECT STATEMENT Optimizer Mode=ALL_ROWS		120 K	 	1055.92160405075

  VIEW	FI_OPERATIONS.CODE_LOOKUP	120 K	2 M	1055.92160405075

    SORT UNIQUE		120 K	2 M	1055.92160405075
      UNION-ALL
        TABLE ACCESS FULL	FI_OPERATIONS.CODE_TYPE1	120 K	2
M	153.884715586093
        FILTER
          TABLE ACCESS BY INDEX ROWID	FI_OPERATIONS.CODE_TYPE2	1  	26
	1
            INDEX RANGE SCAN	FI_OPERATIONS.IDX2	1  	 	1

I verified with autotrace that only one table gets accessed. Received on Wed Jan 24 2007 - 09:15:10 CST