Skip to Main Content
  • Questions

  • How to use push predicate using union all view with different index component?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Chang-sei.

Asked: March 21, 2016 - 8:01 am UTC

Last updated: March 23, 2016 - 9:58 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi..

I've created a view with 5 tables using "union all"
and wrote an sql that used this view with another table..
I expected index range scan of view component tables through predicate push,
but execution plan showed full scan as follows..

Can you please tell me why this occurred and how to solve?

Thanks..

----------------------------------------
Table creation script (1st table of union all view)
----------------------------------------

CREATE TABLE EESC2_EQP_CEID_TRX_SITE (
TX_DATE DATE,
RAWID NUMBER,
EQP_RAWID NUMBER,
CEID VARCHAR2(100),
EVENT_DTTS DATE,
LOT_TYPE_CD VARCHAR2(10),
LOT_ID VARCHAR2(10)
);

CREATE TABLE EESM10_EQP_CEID_TRX_SITE (
TX_DATE DATE,
RAWID NUMBER,
EQP_RAWID NUMBER,
CEID VARCHAR2(100),
EVENT_DTTS DATE,
LOT_TYPE_CD VARCHAR2(10),
LOT_ID VARCHAR2(10)
);

CREATE TABLE EESM11_EQP_CEID_TRX_SITE (
TX_DATE DATE,
RAWID NUMBER,
EQP_RAWID NUMBER,
CEID VARCHAR2(100),
EVENT_DTTS DATE,
LOT_TYPE_CD VARCHAR2(10),
LOT_ID VARCHAR2(10)
);

CREATE TABLE EESM12_EQP_CEID_TRX_SITE (
TX_DATE DATE,
RAWID NUMBER,
EQP_RAWID NUMBER,
CEID VARCHAR2(100),
EVENT_DTTS DATE,
LOT_TYPE_CD VARCHAR2(10),
LOT_ID VARCHAR2(10)
);

CREATE TABLE EESM14_DCP_WAFER_HIS (
WAFER_ID VARCHAR2(10) NOT NULL,
TX_DATE DATE,
EVENT_NM VARCHAR2(100) NOT NULL,
END_TM DATE,
START_TM DATE NOT NULL,
EQP_ID VARCHAR2(10),
MODULE_ID VARCHAR2(10)
);


----------------------------------------
Index creation script (1st table of union all view)
----------------------------------------

CREATE INDEX EESC2_DCP_WAFER_HIS_IX1 ON EESC2_EQP_CEID_TRX_SITE(EQP_RAWID, EVENT_DTTS);

CREATE INDEX EESM10_DCP_WAFER_HIS_IX1 ON EESM10_EQP_CEID_TRX_SITE(EQP_RAWID, EVENT_DTTS);

CREATE INDEX EESM11_DCP_WAFER_HIS_IX1 ON EESM11_EQP_CEID_TRX_SITE(EQP_RAWID, EVENT_DTTS);

CREATE INDEX EESM12_DCP_WAFER_HIS_IX1 ON EESM12_EQP_CEID_TRX_SITE(EQP_RAWID, EVENT_DTTS);

-- M14 has a different index composition
CREATE INDEX EESM14_DCP_WAFER_HIS_IX1 ON EESM14_DCP_WAFER_HIS(EQP_ID, START_TM);

ALTER TABLE EESM14_DCP_WAFER_HIS ADD CONSTRAINT EESM14_DCP_WAFER_HIS_PK PRIMARY KEY (WAFER_ID, EVENT_NM, START_TM) USING INDEX;


----------------------------------------
Table creation script (Another table for join)
----------------------------------------

CREATE TABLE EPA_EESEQPBASE_HIS (
SYS_CATG_CD VARCHAR2(10),
LINE_ID VARCHAR2(10),
CUTOFF_KEY VARCHAR2(10),
CUTOFF_CYCLE VARCHAR2(10),
EQP_ID VARCHAR2(10),
MOD_NM VARCHAR2(100),
RAWID NUMBER,
C1 VARCHAR2(10)
);

ALTER TABLE EPA_EESEQPBASE_HIS ADD CONSTRAINT EPA_EESEQPBASE_HIS_PK
PRIMARY KEY (SYS_CATG_CD,CUTOFF_KEY,CUTOFF_CYCLE,LINE_ID,EQP_ID,MOD_NM, C1) USING INDEX;

----------------------------------------
View script
----------------------------------------

