Queries have different plan with UNION

User505978 - Oracle

I have 2 query like this:

select * from emp where emp_no=2;

select * from emp where emp_no=3;

when i look at their execution plan seperately, it works fine and no problem but,

when i do this;

select * from emp where emp_no=2
union
select * from emp where emp_no=3;

i see different plan for both.

Is there a way to make use their own plan independently in union statement?

marco

Do you really need UNION ? Maybe UNION ALL is enough? FYI: UNION works as distinct.

Nimish Garg

first point use IN (1,2) in a single query and avoid UNION
second if you want to use UNION, use UNION ALL

Santhosh T

Union is to get the distinct records from both the tables and Union all is to get all the records from the both tables, hence the execution plan will be different.

SQL> explain plan for SELECT *
  2    FROM emp
  3  UNION
  4  SELECT *
  5    FROM emp;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774834881

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    28 |  1036 |     8  (63)| 00:00:01 |
|   1 |  SORT UNIQUE        |      |    28 |  1036 |     8  (63)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

11 rows selected.

SQL> explain plan for SELECT *
  2    FROM emp
  3  UNION ALL
  4  SELECT *
  5    FROM emp;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1301082189

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    28 |  1036 |     6  (50)| 00:00:01 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

10 rows selected.

SQL> select count(*) from (SELECT * FROM emp UNION SELECT * FROM emp);

  COUNT(*)
----------
        14

SQL> SELECT count(*) from ( select * FROM emp UNION ALL SELECT * FROM emp);

  COUNT(*)
----------
        28

Jonathan Lewis

983327 wrote:

when i look at their execution plan seperately, it works fine and no problem but,

when i do this;

select * from emp where emp_no=2
union
select * from emp where emp_no=3;

i see different plan for both.

Is there a way to make use their own plan independently in union statement?

Tell us the version of Oracle, and show us the execution plans first - they we may be able to give you some intelligent advice.

Regards
Jonathan Lewis

User505978 - Oracle

I'm really sorry that i gave little wrong information. My queries are lie the following;

select * from emp where dept_id=10 and emp_id=15

union

select * from emp where dept_id=10 and emp_id=16;


my previous examples were not correct in my first post..

when i look at their plans one by one, i see thet index is used, but when i checked with union, they dont use index.

Off course my real queries are not short like that.

User505978 - Oracle

i tried with union all but it is same, execution plan is changing.

SomeoneElse

What problem are you actually having?

User505978 - Oracle

I'm sending queries and plans. I hope you can und:)it is really long.

Query1

SELECT /*+ use_nl(adres CARD) use_nl(@CUST1 [email protected] [email protected]) index(@CUST1 [email protected] IX2_MUSTERI_NAME) use_nl(@CUST2 [email protected] [email protected]) index(@CUST2 [email protected] IX2_CANDIDATE_NAME)*/
'' AS grp_customer_type,
( cst.name || ' ' || cst.midname || ' ' || cst.surname ) as grp_main_customer_info,
card.main_card_no as grp_main_card_info,
card.main_card_no,
card.card_no,
to_char(cst.banking_customer_no),
cst.business_chain_level,
case when cst.business_chain_level in ( 1,2) then 1 else 0 end AS is_dummy_card,
0 AS supp_seq_no,
cst.name,
cst.midname,
cst.surname,
( cst.name || ' ' || cst.midname || ' ' || cst.surname ) as maincardcustomername,
cst.customer_no,
cst.stat_code,
cst.stmt_stat_code,
card.card_level,
card.card_stat_code || '~' || card.card_sub_stat_code cardstatus,
card.emboss_name1,
card.card_type,
card.product_id,
to_char( to_date( case when length( to_char(card.expiry_date) ) < 6 then '190001' else to_char(card.expiry_date) end,'yyyyMM') ,'MM/yyyy') ,
CARD.MAIN_CUSTOMER_NO,
to_char(cst.tc_no),
cst.mother_name,
cst.father_name,
adres.zip_code,
adres.address1,
cst.company_no,
to_char( to_date( to_char(cst.birth_date),'yyyymmdd') ,'dd/MM/yyyy') ,
'' as irtbilgi ,
card.card_branch,
cst.point_collection_type
FROM
OC_CST.CST_CUSTOMER CST,
oc_cst.cst_customer_address_main adres,
OC_CRD.CRD_CARD CARD
WHERE

CARD.CUSTOMER_NO(+) = CST.CUSTOMER_NO
AND CARD.STATUS(+) = 1
AND CST.STATUS(+) = 1
AND cst.banking_customer_no = adres.banking_customer_no(+)
AND cst.post_adress_idx = adres.address_idx(+)
AND CST.MBR_ID = 101
and cst.name like '%ASDF%'


