13 Replies Latest reply on Jun 6, 2013 9:33 PM by rp0428

    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?
        • 1. Re: Queries have different plan with UNION
          marco
          Do you really need UNION ? Maybe UNION ALL is enough? FYI: UNION works as distinct.
          • 2. Re: Queries have different plan with UNION
            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
            • 3. Re: Queries have different plan with UNION
              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
              • 4. Re: Queries have different plan with UNION
                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
                • 5. Re: Queries have different plan with UNION
                  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.
                  • 6. Re: Queries have different plan with UNION
                    User505978 - Oracle
                    i tried with union all but it is same, execution plan is changing.
                    • 7. Re: Queries have different plan with UNION
                      SomeoneElse
                      What problem are you actually having?
                      • 8. Re: Queries have different plan with UNION
                        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 info@CUST1 personal@CUST1) index(@CUST1 personal@CUST1 IX2_MUSTERI_NAME) use_nl(@CUST2 info@CUST2 personal@CUST2) index(@CUST2 personal@CUST2 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 info@CUST1 personal@CUST1) index(@CUST1 personal@CUST1 IX2_MUSTERI_NAME) use_nl(@CUST2 info@CUST2 personal@CUST2) index(@CUST2 personal@CUST2 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 info@CUST1 personal@CUST1) index(@CUST1 personal@CUST1 IX2_MUSTERI_NAME) use_nl(@CUST2 info@CUST2 personal@CUST2) index(@CUST2 personal@CUST2 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
                        • 9. Re: Queries have different plan with UNION
                          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
                          • 10. Re: Queries have different plan with UNION
                            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.
                            • 11. Re: Queries have different plan with UNION
                              Jonathan Lewis
                              983327 wrote:
                              **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.
                              Your original question was: "I have two queries which use plan X when I run them separately but do something completely different when I stick them together with a UNION". My point about the hints and the degree to which your hints are correct or not was that the UNION introduces a new level of query blocks, and your hints (or some of them) have therefore (probably) gone out of scope and are no longer addressing the query blocks that they need to address. In other words, the new plan may well be the plan that you would get if you deleted all the hints.

                              Regards
                              Jonathan Lewis
                              • 12. Re: Queries have different plan with UNION
                                User505978 - Oracle
                                I want to ask sth lastly.

                                I know that there is "baseline" with 11g insted of outlines in 10g. I tested baseline on my local database but it is not enough to solve my problem.

                                For example;

                                I create baseline for the following sql before creating index;

                                select name from emp where emp_id=123;

                                i checked it is using full table scan.

                                Then i create an index for the table and when i query same sql, it is not using index according to created baseline. Everything is ok till here.

                                But, when i change my sql like this:

                                select name from emp where emp_id=234;

                                it is using the index. I'm aware that sql is changed and baseline is not referenced for this query.


                                My question is:

                                is there anyway to force this query not to use index with different emp_id?
                                • 13. Re: Queries have different plan with UNION
                                  >
                                  My question is:

                                  is there anyway to force this query not to use index with different emp_id?
                                  >
                                  Since that is a new question you need to create a new thread.