CREATE OR REPLACE VIEW EES_EQPWF_H AS
SELECT
'C2' AS SYS_CATG_CD,
TX_DATE AS TX_DATE,
RAWID AS RAWID,
EQP_RAWID AS EQP_RAWID,
CEID AS CE_ID,
TO_DATE(NULL) AS EVENT_END_TM,
EVENT_DTTS AS EVENT_TM,
TO_CHAR(NULL) AS EQP_ID,
TO_CHAR(NULL) AS MOD_ID,
LOT_TYPE_CD AS LOT_CD,
LOT_ID AS LOT_ID
-- , …
FROM EESC2_EQP_CEID_TRX_SITE
UNION ALL
SELECT 'M10' AS SYS_CATG_CD,
TX_DATE AS TX_DATE,
RAWID AS RAWID,
EQP_RAWID AS EQP_RAWID,
CEID AS CE_ID,
TO_DATE(NULL) AS EVENT_END_TM,
EVENT_DTTS AS EVENT_TM,
TO_CHAR(NULL) AS EQP_ID,
TO_CHAR(NULL) AS MOD_ID,
LOT_TYPE_CD AS LOT_CD,
LOT_ID AS LOT_ID
-- , …
FROM EESM10_EQP_CEID_TRX_SITE
UNION ALL
SELECT 'M11' AS SYS_CATG_CD,
TX_DATE AS TX_DATE,
RAWID AS RAWID,
EQP_RAWID AS EQP_RAWID,
CEID AS CE_ID,
TO_DATE(NULL) AS EVENT_END_TM,
EVENT_DTTS AS EVENT_TM,
TO_CHAR(NULL) AS EQP_ID,
TO_CHAR(NULL) AS MOD_ID,
LOT_TYPE_CD AS LOT_CD,
LOT_ID AS LOT_ID
-- , …
FROM EESM11_EQP_CEID_TRX_SITE
UNION ALL
SELECT 'M12' AS SYS_CATG_CD,
TX_DATE AS TX_DATE,
RAWID AS RAWID,
EQP_RAWID AS EQP_RAWID,
CEID AS CE_ID,
TO_DATE(NULL) AS EVENT_END_TM,
EVENT_DTTS AS EVENT_TM,
TO_CHAR(NULL) AS EQP_ID,
TO_CHAR(NULL) AS MOD_ID,
LOT_TYPE_CD AS LOT_CD,
LOT_ID AS LOT_ID
-- , …
FROM EESM12_EQP_CEID_TRX_SITE
UNION ALL
SELECT 'M14' AS SYS_CATG_CD,
TX_DATE AS TX_DATE,
TO_NUMBER(NULL) AS RAWID, --NULL
-1 AS EQP_RAWID, --NULL
EVENT_NM AS CE_ID,
END_TM AS EVENT_END_TM,
START_TM AS EVENT_TM,
EQP_ID AS EQP_ID,
MODULE_ID AS MOD_ID,
TO_CHAR(NULL) AS LOC_CD,
TO_CHAR(NULL) AS LOT_ID
-- , …
FROM EESM14_DCP_WAFER_HIS

----------------------------------------
SQL & execution plan in question
----------------------------------------

SELECT /*+ ORDERED USE_NL(B A) */
SUM( DECODE( A.CE_ID, 'WAFERSTART', 1, 0 ) ) AS STARTCOUNT
, SUM( DECODE( A.CE_ID, 'WAFEREND', 1, 0 ) ) AS ENDCOUNT
FROM EPA_EESEQPBASE_HIS B
, EES_EQPWF_H A
WHERE 1=1
AND A.EQP_RAWID = B.RAWID
-- AND A.EQP_ID = B.EQP_ID
AND B.SYS_CATG_CD = :V_SYS_CATG_CD --'M11'
AND B.LINE_ID = :V_LINE_ID --'M11'
AND B.CUTOFF_KEY = :V_CUTOFF_KEY -- '20151204'
AND B.CUTOFF_CYCLE = :V_CUTOFF_CYCLE --'D'
AND B.EQP_ID = :V_EQP_ID --'TWS807'
AND B.MOD_NM = :V_MOD_NM -- 'TWS807_3'
AND A.SYS_CATG_CD = B.SYS_CATG_CD --'M11'
AND A.CE_ID IN ('WAFERSTART', 'WAFEREND')
AND A.EVENT_TM >= TO_TIMESTAMP( :V_EVENT_TM_1, 'yyyy-mm-dd hh24:mi:ss' ) --'2015-12-04 06:58:59'
AND A.EVENT_TM < TO_TIMESTAMP( :V_EVENT_TM_2 , 'yyyy-mm-dd hh24:mi:ss' ) --'2015-12-05 06:59:48'
;

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 168 | | | 1 |00:00:00.01 |
| 2 | NESTED LOOPS | | 1 | 1 | 168 | 10 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EPA_EESEQPBASE_HIS | 1 | 1 | 100 | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 4 | INDEX RANGE SCAN | EPA_EESEQPBASE_HIS_PK | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 5 | VIEW | EES_EQPWF_H | 0 | 1 | 68 | 9 (0)| 00:00:01 | 0 |00:00:00.01 |
| 6 | UNION-ALL | | 0 | | | | | 0 |00:00:00.01 |
|* 7 | FILTER | | 0 | | | | | 0 |00:00:00.01 |
|* 8 | TABLE ACCESS FULL | EESC2_EQP_CEID_TRX_SITE | 0 | 1 | 74 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 9 | FILTER | | 0 | | | | | 0 |00:00:00.01 |
|* 10 | TABLE ACCESS FULL | EESM10_EQP_CEID_TRX_SITE | 0 | 1 | 74 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 11 | FILTER | | 0 | | | | | 0 |00:00:00.01 |
|* 12 | TABLE ACCESS FULL | EESM11_EQP_CEID_TRX_SITE | 0 | 1 | 74 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 13 | FILTER | | 0 | | | | | 0 |00:00:00.01 |
|* 14 | TABLE ACCESS FULL | EESM12_EQP_CEID_TRX_SITE | 0 | 1 | 74 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 15 | FILTER | | 0 | | | | | 0 |00:00:00.01 |
|* 16 | INDEX FULL SCAN | EESM14_DCP_WAFER_HIS_PK | 0 | 1 | 61 | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------------------------------