Execution Plan1


SELECT STATEMENT, GOAL = ALL_ROWS 8792467 1305094 464613464
NESTED LOOPS OUTER 8792467 1305094 464613464
NESTED LOOPS OUTER 5219783 892972 238423524
VIEW OC_CST CST_CUSTOMER 3433411 892972 193774924
UNION-ALL
NESTED LOOPS
NESTED LOOPS 3300974 847502 91530216
MAT_VIEW ACCESS BY INDEX ROWID OC_MVT SNAP_UAT_T_MUSTERI 1082860 1083348 56334096
INDEX FULL SCAN OC_SYS IX2_MUSTERI_NAME 57075 1083348
INDEX RANGE SCAN OC_CST IDX_OC_CST_CUSTOMER_NBNKCST_NO 2 2
TABLE ACCESS BY INDEX ROWID OC_CST CST_CUSTOMER_INFO 4 1 56
NESTED LOOPS
NESTED LOOPS 132429 45469 4774245
MAT_VIEW ACCESS BY INDEX ROWID OC_MVT SNAP_UAT_T_CANDIDATE 26817 27326 1338974
INDEX FULL SCAN OC_SYS IX2_CANDIDATE_NAME 1354 27326
INDEX RANGE SCAN OC_CST IDX_OC_CST_CUSTOMER_NBNKCST_NO 2 2
TABLE ACCESS BY INDEX ROWID OC_CST CST_CUSTOMER_INFO 4 2 112
NESTED LOOPS
NESTED LOOPS 7 1 111
TABLE ACCESS STORAGE FULL OC_CST CST_CUSTOMER_TEMP 3 1 55
INDEX RANGE SCAN OC_CST IDX_OC_CST_CUSTOMER_NBNKCST_NO 2 2
TABLE ACCESS BY INDEX ROWID OC_CST CST_CUSTOMER_INFO 4 2 112
MAT_VIEW ACCESS BY INDEX ROWID OC_MVT SNAP_T_ADRES 2 1 50
INDEX UNIQUE SCAN OC_MVT T_ADRES_CLIENT_NO2 1 1
TABLE ACCESS BY INDEX ROWID OC_CRD CRD_CARD 4 1 89
INDEX RANGE SCAN OC_CRD IDX_CRD_CARD_05 2 1






Query2

SELECT /*+ use_nl(adres CARD) use_nl(@CUST1 [email protected] [email protected]) index(@CUST1 [email protected] IX2_MUSTERI_NAME) use_nl(@CUST2 [email protected] [email protected]) index(@CUST2 [email protected] IX2_CANDIDATE_NAME)*/
'' AS grp_customer_type,
( cst.name || ' ' || cst.midname || ' ' || cst.surname ) as grp_main_customer_info,
card.main_card_no as grp_main_card_info,
card.main_card_no,
card.card_no,
to_char(cst.banking_customer_no),
cst.business_chain_level,
case when cst.business_chain_level in ( 1,2) then 1 else 0 end AS is_dummy_card,
0 AS supp_seq_no,
cst.name,
cst.midname,
cst.surname,
( cst.name || ' ' || cst.midname || ' ' || cst.surname ) as maincardcustomername,
cst.customer_no,
cst.stat_code,
cst.stmt_stat_code,
card.card_level,
card.card_stat_code || '~' || card.card_sub_stat_code cardstatus,
card.emboss_name1,
card.card_type,
card.product_id,
to_char( to_date( case when length( to_char(card.expiry_date) ) < 6 then '190001' else to_char(card.expiry_date) end,'yyyyMM') ,'MM/yyyy') ,
CARD.MAIN_CUSTOMER_NO,
to_char(cst.tc_no),
cst.mother_name,
cst.father_name,
adres.zip_code,
adres.address1,
cst.company_no,
to_char( to_date( to_char(cst.birth_date),'yyyymmdd') ,'dd/MM/yyyy') ,
'' as irtbilgi ,
card.card_branch,
cst.point_collection_type
FROM
OC_CST.CST_CUSTOMER CST,
oc_cst.cst_customer_address_main adres,
OC_CRD.CRD_CARD CARD
WHERE

CARD.CUSTOMER_NO(+) = CST.CUSTOMER_NO
AND CARD.STATUS(+) = 1
AND CST.STATUS(+) = 1
AND cst.banking_customer_no = adres.banking_customer_no(+)
AND cst.post_adress_idx = adres.address_idx(+)
AND CST.MBR_ID = 101
and cst.name like '%ASDF%'

