1 2 Previous Next 16 Replies Latest reply: Mar 14, 2009 3:11 PM by max71 RSS

    Hints ignored

    max71
      Hello,

      Can someone help me out with this. I'm running this SQL in 10.2.0.4.


      Table volume:


      TBL1 - 723K currently ( this can go up to 1-2 million )
      TGT2 - 723K currently ( this can go upto 60M )


      Stats are up-to-date on both the tables ( used 100% as estimate% Just to eliminate the stat quality here )


      Have following SQL which involve 2 list partitioned table on the same column PKEY (i.e. Region) and running a merge statement and it's ignoring the hints I specified. Especially to use hash join. How to force the hash join in here?



      Please note that ‘NONE' in "PKEY IN" is a non-existent partition and part of application logic that can't be changed.


      MERGE 
      /*+ FULL(TGT2) USE_HASH(TGT2) PARALLEL(TGT2, 2) pq_distribute(tgt2, broadcast, broadcast) */*
      *INTO TGT2*
      *USING (SELECT /*+ MERGE FULL(TBL1) USE_HASH(TBL1) PARALLEL(TBL1,2) */
      
      *FROM TBL1 TBL1*
      *WHERE* 
      *TBL1.PKEY IN ('CA' , 'NONE','NONE','NONE')) TGT1*
      *--TBL1.PKEY = 'CA' )TGT1*
      *ON ( TGT2.srvc_id = TGT1.srvc_id*
      *AND TGT2.accnt_id = TGT1.accnt_id*
      *AND TGT2.PKEY = TGT1.PKEY*
      *AND TGT2.PKEY in ('CA' , 'NONE','NONE','NONE') )*
      *--AND TGT2.PKEY = 'CA' )*
      *WHEN MATCHED THEN*
      *UPDATE*
      *SET TGT2.code1 = TGT1.code1,*
      *TGT2.code2 = TGT1.code2,* 
      *TGT2.code2_old = TGT1.code2_old,*
      *WHEN NOT MATCHED THEN*
      *INSERT (TGT2.srvc_id, TG2.accnt_id, TGT2.code2, TGT2.code2_old TGT2.PKEY )*
      *VALUES (TGT1.srvc_id, TGT1.accnt_id, TGT1.code2, TGT1.code2_old, TGT1.PKEY )*
      
      
      
      *------------------------------------------------------------------------------------------------------------------------------------------------*
      *| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |*
      *------------------------------------------------------------------------------------------------------------------------------------------------*
      *| 0 | MERGE STATEMENT | | 723K| 433M| 231K (1)| 00:46:17 | | | | | |*
      *| 1 | MERGE | TGT2 | | | | | | | | | |*
      *| 2 | PX COORDINATOR | | | | | | | | | | |*
      *| 3 | PX SEND QC (RANDOM) | :TQ10000 | 723K| 737M| 231K (1)| 00:46:17 | | | Q1,00 | P->S | QC (RAND) |*
      *| 4 | VIEW | | | | | | | | Q1,00 | PCWP | |*
      *| 5 | NESTED LOOPS OUTER | | 723K| 737M| 231K (1)| 00:46:17 | | | Q1,00 | PCWP | |*
      *| 6 | PX BLOCK ITERATOR | | 723K| 369M| 10 (80)| 00:00:01 |KEY(I) |KEY(I) | Q1,00 | PCWC | |*
      *| 7 | TABLE ACCESS FULL | TBL1 | 723K| 369M| 10 (80)| 00:00:01 |KEY(I) |KEY(I) | Q1,00 | PCWP | |*
      *| 8 | VIEW | | 1 | 441 | | | | | Q1,00 | PCWP | |*
      *|* 9 | FILTER | | | | | | | | Q1,00 | PCWP | |
      | 10 | PARTITION LIST INLIST| | 1 | 441 | 12392 (2)| 00:02:29 |KEY(I) |KEY(I) | Q1,00 | PCWP | |
      |* 11 | TABLE ACCESS FULL | TGT2 | 1 | 441 | 12392 (2)| 00:02:29 |KEY(I) |KEY(I) | Q1,00 | PCWP | |
      ------------------------------------------------------------------------------------------------------------------------------------------------
      
      
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
      9 - filter("TBL1"."PKEY"='NONE' OR "TBL1"."PKEY"='CA')
      11 - filter("TGT2"."accnt_id"="TBL1"."accnt_id" AND "TGT2"."srvc_id"="TBL1"."srvc_id" AND 
      "TGT2"."PKEY"="TBL1"."PKEY")
      If I change IN clause with equality in both Main and Subquery as following , it does consider the hints and generate plan with hash join.


      --AND TGT2.PKEY in ('CA' , 'NONE','NONE','NONE') )
      AND TGT2.PKEY = 'CA' )

      -------------------------------------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
      -------------------------------------------------------------------------------------------------------------------------------------------------
      | 0 | MERGE STATEMENT | | 1 | 520 | 12433 (2)| 00:02:30 | | | | | |
      | 1 | MERGE | TGT2 | | | | | | | | | |
      | 2 | PX COORDINATOR | | | | | | | | | | |
      | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 1128 | 12433 (2)| 00:02:30 | | | Q1,02 | P->S | QC (RAND) |
      | 4 | VIEW | | | | | | | | Q1,02 | PCWP | |
      |* 5 | HASH JOIN OUTER | | 1 | 1128 | 12433 (2)| 00:02:30 | | | Q1,02 | PCWP | |
      | 6 | PX RECEIVE | | 1 | 687 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
      | 7 | PX SEND HASH | :TQ10001 | 1 | 687 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH |
      | 8 | PX BLOCK ITERATOR | | 1 | 687 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWC | |
      | 9 | TABLE ACCESS FULL | TBL1 | 1 | 687 | 2 (0)| 00:00:01 | 2 | 2 | Q1,01 | PCWP | |
      | 10 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
      | 11 | PX RECEIVE | | 723K| 368M| 12428 (2)| 00:02:30 | | | Q1,02 | PCWP | |
      | 12 | PX SEND HASH | :TQ10000 | 723K| 368M| 12428 (2)| 00:02:30 | | | | S->P | HASH |
      | 13 | PARTITION LIST SINGLE| | 723K| 368M| 12428 (2)| 00:02:30 | KEY | KEY | | | |
      | 14 | TABLE ACCESS FULL | TGT2 | 723K| 368M| 12428 (2)| 00:02:30 | 2 | 2 | | | |
      -------------------------------------------------------------------------------------------------------------------------------------------------
      
      
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
      5 - access("TGT2"."PKEY"(+)="TBL1"."PKEY" AND "TGT2"."accnt_id"(+)="TBL1"."accnt_id" AND 
      "TGT2"."srvc_id"(+)="TBL1"."srvc_id")
      Edited by: max71 on Feb 28, 2009 4:07 PM
        • 1. Re: Hints ignored
          Randolf Geist
          max71 wrote:
          MERGE  
          /*+    FULL(TG2)  USE_HASH(TG2) PARALLEL(TG2, 2) pq_distribute(tg2, broadcast, broadcast)    */
          INTO  TGT2
          USING (SELECT /*+  MERGE FULL(TB1) USE_HASH(TB1) PARALLEL(TB1,2)    */
          I'm not sure why you think that your hints should work, and may be it's just a typo/posting issue, but none of your objects in the SQL are called TG2 or TB1, so may be you want to try it with TGT2/TBL1
          Stats are up-to-date on both the tables ( used 100% as estimate% Just to eliminate the stat quality here )
          Yet there seem to be several issues possibly related to the statistics:

          1. This table access does have a cost of only 10 (single-block reads), sounds a bit odd for 723K rows
          |   7 |        TABLE ACCESS FULL     | TBL1               |   723K|   369M|    10  (80)| 00:00:01 |KEY(I) |KEY(I) |  Q1,00 | PCWP |            |
          2. This join predicate is estimated to return literally no rows based on global level statistics:
          11 - filter("TGT2"."accnt_id"="TBL1"."accnt_id" AND "TGT2"."srvc_id"="TBL1"."srvc_id" AND 
                        "TGT2"."PKEY"="TBL1"."PKEY")
          3. This partition (presumably holding the "CA" values) seems to be empty according to the partition level statistics:
          |   9 |          TABLE ACCESS FULL    | TBL1               |     1 |   687 |     2   (0)| 00:00:01 |     2 |     2 |  Q1,01 | PCWP |            |
          Questions:

          1. Can you show us the output of the following in SQL*Plus:
          show parameter optimizer
          
          show parameter db_file_multi
          
          show parameter db_block_size
          
          column sname format a20
          column pname format a20
          column pval2 format a20
          
          select
          sname
          , pname
          , pval1
          , pval2
          from
          sys.aux_stats$;
          2. How did you gather the statistics, in particular for the partitions and the global level? I know you've said you've used 100%, but there are more options to the DBMS_STATS calls, in particular the GRANULARITY parameter.

          You might want to read this HOW TO: Post a SQL statement tuning request - template posting along with the accompanying post how to gather further details about the statement execution and the actual row source cardinalities in comparison to the estimates of the optimizer:

          In particular the extended SQL trace and the DBMS_XPLAN.DISPLAY_CURSOR output (when used with the GATHER_PLAN_STATISTICS hint) should help to reveal where the optimizer makes wrong assumptions.

          Regards,
          Randolf

          Oracle related stuff blog:
          http://oracle-randolf.blogspot.com/

          SQLTools++ for Oracle (Open source Oracle GUI for Windows):
          http://www.sqltools-plusplus.org:7676/
          http://sourceforge.net/projects/sqlt-pp/
          • 2. Re: Hints ignored
            max71
            Randolf,

            Good catch on Aliases. It was a typo during the post. I corrected in the origional post ( but now it messed up the formatting :-( )

            There is no workload system statistics.

            Regarding cost issue.. not sure what the issue was but I have re-collected the stats and here are the execution plan with correct costs.

            Execution plan when Partition elimination Predicate uses "="(equality) operator



            --AND TGT2.PKEY   in  ('CA' , 'NONE','NONE','NONE') )*
            AND TGT2.PKEY   = 'CA' )


            --------------------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                    | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
            --------------------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | MERGE STATEMENT              |                    |   723K|  1708M|       | 32581   (1)| 00:06:31 |       |       |        |      |            |
            |   1 |  MERGE                       | TGT2               |       |       |       |            |          |       |       |        |      |            |
            |   2 |   PX COORDINATOR             |                    |       |       |       |            |          |       |       |        |      |            |
            |   3 |    PX SEND QC (RANDOM)       | :TQ10002           |   723K|   736M|       | 32581   (1)| 00:06:31 |       |       |  Q1,02 | P->S | QC (RAND)  |
            |   4 |     VIEW                     |                    |       |       |       |            |          |       |       |  Q1,02 | PCWP |            |
            |*  5 |      HASH JOIN OUTER BUFFERED|                    |   723K|   736M|   189M| 32581   (1)| 00:06:31 |       |       |  Q1,02 | PCWP |            |
            |   6 |       PX RECEIVE             |                    |   723K|   368M|       |  6902   (2)| 00:01:23 |       |       |  Q1,02 | PCWP |            |
            |   7 |        PX SEND HASH          | :TQ10000           |   723K|   368M|       |  6902   (2)| 00:01:23 |       |       |  Q1,00 | P->P | HASH       |
            |   8 |         PX BLOCK ITERATOR    |                    |   723K|   368M|       |  6902   (2)| 00:01:23 |   KEY |   KEY |  Q1,00 | PCWC |            |
            |   9 |          TABLE ACCESS FULL   | TBL1               |   723K|   368M|       |  6902   (2)| 00:01:23 |     2 |     2 |  Q1,00 | PCWP |            |
            |  10 |       PX RECEIVE             |                    |   723K|   368M|       |  6889   (2)| 00:01:23 |       |       |  Q1,02 | PCWP |            |
            |  11 |        PX SEND HASH          | :TQ10001           |   723K|   368M|       |  6889   (2)| 00:01:23 |       |       |  Q1,01 | P->P | HASH       |
            |  12 |         PX BLOCK ITERATOR    |                    |   723K|   368M|       |  6889   (2)| 00:01:23 |   KEY |   KEY |  Q1,01 | PCWC |            |
            |  13 |          TABLE ACCESS FULL   | TGT2               |   723K|   368M|       |  6889   (2)| 00:01:23 |     2 |     2 |  Q1,01 | PCWP |            |
            --------------------------------------------------------------------------------------------------------------------------------------------------------
            
            
            
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
                5 - access("TGT2"."PKEY"(+)="TBL1"."PKEY" AND "TGT2"."accnt_id"(+)="TBL1"."accnt_id" AND 
                          "TGT2"."srvc_id"(+)="TBL1"."srvc_id")
            Execution plan when Partition elimination Predicate uses "IN" operator



            AND TGT2.PKEY   in  ('CA' , 'NONE','NONE','NONE') )
            --  AND TGT2.PKEY   = 'CA' )


              
            
            
            ------------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
            ------------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | MERGE STATEMENT              |                    |   875K|  1708M|   451K  (1)| 01:30:21 |       |       |        |      |            |
            |   1 |  MERGE                       | TGT2               |       |       |            |          |       |       |        |      |            |
            |   2 |   PX COORDINATOR             |                    |       |       |            |          |       |       |        |      |            |
            |   3 |    PX SEND QC (RANDOM)       | :TQ10000           |   875K|   736M|   451K  (1)| 01:30:21 |       |       |  Q1,00 | P->S | QC (RAND)  |
            |   4 |     VIEW                     |                    |       |       |            |          |       |       |  Q1,00 | PCWP |            |
            |   5 |      NESTED LOOPS OUTER      |                    |   875K|   736M|   451K  (1)| 01:30:21 |       |       |  Q1,00 | PCWP |            |
            |   6 |       PX BLOCK ITERATOR      |                    |   875K|   368M|  6904   (2)| 00:01:23 |KEY(I) |KEY(I) |  Q1,00 | PCWC |            |
            |   7 |        TABLE ACCESS FULL     | TBL1               |   875K|   368M|  6904   (2)| 00:01:23 |KEY(I) |KEY(I) |  Q1,00 | PCWP |            |
            |   8 |       VIEW                   |                    |     1 |   441 |            |          |       |       |  Q1,00 | PCWP |            |
            |*  9 |        FILTER                |                    |       |       |            |          |       |       |  Q1,00 | PCWP |            |
            |  10 |         PARTITION LIST INLIST|                    |     1 |   441 |  6869   (2)| 00:01:23 |KEY(I) |KEY(I) |  Q1,00 | PCWP |            |
            |* 11 |          TABLE ACCESS FULL   | TGT2               |     1 |   441 |  6869   (2)| 00:01:23 |KEY(I) |KEY(I) |  Q1,00 | PCWP |            |
            ------------------------------------------------------------------------------------------------------------------------------------------------
             
            
            
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               9 - filter("TBL1"."PKEY"='NONE' OR "TBL1"."PKEY"='CA')
              11 - filter("TGT2"."accnt_id"="TBL1"."accnt_id" AND "TGT2"."srvc_id"="TBL1"."srvc_id" AND 
                          "TGT2"."PKEY"="TBL1"."PKEY")
            I have collected stats with following option.
            *+DBMS_STATS.gather_table_stats (ownname => 'TEST',tabname => 'TGT2',method_opt => 'FOR ALL COLUMNS SIZE 1',estimate_percent => 100,DEGREE => 4,granularity => 'ALL',CASCADE => TRUE);+*
            Answer I'm looking here is 1) why hints USE_HASH as well as MERGE being ignored ? 2) how to fully hint the SQL so it uses HASH join instead of NESTED LOOP.

            Edited by: max71 on Feb 28, 2009 10:19 PM
            • 3. Re: Hints ignored
              Randolf Geist
              max71 wrote:
              There is no workload system statistics.
              OK, and what are your optimizer related settings:
              show parameter optimizer
               
              show parameter db_file_multi
               
              show parameter db_block_size
              Regarding cost issue.. not sure what the issue was but I have re-collected the stats and here are the execution plan with correct costs.
              And what you have you done differently this time? Or has the default statistics gathering job kicked in in the meanwhile (during the night)?
              I have collected stats with following option.
              *+DBMS_STATS.gather_table_stats (ownname => 'TEST',tabname => 'TGT2',method_opt => 'FOR ALL COLUMNS SIZE 1',estimate_percent => 100,DEGREE => 4,granularity => 'ALL',CASCADE => TRUE);+*
              That code snippet collects only for the GLOBAL level, have you gathered for the partition level as well?

              If you want to compute rather than estimate, I suggest to use estimate_percent=>NULL rather than 100. Although it shouldn't make a difference in theory you might hit a bug.
              Answer I'm looking here is 1) why hints USE_HASH as well as MERGE being ignored ? 2) how to fully hint the SQL so it uses HASH join instead of NESTED LOOP.
              I would start with an equivalent SELECT that outer joins TGT2 to see the effects of the joins and hints. You can run a 10053 trace in addition, it will tell you at the end of the trace which of the hints got ignored/applied.

              In 10g you can use the QB_NAME hint to apply query block names. Using this query block names then in your hints increases the likelihood that they are applied to the part of your statement you want it to.

              What do you get if you omit all the hints? Having representative statistics in place you should get a reasonable plan without hints.

              The crucial difference between the two variants of the statements is that one can use the partition level statistics whereas the other one resorts to the global statistics if you're using multiple partition keys.

              By the way, the Broadcast parallel distribution hint is probably not a good idea (and was ignored so far I think since "broadcast,broadcast" is invalid). It would send all data from one table to all slaves. This is usually only applicable if the amount of data would be small to be broadcasted.

              Regards,
              Randolf

              Oracle related stuff blog:
              http://oracle-randolf.blogspot.com/

              SQLTools++ for Oracle (Open source Oracle GUI for Windows):
              http://www.sqltools-plusplus.org:7676/
              http://sourceforge.net/projects/sqlt-pp/
              • 4. Re: Hints ignored
                max71

                here are the parameters.

                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- ---------------
                db_block_size                        integer     8192
                db_file_multiblock_read_count        integer     16
                optimizer_dynamic_sampling           integer     2
                optimizer_features_enable            string      10.2.0.4
                optimizer_index_caching              integer     0
                optimizer_index_cost_adj             integer     100
                optimizer_mode                       string      ALL_ROWS
                optimizer_secure_view_merging        boolean     TRUE
                Reg. stats, I have collected the stats at both GLOBAL and partition level. Also all other partitions except CA does not have data. So essentially GLOBAL stats
                should be equal to PART stats. Shouldn't it?


                One thing I observe between two plans is , view merging is taking place in one plan (one that uses hash_join ) but not in other (NESTED LOOP one).Not sure
                why?


                Reg. pq_distribrute hint, I was playing with it but it uses hash distribution no mater what..


                Reg. 10053, which section of 10053 trace will give me that.. Here is the outline section of it for nested loop plan.

                BEGIN_OUTLINE_DATA
                      IGNORE_OPTIM_EMBEDDED_HINTS
                      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
                      ALL_ROWS
                      OUTLINE_LEAF(@"SEL$2")
                      OUTLINE_LEAF(@"SEL$8976F1A6")
                      MERGE(@"SEL$3")
                      OUTLINE_LEAF(@"SEL$4")
                      OUTLINE_LEAF(@"MRG$1")
                      OUTLINE(@"SEL$1")
                      OUTLINE(@"SEL$3")
                      NO_ACCESS(@"MRG$1" [mailto:%22from$_subquery$_008%22@%22MRG$1]")
                      FULL(@"MRG$1" [mailto:%22TGT2%22@%22MRG$1]")
                      NO_ACCESS(@"MRG$1" [mailto:%22TG1%22@%22MRG$1]")
                      LEADING(@"MRG$1" [mailto:%22from$_subquery$_008%22@%22MRG$1]" [mailto:%22TGT2%22@%22MRG$1]" [mailto:%22TG1%22@%22MRG$1]")
                      USE_MERGE_CARTESIAN(@"MRG$1" [mailto:%22TGT2%22@%22MRG$1]")
                      USE_MERGE_CARTESIAN(@"MRG$1" [mailto:%22TG1%22@%22MRG$1]")
                      PQ_DISTRIBUTE(@"MRG$1" [mailto:%22TGT2%22@%22MRG$1%22BROADCAST] NONE)
                      PQ_DISTRIBUTE(@"MRG$1" [mailto:%22TG1%22@%22MRG$1%22BROADCAST] NONE)
                      FULL(@"SEL$8976F1A6" [mailto:%22TBL1%22@%22SEL$3]")
                      NO_ACCESS(@"SEL$8976F1A6" [mailto:%22from$_subquery$_007%22@%22SEL$1]")
                      LEADING(@"SEL$8976F1A6" [mailto:%22TBL1%22@%22SEL$3]" [mailto:%22from$_subquery$_007%22@%22SEL$1]")
                      USE_NL(@"SEL$8976F1A6" [mailto:%22from$_subquery$_007%22@%22SEL$1]")
                      FULL(@"SEL$4" [mailto:%22TBL1%22@%22SEL$4]")
                      FULL(@"SEL$2" [mailto:%22TGT2%22@%22SEL$2]")
                    END_OUTLINE_DATA
                {code}
                
                
                
                Removing hint uses index on TGT2 with NESTED LOOP. but I would like to use FTS and HASH OUTER JOIN as it's going to perform better when rowcount to be updated is higher. 
                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                • 5. Re: Hints ignored
                  Randolf Geist
                  max71 wrote:

                  Reg. stats, I have collected the stats at both GLOBAL and partition level. Also all other partitions except CA does not have data. So essentially GLOBAL stats
                  should be equal to PART stats. Shouldn't it?
                  And, have you compared the partition level and global statistics? Are they equal? In particular the column level statistics, can you see the same statistics? Or do you end up with significant different number of rows/distinct values etc.?
                  Reg. 10053, which section of 10053 trace will give me that..
                  Search for:
                  Dumping Hints
                  =============
                  Removing hint uses index on TGT2 with NESTED LOOP.
                  Can you post the plan that you get?
                  but I would like to use FTS and HASH OUTER JOIN as it's going to perform better when rowcount to be updated is higher.
                  But that sounds like an odd approach. You're testing with a scenario that doesn't correspond to your actual one I understand, since you're going to have more data in your table, and probably more partitions populated.

                  You should at least load some fake data in your remaining partitions and then fudge the statistics manually using the SET__STATS procedures to emulate your actual scenario.

                  I don't think that it's a good idea to fix now a plan using hints based on a test scenario that doesn't correspond to your actual one.

                  As I've already said I would do the following:

                  1. Compare the partition level and global statistics. It seems to be odd that you get a NESTED LOOP/INDEX plan when using global statistics.

                  2. Use an equivalent query outer joining TGT2 to TBL1. There you have better control using the hints. It should be possible to force a HASH JOIN/FTS plan. Check the corresponding costs. They should be higher than the costs of the NESTED LOOP/INDEX plan chosen. Find out why these costs are higher. If they are less when forcing the HASH JOIN/FTS plan, then this looks like a bug.

                  You might want to test with a serial plan first, the parallelism adds complexity. Start with a simple approach.

                  Regards,
                  Randolf

                  Oracle related stuff blog:
                  http://oracle-randolf.blogspot.com/

                  SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                  http://www.sqltools-plusplus.org:7676/
                  http://sourceforge.net/projects/sqlt-pp/
                  • 6. Re: Hints ignored
                    601585
                    One thing I observe between two plans is , view merging is taking place in one plan (one that uses hash_join ) but not in other (NESTED LOOP one).Not sure
                    why?
                    It seems that view merging not taking place is the key issue here.

                    I was able to reproduce your case in no time. See following:
                    UKJA@ukja102> create table t1(c1 varchar2(10), c2 int, c3 int, c4 int)
                      2  partition by list (c1) (
                      3    partition p1 values ('A'),
                      4    partition p2 values ('B')
                      5  )
                      6  ;
                    
                    Table created.
                    
                    UKJA@ukja102> 
                    UKJA@ukja102> 
                    UKJA@ukja102> create table t2(c1 varchar2(10), c2 int, c3 int, c4 int)
                      2  partition by list (c1) (
                      3    partition p1 values ('A'),
                      4    partition p2 values ('B')
                      5  )
                      6  ;
                    
                    Table created.
                    
                    UKJA@ukja102> 
                    UKJA@ukja102> insert into t1
                      2  --select decode(mod(level,2),0,'A','B'), level, level, level
                      3  select 'A', level, level, level
                      4  from dual connect by level <= 1000
                      5  ;
                    
                    1000 rows created.
                    
                    UKJA@ukja102> 
                    UKJA@ukja102> insert into t2
                      2  --select decode(mod(level,2),0,'A','B'), level, level, level
                      3  select 'A', level, level, level
                      4  from dual connect by level <= 1000
                      5  ;
                    
                    1000 rows created.
                    
                    UKJA@ukja102> 
                    UKJA@ukja102> 
                    UKJA@ukja102> 
                    UKJA@ukja102> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);
                    
                    PL/SQL procedure successfully completed.
                    
                    UKJA@ukja102> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);
                    
                    PL/SQL procedure successfully completed.
                    
                    UKJA@ukja102> 
                    UKJA@ukja102> explain plan for
                      2  merge
                      3  into t2
                      4  using (select * from t1 where c1 = 'A') x
                      5  on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3
                      6        and t2.c1 = 'A')
                      7  when matched then
                      8    update set t2.c4 = x.c4
                      9  when not matched then
                     10    insert (t2.c1, t2.c2, t2.c3, t2.c4)
                     11    values (x.c1, x.c2, x.c3, x.c4)
                     12  ;
                    
                    Explained.
                    
                    UKJA@ukja102> 
                    UKJA@ukja102> select * from table(dbms_xplan.display)
                      2  /
                    
                    PLAN_TABLE_OUTPUT                                                                                   
                    ----------------------------------------------------------------------------------------------------
                    Plan hash value: 2904298926                                                                         
                                                                                                                        
                    -------------------------------------------------------------------------------------------------   
                    | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
                    -------------------------------------------------------------------------------------------------   
                    |   0 | MERGE STATEMENT          |      |  1000 | 72000 |     7  (15)| 00:00:01 |       |       |   
                    |   1 |  MERGE                   | T2   |       |       |            |          |       |       |   
                    |   2 |   VIEW                   |      |       |       |            |          |       |       |   
                    |*  3 |    HASH JOIN OUTER       |      |  1000 | 26000 |     7  (15)| 00:00:01 |       |       |   
                    |   4 |     PARTITION LIST SINGLE|      |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |   
                    |   5 |      TABLE ACCESS FULL   | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |   
                    |   6 |     PARTITION LIST SINGLE|      |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |   
                    |   7 |      TABLE ACCESS FULL   | T2   |  1000 | 13000 |     3   (0)| 00:00:01 |     1 |     1 |   
                    -------------------------------------------------------------------------------------------------   
                                                                                                                        
                    UKJA@ukja102> 
                    UKJA@ukja102> explain plan for
                      2  merge
                      3  into t2
                      4  using (select * from t1 where c1 in ('A', 'X', 'X', 'X')) x
                      5  on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3
                      6        and t2.c1 in ('A', 'X', 'X', 'X'))
                      7  when matched then
                      8    update set t2.c4 = x.c4
                      9  when not matched then
                     10    insert (t2.c1, t2.c2, t2.c3, t2.c4)
                     11    values (x.c1, x.c2, x.c3, x.c4)
                     12  ;
                    
                    Explained.
                    
                    UKJA@ukja102> 
                    UKJA@ukja102> select * from table(dbms_xplan.display)
                      2  /
                    
                    PLAN_TABLE_OUTPUT                                                                                   
                    ----------------------------------------------------------------------------------------------------
                    Plan hash value: 4022167151                                                                         
                                                                                                                        
                    -------------------------------------------------------------------------------------------------   
                    | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
                    -------------------------------------------------------------------------------------------------   
                    |   0 | MERGE STATEMENT          |      |  1000 | 72000 |  3034   (2)| 00:00:37 |       |       |   
                    |   1 |  MERGE                   | T2   |       |       |            |          |       |       |   
                    |   2 |   VIEW                   |      |       |       |            |          |       |       |   
                    |   3 |    NESTED LOOPS OUTER    |      |  1000 | 71000 |  3034   (2)| 00:00:37 |       |       |   
                    |   4 |     PARTITION LIST INLIST|      |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |   
                    |   5 |      TABLE ACCESS FULL   | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |   
                    |   6 |     PARTITION LIST INLIST|      |     1 |    58 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |   
                    |   7 |      VIEW                |      |     1 |    58 |     3   (0)| 00:00:01 |       |       |   
                    |*  8 |       TABLE ACCESS FULL  | T2   |     1 |    13 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |   
                    -------------------------------------------------------------------------------------------------   
                                                                                                                        
                    Looks familiar, isn't it?

                    I took 10053 trace and got following interesting result.

                    - With t2.c1 = 'A' predicate, Oracle transformed the select part to this one:
                    SELECT /*+ NO_MERGE */ 
                    "T2".ROWID "ROWID","T2"."C1" "C1","T2"."C2" "C2",
                    "T2"."C3" "C3","T2"."C4" "C4","T1"."C1" "C1",
                    "T1"."C2" "C2","T1"."C3" "C3","T1"."C4" "C4" 
                    FROM "UKJA"."T1" "T1","UKJA"."T2" "T2" 
                    WHERE "T1"."C3"="T2"."C3"(+) AND 
                    "T1"."C2"="T2"."C2"(+) AND 
                    "T1"."C1"="T2"."C1"(+) AND 
                    "T2"."C1"(+)='A' AND "T1"."C1"='A'
                    - With t2.c1 in ('A', 'X', 'X', 'X') predicate, Oracle transformed the select part to this one:
                    SELECT /*+ NO_MERGE */ 
                    "from$_subquery$_007"."ROWID_4" "ROWID","from$_subquery$_007"."C1_0" "C1",
                    "from$_subquery$_007"."C2_1" "C2","from$_subquery$_007"."C3_2" "C3",
                    "from$_subquery$_007"."C4_3" "C4","T1"."C1" "C1","T1"."C2" "C2",
                    "T1"."C3" "C3","T1"."C4" "C4" 
                    FROM "UKJA"."T1" "T1", 
                    LATERAL( 
                    (SELECT "T2"."C1" "C1_0","T2"."C2" "C2_1","T2"."C3" "C3_2",
                    "T2"."C4" "C4_3","T2".ROWID "ROWID_4" 
                    FROM "UKJA"."T2" "T2" 
                    WHERE "T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" 
                    AND "T1"."C3"="T2"."C3" AND 
                    ("T2"."C1"='A' OR "T2"."C1"='X' OR "T2"."C1"='X')))(+) 
                    "from$_subquery$_007" 
                    WHERE "T1"."C1"='A' OR "T1"."C1"='X' OR "T1"."C1"='X' OR "T1"."C1"='X'
                    It seems that Oracle just denies to merge the view when the target(t2 here) has "OR(in)" predicates. "OR" might mean "complex thing" to Oracle. It's very common for Oracle to deny efficient query transformation with OR predicates.

                    Anyway, for this reason, Oracle couldn't even consider the hash join as an option, thus you have very inefficient nested loops join.

                    In your case, is it possible to remove the redundant TGT2.PKEY in ('CA' , 'NONE','NONE','NONE') ) predicate? Without it, Oracle would successfully merge the view and generates efficient plan like following:
                    UKJA@ukja102> explain plan for
                      2  merge
                      3  into t2
                      4  using (select * from t1 where c1 in ('A', 'X', 'X', 'X')) x
                      5  on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3)
                      6  when matched then
                      7    update set t2.c4 = x.c4
                      8  when not matched then
                      9    insert (t2.c1, t2.c2, t2.c3, t2.c4)
                     10    values (x.c1, x.c2, x.c3, x.c4)
                     11  ;
                    
                    Explained.
                    
                    UKJA@ukja102>
                    UKJA@ukja102> @plan
                    
                    PLAN_TABLE_OUTPUT
                    ------------------------------------------------------------------------------------------------
                    
                    Plan hash value: 1390282189
                    
                    ------------------------------------------------------------------------------------------------
                    | Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                    ------------------------------------------------------------------------------------------------
                    |   0 | MERGE STATEMENT         |      |  1000 | 72000 |     7  (15)| 00:00:01 |       |       |
                    |   1 |  MERGE                  | T2   |       |       |            |          |       |       |
                    |   2 |   VIEW                  |      |       |       |            |          |       |       |
                    |   3 |    PARTITION LIST INLIST|      |  1000 | 26000 |     7  (15)| 00:00:01 |KEY(I) |KEY(I) |
                    |*  4 |     HASH JOIN OUTER     |      |  1000 | 26000 |     7  (15)| 00:00:01 |       |       |
                    |   5 |      TABLE ACCESS FULL  | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                    |   6 |      TABLE ACCESS FULL  | T2   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                    ------------------------------------------------------------------------------------------------
                    ================================
                    Dion Cho - Oracle Performance Storyteller

                    http://dioncho.wordpress.com (english)
                    http://ukja.tistory.com (korean)
                    ================================
                    • 7. Re: Hints ignored
                      max71

                      Global level and partition level stats are same for all joined columns. I checked DISTVAL and DENSITY. NUMROWS is also same at both levels.



                      Test envrioment is so small that complete dataset can't be loaded so testing with limited data. That's reason I want to fully hint the SQL.


                      I also tried your suggestion of trying SELECT with outer join and it does not exhibit this behaviour. i.e. hints being followed and hash join being used.
                      So I'm still scratching my head as to why hints not being used. I also tried not using parallel hints but it did not make any difference in the plan. The moment I use IN list, it ignore my hint of HASH join.



                      Here is the plan with no hints (INDEX Plan )

                      ------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                               | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                      ------------------------------------------------------------------------------------------------------------------------------
                      |   0 | MERGE STATEMENT                         |                    |   723K|  1708M|   204K  (1)| 00:40:57 |       |       |
                      |   1 |  MERGE                                  | TGT2               |       |       |            |          |       |       |
                      |   2 |   VIEW                                  |                    |       |       |            |          |       |       |
                      |   3 |    NESTED LOOPS OUTER                   |                    |   723K|   736M|   204K  (1)| 00:40:57 |       |       |
                      |   4 |     PARTITION LIST INLIST               |                    |   723K|   368M| 12471   (2)| 00:02:30 |KEY(I) |KEY(I) |
                      |   5 |      TABLE ACCESS FULL                  | TBL1               |   723K|   368M| 12471   (2)| 00:02:30 |KEY(I) |KEY(I) |
                      |   6 |     VIEW                                |                    |     1 |   441 |     0   (0)| 00:00:01 |       |       |
                      |*  7 |      FILTER                             |                    |       |       |            |          |       |       |
                      |   8 |       PARTITION LIST INLIST             |                    |     1 |   441 |     5   (0)| 00:00:01 |KEY(I) |KEY(I) |
                      |   9 |        TABLE ACCESS BY LOCAL INDEX ROWID| TGT2               |     1 |   441 |     5   (0)| 00:00:01 |KEY(I) |KEY(I) |
                      |* 10 |         INDEX UNIQUE SCAN               | TGT2_I1            |     1 |       |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
                      ------------------------------------------------------------------------------------------------------------------------------
                       
                      
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         7 - filter("TBL1"."PKEY"='NONE' OR "TBL1"."PKEY"='CA')
                        10 - access("TGT2"."ACCT_ID"="TBL1"."ACCT_ID" AND "TGT2"."SRVC_ID"="TBL1"."SRVC_ID" AND 
                                    "TGT2"."PKEY"="TBL1"."PKEY")
                      Here is dump for hints. USE_HASH(TGT2) has used=1 , means it's used. Isn't it??

                      Dumping Hints
                      =============
                        atom_hint=(@=700000030ba24c0 err=0 resol=1 used=1 token=177 org=1 lvl=3 txt=PARALLEL ("TGT2",2) )
                        atom_hint=(@=700000030ba2708 err=0 resol=1 used=1 token=922 org=1 lvl=3 txt=USE_HASH ("TGT2") )
                        atom_hint=(@=700000030ba2950 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("TGT2") )
                        atom_hint=(@=700000030125be8 err=0 resol=1 used=1 token=177 org=1 lvl=3 txt=PARALLEL ("TGT2",2) )
                        atom_hint=(@=700000030125c88 err=0 resol=1 used=1 token=922 org=1 lvl=3 txt=USE_HASH ("TGT2") )
                        atom_hint=(@=7000000308f0670 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("TG2") )
                        atom_hint=(@=700000030ba1038 err=0 resol=1 used=0 token=922 org=1 lvl=3 txt=USE_HASH ("TBL1") )
                        atom_hint=(@=700000030ba0df0 err=0 resol=1 used=1 token=177 org=1 lvl=3 txt=PARALLEL ("TBL1",2) )
                        atom_hint=(@=700000030ba1330 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("TBL1") )
                      • 8. Re: Hints ignored
                        max71

                        Thanks Dion for taking time to reproduce this at your end. Yes! view merging seems to be the culprit..



                        Anyway, for this reason, Oracle couldn't even consider the hash join as an option, thus you have very inefficient nested loops join.
                        Hmm.. Does that mean HASH join can't be done whenever there is a OR conditions?


                        In your case, is it possible to remove the redundant TGT2.PKEY in ('CA' , 'NONE','NONE','NONE') ) predicate? Without it, Oracle would successfully merge the view and generates efficient plan like following:
                        No! It's not possible.. In fact , most time it processes more than one partitions.. so PKEY can be ( 'CA', 'NV' ) as well. Even in this case HASH joins are ignored.

                        Also why there is NO_MERGE hint gets appended in the origional SQL? Also did you try with MERGE hint? Why it gets ignored ? Is there a way to resolve this issue ?

                        Thanks





                        • 9. Re: Hints ignored
                          601585
                          So I'm still scratching my head as to why hints not being used. I also tried not using parallel hints but it did not make any difference in the plan. The moment I use IN list, it ignore my hint of HASH join.
                          What I've found from 10053 trace are:

                          1. In List means OR predicates in your statement
                          2. Oracle denied merging your (internal) view(select on target table t2) due to the complex OR predicates.
                          3. Because view merge was disabled, Oracle could not consider hash join as an valid method.

                          Pleases, take a look at my previous comment.

                          ================================
                          Dion Cho - Oracle Performance Storyteller

                          http://dioncho.wordpress.com (english)
                          http://ukja.tistory.com (korean)
                          ================================
                          • 10. Re: Hints ignored
                            601585
                            Oops! I replied too quickly. :)
                            Anyway, for this reason, Oracle couldn't even consider the hash join as an option, thus you have very inefficient nested loops join.
                            Hmm.. Does that mean HASH join can't be done whenever there is a OR conditions?
                            No, not always. In your case, Oracle transformed the internal select on table t2 as following.
                            SELECT /*+ NO_MERGE */ 
                            "from$_subquery$_007"."ROWID_4" "ROWID","from$_subquery$_007"."C1_0" "C1",
                            "from$_subquery$_007"."C2_1" "C2","from$_subquery$_007"."C3_2" "C3",
                            "from$_subquery$_007"."C4_3" "C4","T1"."C1" "C1","T1"."C2" "C2",
                            "T1"."C3" "C3","T1"."C4" "C4" 
                            FROM "UKJA"."T1" "T1", 
                            LATERAL( 
                            (SELECT "T2"."C1" "C1_0","T2"."C2" "C2_1","T2"."C3" "C3_2",
                            "T2"."C4" "C4_3","T2".ROWID "ROWID_4" 
                            FROM "UKJA"."T2" "T2" 
                            WHERE "T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" 
                            AND "T1"."C3"="T2"."C3" AND 
                            ("T2"."C1"='A' OR "T2"."C1"='X' OR "T2"."C1"='X')))(+) 
                            "from$_subquery$_007" 
                            WHERE "T1"."C1"='A' OR "T1"."C1"='X' OR "T1"."C1"='X' OR "T1"."C1"='X'
                            When above statement is optimized without further transformation, there would be no choice but to nested loops outer join because of the correlated predicates. That's what I mean.
                            Also why there is NO_MERGE hint gets appended in the origional SQL? Also did you try with MERGE hint? Why it gets ignored ? Is there a way to resolve this issue ?
                            NO_MERGE hint is appended after the internal select query was transformed. So it would have no effects on the entire plan. I've tried merge hints but with no success. I think that Oracle's internal limitation disables any merging.

                            With something meaningful found, I'd hit this post again.


                            ================================
                            Dion Cho - Oracle Performance Storyteller

                            http://dioncho.wordpress.com (english)
                            http://ukja.tistory.com (korean)
                            ================================
                            • 11. Re: Hints ignored
                              Randolf Geist
                              max71 wrote:

                              Test envrioment is so small that complete dataset can't be loaded so testing with limited data. That's reason I want to fully hint the SQL.
                              I still question that approach, and refer to my previous post. At least try to emulate your actual setup in your test environment. Hinting statements should be your last resort in case you know something about your data the optimizer simply can't recognize.
                              I also tried your suggestion of trying SELECT with outer join and it does not exhibit this behaviour. i.e. hints being followed and hash join being used.
                              So I'm still scratching my head as to why hints not being used. I also tried not using parallel hints but it did not make any difference in the plan. The moment I use IN list, it ignore my hint of HASH join.
                              Dion, thanks for that great test case. I was wondering all the time about this particular part of the NESTED LOOP plans:
                              |   6 |     VIEW                                |                    |     1 |   441 |     0   (0)| 00:00:01 |       |       |
                              |*  7 |      FILTER                             |                    |       |       |            |          |       |       |
                              |   8 |       PARTITION LIST INLIST             |                    |     1 |   441 |     5   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |   9 |        TABLE ACCESS BY LOCAL INDEX ROWID| TGT2               |     1 |   441 |     5   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |* 10 |         INDEX UNIQUE SCAN               | TGT2_I1            |     1 |       |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                              7 - filter("TBL1"."PKEY"='NONE' OR "TBL1"."PKEY"='CA')
                              Why is the FILTER expression on TBL1 applied in the inner part of the NESTED LOOP which accesses TGT2? That looks odd.

                              Dion: I wonder why your MERGE NESTED LOOP EXPLAIN PLAN doesn't show that filter step? If I run your test case on 10.2.0.4 I get the following plan:
                              SQL> 
                              SQL> explain plan for
                                2  merge
                                3  into t2
                                4  using (select * from t1 where c1 in ('A', 'X', 'X', 'X')) x
                                5  on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3
                                6             and t2.c1 in ('A', 'X', 'X', 'X'))
                                7  when matched then
                                8    update set t2.c4 = x.c4
                                9  when not matched then
                               10    insert (t2.c1, t2.c2, t2.c3, t2.c4)
                               11    values (x.c1, x.c2, x.c3, x.c4)
                               12  ;
                              
                              Explained.
                              
                              SQL> 
                              SQL> select * from table(dbms_xplan.display);
                              
                              PLAN_TABLE_OUTPUT
                              ----------------------------------------------------------------------------------------------------------------------------------
                              Plan hash value: 2160114767
                              
                              ---------------------------------------------------------------------------------------------------
                              | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                              ---------------------------------------------------------------------------------------------------
                              |   0 | MERGE STATEMENT            |      |  1000 | 72000 |  3027   (1)| 00:00:37 |       |       |
                              |   1 |  MERGE                     | T2   |       |       |            |          |       |       |
                              |   2 |   VIEW                     |      |       |       |            |          |       |       |
                              |   3 |    NESTED LOOPS OUTER      |      |  1000 | 71000 |  3027   (1)| 00:00:37 |       |       |
                              |   4 |     PARTITION LIST INLIST  |      |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |   5 |      TABLE ACCESS FULL     | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |   6 |     VIEW                   |      |     1 |    58 |     3   (0)| 00:00:01 |       |       |
                              |*  7 |      FILTER                |      |       |       |            |          |       |       |
                              |   8 |       PARTITION LIST INLIST|      |     1 |    13 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |*  9 |        TABLE ACCESS FULL   | T2   |     1 |    13 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              ---------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 7 - filter("T1"."C1"='A' OR "T1"."C1"='X')
                                 9 - filter("T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3" AND "T1"."C1"="T2"."C1")
                              
                              22 rows selected.
                              Do you see the FILTER predicate for operation id 7?

                              Using Dion's test case I can reproduce the issue using a simple SELECT:
                              select
                                       *
                              from
                                     (
                                        select
                                                 *
                                        from
                                                 t1
                                        where
                                                 c1 in ('A', 'X', 'X', 'X')
                                     ) x
                              left outer join
                                       t2
                              on
                                       x.c1 = t2.c1
                              and      x.c2 = t2.c2
                              and      x.c3 = t2.c3
                              and      t2.c1 in ('A', 'X', 'X', 'X');
                              which gives me this plan:
                              Plan hash value: 3431925742
                               
                              -------------------------------------------------------------------------------------------------
                              | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                              -------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT         |      |  1000 | 59000 |  3027   (1)| 00:00:37 |       |       |
                              |   1 |  NESTED LOOPS OUTER      |      |  1000 | 59000 |  3027   (1)| 00:00:37 |       |       |
                              |   2 |   PARTITION LIST INLIST  |      |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |   3 |    TABLE ACCESS FULL     | T1   |  1000 | 13000 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |   4 |   VIEW                   |      |     1 |    46 |     3   (0)| 00:00:01 |       |       |
                              |*  5 |    FILTER                |      |       |       |            |          |       |       |
                              |   6 |     PARTITION LIST INLIST|      |     1 |    13 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              |*  7 |      TABLE ACCESS FULL   | T2   |     1 |    13 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
                              -------------------------------------------------------------------------------------------------
                               
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                               
                                 5 - filter("T1"."C1"='A' OR "T1"."C1"='X')
                                 7 - filter("T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3" AND "T1"."C1"="T2"."C1")
                              To the OP: I can't force a hash join using hints in this SELECT. So you might want to post your query that you've tried that allows you to use a hash join.

                              The reason why there is no view merging taking place becomes obvious if you try to convert this statement to the Oracle outer join syntax:
                              select
                                       *
                              from
                                     (
                                       select
                                                *
                                       from
                                                t1
                                       where
                                                c1 in ('A', 'X', 'X', 'X')
                                     ) x
                                     , t2
                              where
                                       x.c1 = t2.c1 (+)
                              and      x.c2 = t2.c2 (+)
                              and      x.c3 = t2.c3 (+)
                              and      t2.c1 (+) in ('A', 'X', 'X', 'X');
                              This raises error: ORA-01719: outer join operator (+) not allowed in operand of OR or IN

                              Therefore the OR condition can't be outer joined natively, therefore the LATERAL view construct is required which can't be merged.

                              For more information, see: http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

                              But the point is not the view merging not taking place, but the hash join not supporting the lateral view construct.

                              The partitioning seems to make things a bit more complicated. If you repeat Dion's test case and use simple tables without partitioning, you still have the same issue. The outer join containing the OR condition can't use a hash join operation, which looks like a present limitation.

                              In case of a query you could help yourself by re-writing it to something like that:
                              select
                                       *
                              from
                                     (
                                        select
                                                 *
                                        from
                                                 t1
                                        where
                                                 c1 in ('A', 'X', 'X', 'X')
                                     ) x
                              left outer join
                                     (
                                        select
                                                 *
                                        from
                                                 t2
                                        where
                                                 c1 in ('A', 'X', 'X', 'X')
                                     ) t2
                              on
                                       x.c1 = t2.c1
                              and      x.c2 = t2.c2
                              and      x.c3 = t2.c3;
                              if you want the explicit partition pruning to take place on both tables. But you obviously can't do that in case of a MERGE statement, because it doesn't support an inline view, so the following is not possible:
                              merge
                              into (select * from t2 where c1 in ('A', 'X', 'X', 'X')) t2
                              using (select * from t1 where c1 in ('A', 'X', 'X', 'X')) x
                              on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3)
                              when matched then
                                update set t2.c4 = x.c4
                              when not matched then
                                insert (t2.c1, t2.c2, t2.c3, t2.c4)
                                values (x.c1, x.c2, x.c3, x.c4);
                              But the following works (but doesn't help in your particular case):
                              create or replace view y
                              as
                              select * from t2 where c1 in ('A', 'X', 'X', 'X')
                              
                              merge
                              into y t2
                              using (select * from t1 where c1 in ('A', 'X', 'X', 'X')) x
                              on (x.c1 = t2.c1 and x.c2 = t2.c2 and x.c3 = t2.c3)
                              when matched then
                                update set t2.c4 = x.c4
                              when not matched then
                                insert (t2.c1, t2.c2, t2.c3, t2.c4)
                                values (x.c1, x.c2, x.c3, x.c4);
                              This uses a hash join operation.

                              As Dion has already pointed out, it all boils down to the IN/OR predicate applied to the T2/TGT2 table as part of the outer join operation.

                              Regards,
                              Randolf

                              Oracle related stuff blog:
                              http://oracle-randolf.blogspot.com/

                              SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                              http://www.sqltools-plusplus.org:7676/
                              http://sourceforge.net/projects/sqlt-pp/
                              • 12. Re: Hints ignored
                                601585
                                Why is the FILTER expression on TBL1 applied in the inner part of the NESTED LOOP which accesses TGT2? That looks odd.

                                ...
                                Do you see the FILTER predicate for operation id 7?
                                It also looks odd to me. In 10.2.0.1, I see no FILTER predicate. In 11.1.0.6, yes, I see the FILTER predicate. Maybe some minor changes at the later version of 10gR2.


                                For more information, see: http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

                                But the point is not the view merging not taking place, but the hash join not supporting the lateral view construct.
                                Exactly. Thanks for pointing it out.


                                ================================
                                Dion Cho - Oracle Performance Storyteller

                                http://dioncho.wordpress.com (english)
                                http://ukja.tistory.com (korean)
                                ================================
                                • 13. Re: Hints ignored
                                  max71

                                  No, not always. In your case, Oracle transformed the internal select on table t2 as following.
                                  Is there a way to prevent this Transformation??

                                  • 14. Re: Hints ignored
                                    max71
                                    {noformat}
                                    select
                                    *from
                                    (
                                    select
                                    *
                                    from
                                    t1
                                    where
                                    c1 in ('A', 'X', 'X', 'X')
                                    ) x
                                    , t2
                                    where
                                    x.c1 = t2.c1 (+)
                                    and x.c2 = t2.c2 (+)
                                    and x.c3 = t2.c3 (+)
                                    --and t2.c1 (+) in ('A', 'X', 'X', 'X');
                                    How about if I comment last line? Shouldn't it work??

                                    The outer join containing the OR condition can't use a hash join operation, which looks like a present limitation.
                                    Is this limitation legitimate or this could be a bug??


                                    Does that mean you can't use MERGE statement with hash join (similar selects ) when there are multiple partitioned involved (i.e. IN Clause )


                                    {noformat}

                                    Edited by: max71 on Mar 3, 2009 5:18 AM
                                    1 2 Previous Next