1 2 Previous Next 19 Replies Latest reply on Mar 26, 2016 9:55 AM by Andrew Sayer

    What is the best index for the below update query?

    3131275

      I am new to PL/SQL I may have created a wrong Index on both the tables. Please correct me if am wrong!

       

      For Some reason CBO is not using index( IDX_REF_UHC_COS_PHYS_CLMS_07 ) on table REF_UHC_COS_PHYS_CLMS

       

      I believe I may have created a wrong index for the query. Please provide some guidance on creating the best index for the below query.

       

      P.S - I tried gathering the index stats and then saw the explain plan.

       

      Thank you for your time.

       

      SQL> Select * from v$version;

       

      BANNER

      --------------------------------------------------------------------------------

      Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

      PL/SQL Release 11.2.0.4.0 - Production

      CORE 11.2.0.4.0 Production

      TNS for Linux: Version 11.2.0.4.0 - Production

      NLSRTL Version 11.2.0.4.0 - Production

       

       

      explain plan for

        Update /*+ index(h IDX_REF_UHC_COS_PHYS_CLMS_07 )*/ ref_uhc_cos_phys_clms h

                Set h.Claim_Status = 'ABC'

                Where Claim_Status in ('XYZ','LMO')

                and h.Clm_Stat_Dt is null

                and not exists (Select 1

                                From REF_UHC_COSMOS_MEMBER M

                                where M.MBR_SYS_ID = H.Mbr_Sys_Id

                                );

       

       

       

      PLAN_TABLE_OUTPUT

      Plan hash value: 3719384626

       

      --------------------------------------------------------------------------------------------------------

      | Id  | Operation              | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

      --------------------------------------------------------------------------------------------------------

      |   0 | UPDATE STATEMENT       |                       |    37M|  1379M|       |    19M  (1)| 63:45:24 |

      |   1 |  UPDATE                | REF_UHC_COS_PHYS_CLMS |       |       |       |            |          |

      |*  2 |   HASH JOIN ANTI       |                       |    37M|  1379M|  2525M|    19M  (1)| 63:45:24 |

      |*  3 |    TABLE ACCESS FULL   | REF_UHC_COS_PHYS_CLMS |    60M|  1837M|       |    18M  (1)| 62:18:18 |

      |   4 |    INDEX FAST FULL SCAN| UHC_COS_MBR_IDX02     |   145M|   969M|       |   178K  (1)| 00:35:44 |

      --------------------------------------------------------------------------------------------------------

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

         2 - access("M"."MBR_SYS_ID"="H"."MBR_SYS_ID")

         3 - filter(("CLAIM_STATUS"='LMO' OR "CLAIM_STATUS"='XYZ') AND "H"."CLM_STAT_DT" IS NULL)

       

       

      Index on REF_UHC_COSMOS_MEMBER

       

      create index UHC_COS_MBR_IDX02 on REF_UHC_COSMOS_MEMBER (MBR_SYS_ID, FST_NM)

        tablespace RAWINDX

        pctfree 10

        initrans 2

        maxtrans 255

        storage

        (

          initial 64K

          next 1M

          minextents 1

          maxextents unlimited

        );

       

       

      Index on ref_uhc_cos_phys_clms

       

      create index IDX_REF_UHC_COS_PHYS_CLMS_07 on REF_UHC_COS_PHYS_CLMS (CASE  WHEN CLM_STAT_DT IS NULL THEN CLAIM_STATUS END, CASE  WHEN CLM_STAT_DT IS NULL THEN MBR_SYS_ID END, CLM_STAT_DT)

        tablespace RAWINDX

        pctfree 10

        initrans 2

        maxtrans 255

        storage

        (

          initial 64K

          next 1M

          minextents 1

          maxextents unlimited

        );

        • 1. Re: What is the best index for the below update query?
          BluShadow

          Hard to tell with all that unformatted code, however it looks like you've created a function based index, yet the code in that function based index doesn't match any of the conditions in the update statement, so of course the CBO will not choose to use it.

           

          Please read the two thread listed in the FAQ: Re: 3. How to  improve the performance of my query? / My query is running slow.

          1 person found this helpful
          • 2. Re: What is the best index for the below update query?
            Solomon Yakobson

            We don't know your data. You already have index on REF_UHC_COSMOS_MEMBER(Mbr_Sys_Id). I would look into adding index on REF_UHC_COS_PHYS_CLMS(Claim_Status,Clm_Stat_Dt,Mbr_Sys_Id).

             

            SY.

            1 person found this helpful
            • 3. Re: What is the best index for the below update query?
              John Brady - UK

              The Optimizer is estimating 60M rows will match the 2 main filter conditions. We don't know how many rows in total there are in this table, but 60 million might be a high percentage of that.

               

              The other table is showing as having 145M rows in it. For a join to a 60M data set an index full scan and a hash join make sense from a cost and efficency perspective. A nested loop would be far more expensive.

               

              I'd recommend the same index as Solomon suggests.

               

              John Brady

              1 person found this helpful
              • 4. Re: What is the best index for the below update query?
                Andrew Sayer

                What makes you think that index was going to speed things up? For starters it's a function based index that you don't reference atall.

                 

                How big is this table? Oracle has costed it as 18 million and it thinks you're returning 60 m rows out of it, that's a cost of about 3 per row. Does that add up to what you think it should be?

                 

                Is this a statement that you will run once and forget about? Does it really matter how long it takes?

                 

                Your hint is being "ignored" because it is not valid for Oracle to use the index

                Try rewriting: (that is not to say that using the index is faster than the full table scan, you've not given any information to suggest this)

                 

                Update /*+ index(h IDX_REF_UHC_COS_PHYS_CLMS_07 )*/ ref_uhc_cos_phys_clms h
                          Set h.Claim_Status = 'ABC'
                          Where CASE  WHEN CLM_STAT_DT IS NULL THEN CLAIM_STATUS END in ('XYZ','LMO')
                          and not exists (Select 1
                                          From REF_UHC_COSMOS_MEMBER M
                                          where M.MBR_SYS_ID = CASE  WHEN H.CLM_STAT_DT IS NULL THEN H.MBR_SYS_ID END
                                          )
                

                 

                Note how I'm using the expressions within the function based index to search for the data. I'm not quite sure what the last column on the index was for but if it was made specifically for this plan it can probably be dropped.

                1 person found this helpful
                • 5. Re: What is the best index for the below update query?
                  Chaudhary47

                  Hi 3131275,

                   

                  First of all please read about function based indexes. The index IDX_REF_UHC_COS_PHYS_CLMS_07 will never gonna use in your query.


                  Please create a composite index on CLAIM STATUS and CLM STAT DT columns and create Histogram on CLAIM STATUS as well if there is nonuniform distribution of data.


                  Thanks & Regards,

                  Chaudhary47

                  1 person found this helpful
                  • 6. Re: What is the best index for the below update query?
                    Nimish Garg

                    Indexes mostly depends on data. But my suggestion is to create index on and test

                    1) ref_uhc_cos_phys_clms(Claim_Status,Clm_Stat_Dt)

                    2) REF_UHC_COSMOS_MEMBER(MBR_SYS_ID)

                    • 7. Re: What is the best index for the below update query?
                      Solomon Yakobson

                      Nimish Garg wrote:

                       

                      But my suggestion is to create index on and test

                      1) ref_uhc_cos_phys_clms(Claim_Status,Clm_Stat_Dt)

                      2) REF_UHC_COSMOS_MEMBER(MBR_SYS_ID)

                      Index on REF_UHC_COSMOS_MEMBER(MBR_SYS_ID) already exists. And index on ref_uhc_cos_phys_clms(Claim_Status,Clm_Stat_Dt) is sub-optimal. It would mean index 1) range scan for validating Claim_Status in ('XYZ','LMO') and h.Clm_Stat_Dt is null, but then table access by rowid to get MBR_SYS_ID and using index 2) for validating NOT EXISTS. Index I suggested on ref_uhc_cos_phys_clms(Claim_Status,Clm_Stat_Dt,MBR_SYS_ID) would eliminate table access by rowid for validating NOT EXISTS.

                       

                      SY.

                      • 8. Re: What is the best index for the below update query?
                        3131275

                        Hi Brady,

                         

                        Please find the details below.

                         

                        I have formatted the code for better understanding.

                         

                        SQL> EXPLAIN PLAN FOR

                          2         UPDATE REF_UHC_COS_PHYS_CLMS H

                          3         SET H.Claim_Status = 'ABC'

                          4         WHERE Claim_Status IN ('XYZ','LMO')

                          5         AND H.Clm_Stat_Dt IS NULL

                          6         AND NOT EXISTS (SELECT 1

                          7                                       FROM REF_UHC_COSMOS_MEMBER M

                          8                                       WHERE M.Mbr_Sys_Id = H.Mbr_Sys_Id

                          9                                       );

                         

                        Explained.

                         

                         

                        PLAN_TABLE_OUTPUT

                        ---------------------------------------------------------------------------------------------------------------------------------------------------

                        Plan hash value: 3719384626

                        ---------------------------------------------------------------------------------------------------------------------------------------------------

                        | Id  | Operation                             | Name                                       | Rows  | Bytes     | TempSpc|

                        ----------------------------------------------------------------------------------------------------------------------------------------------------

                        |   0 | UPDATE STATEMENT        |                                                 |    37M  |  1379M  |             |

                        |   1 |  UPDATE                            | REF_UHC_COS_PHYS_CLMS |             |             |             |

                        |*  2 |   HASH JOIN ANTI             |                                                  |    37M  |  1379M  |  2525M |

                        |*  3 |    TABLE ACCESS FULL     | REF_UHC_COS_PHYS_CLMS |    60M  |  1837M  |             |

                        |   4 |    INDEX FAST FULL SCAN| UHC_COS_MBR_IDX02            |   145M |   969M  |             |

                        ---------------------------------------------------------------------------------------------------------------------------------------------------

                        Predicate Information (identified by operation id):

                        -----------------------------------------------------------------------------------------------------------------------------------------------------

                           2 - access("M"."MBR_SYS_ID"="H"."MBR_SYS_ID")

                           3 - filter(("CLAIM_STATUS"='LMO' OR "CLAIM_STATUS"='XYZ') AND "H"."CLM_STAT_D

                         

                        17 rows selected

                         

                        SELECT Table_Name,Num_Rows

                        FROM USER_TABLES

                        WHERE Table_Name='REF_UHC_COS_PHYS_CLMS';

                         

                        TABLE_NAMENUM_ROWS
                        REF_UHC_COS_PHYS_CLMS589053467

                         

                        SELECT Table_Name,Num_Rows

                        FROM USER_TABLES

                        WHERE Table_Name='REF_UHC_COSMOS_MEMBER';

                         

                        TABLE_NAMENUM_ROWS
                        REF_UHC_COSMOS_MEMBER145223240

                         

                        Also, am yet to create the Index you suggested since we have very less resources I need some time. Once the load on server is low I would be creating the Index. As this process goes for a FTS it takes a lot of time to create one.

                         

                        Thanks for you help!

                        • 9. Re: What is the best index for the below update query?
                          Andrew Sayer

                          3131275 wrote:

                           

                          Hi Brady,

                           

                          Please find the details below.

                           

                          I have formatted the code for better understanding.

                           

                          SQL> EXPLAIN PLAN FOR

                            2         UPDATE REF_UHC_COS_PHYS_CLMS H

                            3         SET H.Claim_Status = 'ABC'

                            4         WHERE Claim_Status IN ('XYZ','LMO')

                            5         AND H.Clm_Stat_Dt IS NULL

                            6         AND NOT EXISTS (SELECT 1

                            7                                       FROM REF_UHC_COSMOS_MEMBER M

                            8                                       WHERE M.Mbr_Sys_Id = H.Mbr_Sys_Id

                            9                                       );

                           

                          Explained.

                           

                           

                          PLAN_TABLE_OUTPUT

                          ---------------------------------------------------------------------------------------------------------------------------------------------------

                          Plan hash value: 3719384626

                          ---------------------------------------------------------------------------------------------------------------------------------------------------

                          | Id  | Operation                             | Name                                       | Rows  | Bytes     | TempSpc|

                          ----------------------------------------------------------------------------------------------------------------------------------------------------

                          |   0 | UPDATE STATEMENT        |                                                 |    37M  |  1379M  |             |

                          |   1 |  UPDATE                            | REF_UHC_COS_PHYS_CLMS |             |             |             |

                          |*  2 |   HASH JOIN ANTI             |                                                  |    37M  |  1379M  |  2525M |

                          |*  3 |    TABLE ACCESS FULL     | REF_UHC_COS_PHYS_CLMS |    60M  |  1837M  |             |

                          |   4 |    INDEX FAST FULL SCAN| UHC_COS_MBR_IDX02            |   145M |   969M  |             |

                          ---------------------------------------------------------------------------------------------------------------------------------------------------

                          Predicate Information (identified by operation id):

                          -----------------------------------------------------------------------------------------------------------------------------------------------------

                             2 - access("M"."MBR_SYS_ID"="H"."MBR_SYS_ID")

                             3 - filter(("CLAIM_STATUS"='LMO' OR "CLAIM_STATUS"='XYZ') AND "H"."CLM_STAT_D

                           

                          17 rows selected

                           

                          SELECT Table_Name,Num_Rows

                          FROM USER_TABLES

                          WHERE Table_Name='REF_UHC_COS_PHYS_CLMS';

                           

                          TABLE_NAME NUM_ROWS
                          REF_UHC_COS_PHYS_CLMS 589053467

                           

                          SELECT Table_Name,Num_Rows

                          FROM USER_TABLES

                          WHERE Table_Name='REF_UHC_COSMOS_MEMBER';

                           

                          TABLE_NAME NUM_ROWS
                          REF_UHC_COSMOS_MEMBER 145223240

                           

                          Also, am yet to create the Index you suggested since we have very less resources I need some time. Once the load on server is low I would be creating the Index. As this process goes for a FTS it takes a lot of time to create one.

                           

                          Thanks for you help!

                           

                          Please answer my previous questions, those queries against user_tables do not explain the full situation. By repeating the stats that the optimizer has to us, all we can do is come up with the same plan the optimizer did, which definitely looks like a full table scan is the best option. If you were to tell us about the distribution with the filters and how big the tables are then we can help you to tell the optimizer. The more the optimizer knows, the better your plan will be. The more you tell us, the better ideas we can get to help you.

                           

                          How big is the ref_uhc_cos_phys_clms table? (How many rows? How many blocks?)

                          How many rows do you expect your query to return?

                          For each of the filters that you are applying to ref_uhc_cos_phys_clms in each combination, how many rows do you expect to get back? (e.g. there are x rows with Claim_Status in ('XYZ','LMO') and y rows with Clm_Stat_Dt is null, all x rows also satisfy y.. etc, ) Also consider the not exists filter here (e.g. there'll only be a few rows in the table that don't have Mbr_Sys_Id existing in REF_UHC_COSMOS_MEMBER)

                           

                          Is parallelism an option? (Are there enough resources to allow it ?  I am definitely not suggesting it)

                          Is this a statement that you will run once and forget about? Does it really matter how long it takes?

                          • 10. Re: Re: What is the best index for the below update query?
                            3131275

                            Hi Solomon,

                             

                            I too believe so. Am planning to drop the present index and then create the below index soon and will get back to you guys with the explain plan.

                             

                             

                            CREATE INDEX IDX_REF_UHC_COS_PHYS_CLMS_07 ON REF_UHC_COS_PHYS_CLMS (  Claim_Status ,Mbr_Sys_Id , Clm_Stat_Dt)

                            TABLESPACE RAWINDX

                            PCTFREE 10

                            INITRANS 2

                            MAXTRANS 255

                            STORAGE

                            (

                              INITIAL 64K

                              NEXT 1M

                              MINEXTENTS 1

                              MAXEXTENTS UNLIMITED

                              );

                             

                            Thanks for your help

                            • 11. Re: What is the best index for the below update query?

                               

                              explain plan for

                                Update /*+ index(h IDX_REF_UHC_COS_PHYS_CLMS_07 )*/ ref_uhc_cos_phys_clms h

                                        Set h.Claim_Status = 'ABC'

                                        Where Claim_Status in ('XYZ','LMO')

                                        and h.Clm_Stat_Dt is null

                                        and not exists (Select 1

                                                        From REF_UHC_COSMOS_MEMBER M

                                                        where M.MBR_SYS_ID = H.Mbr_Sys_Id

                                                        );

                               

                               

                               

                              PLAN_TABLE_OUTPUT

                              Plan hash value: 3719384626

                               

                              --------------------------------------------------------------------------------------------------------

                              | Id  | Operation              | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

                              --------------------------------------------------------------------------------------------------------

                              |   0 | UPDATE STATEMENT       |                       |    37M|  1379M|       |    19M  (1)| 63:45:24 |

                              |   1 |  UPDATE                | REF_UHC_COS_PHYS_CLMS |       |       |       |            |          |

                              |*  2 |   HASH JOIN ANTI       |                       |    37M|  1379M|  2525M|    19M  (1)| 63:45:24 |

                              |*  3 |    TABLE ACCESS FULL   | REF_UHC_COS_PHYS_CLMS |    60M|  1837M|       |    18M  (1)| 62:18:18 |

                              |   4 |    INDEX FAST FULL SCAN| UHC_COS_MBR_IDX02     |   145M|   969M|       |   178K  (1)| 00:35:44 |

                              --------------------------------------------------------------------------------------------------------

                              I would NOT expect INDEX to be used when updating 37M rows of a table containing 60M rows

                              • 12. Re: What is the best index for the below update query?
                                John Brady - UK

                                |*  3 |    TABLE ACCESS FULL   | REF_UHC_COS_PHYS_CLMS |    60M|  1837M|       |    18M  (1)| 62:18:18 |


                                I would NOT expect INDEX to be used when updating 37M rows of a table containing 60M rows

                                 

                                sol.beach - Actually the full table scan is filtering and returning 60M rows from the almost 600M rows in the table.  It's a minor thing, but I thought I'd just be clear about it.  The OP posted recently that the row count for the CLMS table was 589,053,467.  The execution plan is telling us the number of rows in the data set returned by the full table scan after the filter has been applied, not the row count in the table itself.

                                 

                                I would still expect the Optimizer to choose a full table scan for 10% of the rows in a table though, so the outcome hasn't changed in any significant way.  In this case a full table scan will probably always be the "lowest cost" access method given the high percentage of rows being processed.  And the OP has made it clear that this table gets emptied and then reloaded and reprocessed each month.  So it is always updating "most" of the data in the table each time it runs.

                                 

                                John Brady

                                • 13. Re: What is the best index for the below update query?
                                  3131275

                                  Brady,

                                   

                                  The table is appended each month and we are only trying to process ( update the rows that were loaded newly ). We are not touching the older ones. The client requires us to keep the retro claims for future reference.

                                   

                                  Thanks,

                                  • 14. Re: What is the best index for the below update query?
                                    Jonathan Lewis

                                    One of the key details of your description is that in one posting you say that the other updates take about 30 minutes each but this one takes days.

                                    You have also supplied an indication of what the full set of updates looks like, including this pair:

                                     

                                    Update REF_UHC_COS_PHYS_CLMS 

                                    set claim_status='Rej_this_3'

                                    where clm_stat_dt is null

                                    and claim_status='Good_Clms'

                                    and not exists in (select clause 4);


                                    Update ref_uhc_cos_phys_clms h

                                    Set h.Claim_Status = 'Rej_this_4'

                                    Where Claim_Status = 'Good_Clms'

                                    and h.Clm_Stat_Dt is null

                                    and not exists (Select 1

                                                              From REF_UHC_COSMOS_MEMBER M

                                                              where M.MBR_SYS_ID = H.Mbr_Sys_Id

                                                              );

                                     

                                    The update to "Rej_this_3" has the same structure - including a "not exists" subquery as that for "Rej_this_4", so we can ask a couple of obvious questions:

                                     

                                    What's the (actual) execution plan for the former and how does it differ from the latter (and are you sure you've shown us the actual execution plan for that ?)

                                    Could the difference in performance be due to a dramatically different number of row updates, or is it due to the efficiency of how Oracle handles the subquery.

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next