In summary, why didn't optimizer use individual index ?

and Chris said...

Well for the most part, it doesn't need to.

SYS_CATG_CD is a constant. It's different for each union all branch in the view. The query has the predicates:

and b.sys_catg_cd  = :v_sys_catg_cd  
and a.sys_catg_cd  = b.sys_catg_cd


So Oracle can infer that:

and a.sys_catg_cd  = :v_sys_catg_cd


So it only needs to access one query from the unions. It can use this information to exclude the others.

I'll load 100 rows to each of the tables in the view and one to the table you join outside it:

insert into epa_eeseqpbase_his (
  sys_catg_cd, line_id, cutoff_key, cutoff_cycle, eqp_id, mod_nm,
  rawid, c1
)  values (
  'M11', 1, 1, 1, 1, 1, 1, 1
) ;

insert into EESC2_EQP_CEID_TRX_SITE
  select sysdate, 1, 1, 'WAFERSTART', sysdate, 1, 1 
  from dual connect by level <= 100;
  
insert into EESM10_EQP_CEID_TRX_SITE
  select sysdate, 1, 1, 'WAFERSTART', sysdate, 1, 1 
  from dual connect by level <= 100;
  
insert into EESM11_EQP_CEID_TRX_SITE
  select sysdate, 1, 1, 'WAFERSTART', sysdate, 1, 1 
  from dual connect by level <= 100;
  
insert into EESM12_EQP_CEID_TRX_SITE
  select sysdate, 1, 1, 'WAFERSTART', sysdate, 1, 1 
  from dual connect by level <= 100;

insert into EESM14_DCP_WAFER_HIS
  select rownum, sysdate, 1, sysdate, sysdate, 1, 1 
  from dual connect by level <= 100;
    
commit;


We'll set :v_sys_catg_cd to M11. So we only need values from EESM11_EQP_CEID_TRX_SITE:

var v_sys_catg_cd varchar2(10);
exec :v_sys_catg_cd := 'M11';

select /*+ gather_plan_statistics */
  sum ( decode ( a.ce_id, 'WAFERSTART', 1, 0 ) ) as startcount , 
  sum ( decode ( a.ce_id, 'WAFEREND', 1, 0 ) ) as endcount
from epa_eeseqpbase_his b , ees_eqpwf_h a
where 1         =1
and a.eqp_rawid = b.rawid
and b.sys_catg_cd  = :v_sys_catg_cd 
and a.sys_catg_cd  = b.sys_catg_cd   --'M11'
and a.ce_id       in ( 'WAFERSTART', 'WAFEREND' );

select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                              
--------------------------------------------------------------------------------------------------------------------
SQL_ID  6zyydwg2tgu67, child number 0                                                                                                                           
-------------------------------------                                                                                                                           
select /*+ gather_plan_statistics */   sum ( decode ( a.ce_id,                                                                                                  
'WAFERSTART', 1, 0 ) ) as startcount ,    sum ( decode ( a.ce_id,                                                                                               
'WAFEREND', 1, 0 ) ) as endcount from epa_eeseqpbase_his b ,                                                                                                    
ees_eqpwf_h a where 1         =1 and a.eqp_rawid = b.rawid and                                                                                                  
b.sys_catg_cd  = :v_sys_catg_cd  and a.sys_catg_cd  = b.sys_catg_cd                                                                                             
--'M11' and a.ce_id       in ( 'WAFERSTART', 'WAFEREND' )                                                                                                       
                                                                                                                                                                
Plan hash value: 3783881811                                                                                                                                     
                                                                                                                                                                
