Forum Stats

  • 3,853,765 Users
  • 2,264,266 Discussions
  • 7,905,444 Comments

Discussions

Categories

Queries have different plan with UNION

User505978 - Oracle
User505978 - Oracle Member Posts: 92
edited Jun 6, 2013 5:33PM in SQL & PL/SQL
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?
· Share on TwitterShare on Facebook
«1

Answers

  • marco
    marco Member Posts: 338
    Do you really need UNION ? Maybe UNION ALL is enough? FYI: UNION works as distinct.
    · Share on TwitterShare on Facebook
  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy
    first point use IN (1,2) in a single query and avoid UNION
    second if you want to use UNION, use UNION ALL
    · Share on TwitterShare on Facebook
  • Santhosh T
    Santhosh T Member Posts: 421 Bronze Badge
    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
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond
    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
    · Share on TwitterShare on Facebook
  • User505978 - Oracle
    User505978 - Oracle Member Posts: 92
    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.
    · Share on TwitterShare on Facebook
  • User505978 - Oracle
    User505978 - Oracle Member Posts: 92
    i tried with union all but it is same, execution plan is changing.
    · Share on TwitterShare on Facebook
  • SomeoneElse
    SomeoneElse Member Posts: 14,867 Silver Crown
    What problem are you actually having?
    · Share on TwitterShare on Facebook
  • User505978 - Oracle
    User505978 - Oracle Member Posts: 92
    edited Jun 5, 2013 9:23AM
    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
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond
    edited Jun 5, 2013 1:13PM
    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
    · Share on TwitterShare on Facebook
  • User505978 - Oracle
    User505978 - Oracle Member Posts: 92
    **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.
    · Share on TwitterShare on Facebook
This discussion has been closed.