union

SELECT /*+ use_nl(adres CARD) use_nl(@CUST1 [email protected] [email protected]) index(@CUST1 [email protected] IX2_MUSTERI_NAME) use_nl(@CUST2 [email protected] [email protected]) index(@CUST2 [email protected] IX2_CANDIDATE_NAME)*/
'' AS grp_customer_type,
( cst.name || ' ' || cst.midname || ' ' || cst.surname ) as grp_main_customer_info,
card.main_card_no as grp_main_card_info,
card.main_card_no,
card.card_no,
to_char(cst.banking_customer_no),
cst.business_chain_level,
case when cst.business_chain_level in ( 1,2) then 1 else 0 end AS is_dummy_card,
0 AS supp_seq_no,
cst.name,
cst.midname,
cst.surname,
( cst.name || ' ' || cst.midname || ' ' || cst.surname ) as maincardcustomername,
cst.customer_no,
cst.stat_code,
cst.stmt_stat_code,
card.card_level,
card.card_stat_code || '~' || card.card_sub_stat_code cardstatus,
card.emboss_name1,
card.card_type,
card.product_id,
to_char( to_date( case when length( to_char(card.expiry_date) ) < 6 then '190001' else to_char(card.expiry_date) end,'yyyyMM') ,'MM/yyyy') ,
CARD.MAIN_CUSTOMER_NO,
to_char(cst.tc_no),
cst.mother_name,
cst.father_name,
adres.zip_code,
adres.address1,
cst.company_no,
to_char( to_date( to_char(cst.birth_date),'yyyymmdd') ,'dd/MM/yyyy') ,
'' as irtbilgi ,
card.card_branch,
cst.point_collection_type
FROM
OC_CST.CST_CUSTOMER CST,
oc_cst.cst_customer_address_main adres,
OC_CRD.CRD_CARD CARD
WHERE

CARD.CUSTOMER_NO(+) = CST.CUSTOMER_NO
AND CARD.STATUS(+) = 1
AND CST.STATUS(+) = 1
AND cst.banking_customer_no = adres.banking_customer_no(+)
AND cst.post_adress_idx = adres.address_idx(+)
AND CST.MBR_ID = 101
and cst.name2 like '%ASDF%'

Execution Plan2

SELECT STATEMENT, GOAL = ALL_ROWS 16511931 3782374 1380457588
SORT UNIQUE 16511931 3782374 1380457588
UNION-ALL
NESTED LOOPS OUTER 5668526 1305094 498545908
NESTED LOOPS OUTER 2095841 892972 261640796
VIEW OC_CST CST_CUSTOMER 309470 892972 216992196
UNION-ALL
HASH JOIN 286728 847502 91530216
TABLE ACCESS STORAGE FULL OC_CST CST_CUSTOMER_INFO 17674 847502 47460112
MAT_VIEW ACCESS STORAGE FULL OC_MVT SNAP_UAT_T_MUSTERI 263042 1083348 56334096
HASH JOIN 22735 45469 4774245
MAT_VIEW ACCESS STORAGE FULL OC_MVT SNAP_UAT_T_CANDIDATE 5058 27326 1338974
TABLE ACCESS STORAGE FULL OC_CST CST_CUSTOMER_INFO 17674 847502 47460112
NESTED LOOPS
NESTED LOOPS 7 1 111
TABLE ACCESS STORAGE FULL OC_CST CST_CUSTOMER_TEMP 3 1 55
INDEX RANGE SCAN OC_CST IDX_OC_CST_CUSTOMER_NBNKCST_NO 2 2
TABLE ACCESS BY INDEX ROWID OC_CST CST_CUSTOMER_INFO 4 2 112
MAT_VIEW ACCESS BY INDEX ROWID OC_MVT SNAP_T_ADRES 2 1 50
INDEX UNIQUE SCAN OC_MVT T_ADRES_CLIENT_NO2 1 1
TABLE ACCESS BY INDEX ROWID OC_CRD CRD_CARD 4 1 89
INDEX RANGE SCAN OC_CRD IDX_CRD_CARD_05 2 1
FILTER
NESTED LOOPS OUTER 10549034 2477280 881911680
NESTED LOOPS OUTER 3767498 1695006 452566602
VIEW OC_CST CST_CUSTOMER 376674 1695006 367816302
UNION-ALL
HASH JOIN 349626 847502 91530216
TABLE ACCESS STORAGE FULL OC_CST CST_CUSTOMER_INFO 17674 847502 47460112
MAT_VIEW ACCESS STORAGE FULL OC_MVT SNAP_UAT_T_MUSTERI 263557 21667104 1126689408
HASH JOIN 27041 847502 88987710
MAT_VIEW ACCESS STORAGE FULL OC_MVT SNAP_UAT_T_CANDIDATE 5059 546527 26779823
TABLE ACCESS STORAGE FULL OC_CST CST_CUSTOMER_INFO 17674 847502 47460112
NESTED LOOPS
NESTED LOOPS 7 2 222
TABLE ACCESS STORAGE FULL OC_CST CST_CUSTOMER_TEMP 3 1 55
INDEX RANGE SCAN OC_CST IDX_OC_CST_CUSTOMER_NBNKCST_NO 2 2
TABLE ACCESS BY INDEX ROWID OC_CST CST_CUSTOMER_INFO 4 2 112
MAT_VIEW ACCESS BY INDEX ROWID OC_MVT SNAP_T_ADRES 2 1 50
INDEX UNIQUE SCAN OC_MVT T_ADRES_CLIENT_NO2 1 1
TABLE ACCESS BY INDEX ROWID OC_CRD CRD_CARD 4 1 89
INDEX RANGE SCAN OC_CRD IDX_CRD_CARD_05 2 1