--------------------------------------------------------------------------------------------------------------------                                            
| Id  | Operation                     | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                            
--------------------------------------------------------------------------------------------------------------------                                            
|   0 | SELECT STATEMENT              |                          |      1 |        |      1 |00:00:00.01 |       8 |                                            
|   1 |  SORT AGGREGATE               |                          |      1 |      1 |      1 |00:00:00.01 |       8 |                                            
|*  2 |   HASH JOIN                   |                          |      1 |      5 |    100 |00:00:00.01 |       8 |                                            
|   3 |    TABLE ACCESS BY INDEX ROWID| EPA_EESEQPBASE_HIS       |      1 |      1 |      1 |00:00:00.01 |       2 |                                            
|*  4 |     INDEX RANGE SCAN          | EPA_EESEQPBASE_HIS_PK    |      1 |      1 |      1 |00:00:00.01 |       1 |                                            
|   5 |    VIEW                       | EES_EQPWF_H              |      1 |      5 |    100 |00:00:00.01 |       6 |                                            
|   6 |     UNION-ALL                 |                          |      1 |        |    100 |00:00:00.01 |       6 |                                            
|*  7 |      FILTER                   |                          |      1 |        |      0 |00:00:00.01 |       0 |                                            
|*  8 |       TABLE ACCESS FULL       | EESC2_EQP_CEID_TRX_SITE  |      0 |      1 |      0 |00:00:00.01 |       0 |                                            
|*  9 |      FILTER                   |                          |      1 |        |      0 |00:00:00.01 |       0 |                                            
|* 10 |       TABLE ACCESS FULL       | EESM10_EQP_CEID_TRX_SITE |      0 |      1 |      0 |00:00:00.01 |       0 |                                            
|* 11 |      FILTER                   |                          |      1 |        |    100 |00:00:00.01 |       6 |                                            
|* 12 |       TABLE ACCESS FULL       | EESM11_EQP_CEID_TRX_SITE |      1 |      1 |    100 |00:00:00.01 |       6 |                                            
|* 13 |      FILTER                   |                          |      1 |        |      0 |00:00:00.01 |       0 |                                            
|* 14 |       TABLE ACCESS FULL       | EESM12_EQP_CEID_TRX_SITE |      0 |      1 |      0 |00:00:00.01 |       0 |                                            
|* 15 |      FILTER                   |                          |      1 |        |      0 |00:00:00.01 |       0 |                                            
|* 16 |       INDEX FULL SCAN         | EESM14_DCP_WAFER_HIS_PK  |      0 |      1 |      0 |00:00:00.01 |       0 |                                            
--------------------------------------------------------------------------------------------------------------------                                            
                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                             
---------------------------------------------------                                                                                                             
                                                                                                                                                                
   2 - access("A"."EQP_RAWID"="B"."RAWID" AND "A"."SYS_CATG_CD"="B"."SYS_CATG_CD")                                                                              
   4 - access("B"."SYS_CATG_CD"=:V_SYS_CATG_CD)                                                                                                                 
   7 - filter('C2'=:V_SYS_CATG_CD)                                                                                                                              
   8 - filter(("CEID"='WAFEREND' OR "CEID"='WAFERSTART'))                                                                                                       
   9 - filter('M10'=:V_SYS_CATG_CD)                                                                                                                             
  10 - filter(("CEID"='WAFEREND' OR "CEID"='WAFERSTART'))                                                                                                       
  11 - filter('M11'=:V_SYS_CATG_CD)                                                                                                                             
  12 - filter(("CEID"='WAFEREND' OR "CEID"='WAFERSTART'))                                                                                                       
  13 - filter('M12'=:V_SYS_CATG_CD)                                                                                                                             
  14 - filter(("CEID"='WAFEREND' OR "CEID"='WAFERSTART'))                                                                                                       
  15 - filter('M14'=:V_SYS_CATG_CD)                                                                                                                             
  16 - filter(("EVENT_NM"='WAFEREND' OR "EVENT_NM"='WAFERSTART'))                                                                                               
                                                                                                                                                                

 49 rows selected


Notice how in the plan only EESM11_EQP_CEID_TRX_SITE has any rows returned (A-rows > 0) for the tables in the view.

Now you might ask why hasn't Oracle used the index for this table?

Well in my example all the rows have the same value for eqp_rawid. So it's not going to help. This may be the case in your real world scenario too.

All the tables are empty in your test case. So this isn't going to be representative of what really happens. If you need further help understanding this, you'll need to post an execution plan for the real world query.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for your answer

Chang-sei Oh, March 24, 2016 - 4:57 am UTC

Thanks for your answer..
As you checked the point, the scripts I sent are in test environment.
In fact I didn't prepare the real env.
It's because I've heard the situation in problem.
If necessary, I'll check the real script and send them.

Anyway.. I appreciate of your help..
thanks.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library