Edited by: 983327 on Jun 5, 2013 6:23 AM

Jonathan Lewis

983327 wrote:
I'm sending queries and plans. I hope you can und:)it is really long.

As far as I can tell from what you've supplied the difference between the two pieces is that one checks cst.name and the other checks cst.name2; is this correct ?

Since there are three queries it would be good to see three execution plans. Convention dictates that you use dbms_xplan to generate the plans so that we get a standard format; and if you include the code tags (the word code in curly brackets) before and after the output it will be presented in fixed to preserve space.

As it is, I'd be interested to know what predicate is associated with the FILTER operation neat the top of the second part of the UNION plan.

If you want to return the correct results for your query, you may need to go back to a UNION ALL, and add a predicate to the second half to exclude rows where cst.name is like '%ASDF%' - taking care that you don't lose rows where the name is null.

A first thought is that you may have uncovered an Oracle bug which has resulted in a calculation from the second part of the query being transferred into the calculations for the first part of the query - changing the cardinalities dramatically, hence the execution plan. This MIGHT be something to do with the statistics of name2 - for example, if a large percentage of the customer rows are null in that column. Without predicate information and the individual plan for the second part of the query, though, I wouldn't like to speculate any further.


UPDATE: I've changed my mind about a switch in cardinality across the two parts of the UNION. The cardinalities match up. The interesting thing is that the cost (or, at least, the column I think is the cost) for the bit you showed us has the following figures for SNAP_UAT_T_CANDIDATE:

Single query:

       MAT_VIEW ACCESS BY INDEX ROWID	OC_MVT	SNAP_UAT_T_CANDIDATE	26817	27326	1338974
        INDEX FULL SCAN	OC_SYS	IX2_CANDIDATE_NAME	1354	27326	

UNION query

        MAT_VIEW ACCESS STORAGE FULL  OC_MVT  SNAP_UAT_T_CANDIDATE  5058  27326  1338974

A cost of 5058 for the tablescan and a cost o 26817 for the indexed access path.
At first sight, the tablescan should have been the first choice in both cases.


I think I've got it - you've got a load of hints in the two separate queries. As with most hinted SQL the hinting is probably very bad, but it produces a "good enough" effect when the queries are run individually - even though it forces the optimizer into a couple of INDEX FULL SCAN steps. When you introduce a UNION, there are a load of changes to query block names, and some of your hints are therefore directed to the wrong place and are irrelevant - and you get parts of the plan with lower costs but (presumably, since you're asking) higher run times.



Regards
Jonathan Lewis

Edited by: Jonathan Lewis on Jun 5, 2013 6:06 PM

Edited by: Jonathan Lewis on Jun 5, 2013 6:11 PM

User505978 - Oracle

**At first, thank you so much Jonathan, for your detailed answer and opinions. You asked me for the 3rd execution plan, i und what you mean but you can count it is same with the other individual query's sql_plan.
**yes , the only difference is name and name2 columns but there are indexes for both columnns for both MWs.

I'm aware i added many hints but the cst_customer views include 3 different views and those views are using materalized views. I had to use QB_NAME to give specific hints for each nested views.

Also i tested index full scan and table scan, though full scan shows better costs, index full scan has much more better run times. I think obsolete statics can be the reason for this.