1 2 Previous Next 20 Replies Latest reply on Aug 20, 2018 11:09 AM by Jonathan Lewis Go to original post
      • 15. Re: Query performance Issue
        Jonathan Lewis

        I've done the write-up: https://jonathanlewis.wordpress.com/2018/08/20/masterclass-1/

         

         

         

         

        Regards

        Jonathan Lewis

        • 16. Re: Query performance Issue
          933257

          Thank You So much Jonathan.

           

          There does exists a frequency histogram on the column STS. Below is the exact data pattern as fetched from the table.

           

          STS   COUNT(*)

          - ----------

          A          6

          E        126

          D        866

          C   80212368

          B     117631

          • 17. Re: Query performance Issue
            Jonathan Lewis

            933257,

             

            Given those stats and your current run-time statistics my comment in the blog note about doing the B's before the A's isn't going to make any difference - you're running through all (or almost all) the B's before returning a result. If you adopt the function-based index (which is a very good idea) then you'd either want to include the D's and E's in the case statement or write a version that said things like:

               case sts when 'C' then to_char(null) else sts

             

            then you wouldn't have to worry about recreating the index with a new definition each time a new STS value appeared

             

            Regards

            Jonathan Lewis

            1 person found this helpful
            • 18. Re: Query performance Issue
              933257

              Thank You so much. Lot of knowledge to digest.

               

              I do have other two of the values(D,E) in the STS column less popular. I will try/test having the new composite index on (STS,DT).

              • 19. Re: Query performance Issue
                933257

                Sorry Didn't see your reply before posting mine. Would it be okay to have just new composite index on (STS,DT) or ((STS,CN), considering other usage in the query without modifying any existing queries?

                • 20. Re: Query performance Issue
                  Jonathan Lewis

                  933257 wrote:

                   

                  Sorry Didn't see your reply before posting mine. Would it be okay to have just new composite index on (STS,DT) or ((STS,CN), considering other usage in the query without modifying any existing queries?

                   

                  It's all about the numbers (and the resources to support the change) - it's certainly a strategy you could use to avoid changing any existing code.

                  You do find sometimes, though, that Oracle will stop using an index for some queries because you've added column to it; and the cost of maintaining the index may go up because the index is physically bigger.  In your case, of course, you'll be recreating the index with "compress 1" (or even just "compress" if the column combination is repetitive).

                   

                  How to think through your choice:

                   

                  For a rough approximation - nearly every B row is in its own block.

                  At present you seem to fetch nearly all of the 117,500 rows before finding one row that matches  cn < '4' and dt < sysdate.

                   

                  Do a count of rows where sts = 'B' and cn < '4'

                  then do a count where sts = 'B' and dt < sysdate

                   

                  If one of those has a very small count compared to the other than that's the one to use in the two-column index. If, individually, they both have fairly large counts then do a count that includes all three predicates - you may want to include all three in the index - probably with "compress 2", and probably with which ever of the cn and dt produces a smaller count in the first two tests.

                   

                  Regards

                  Jonathan Lewis

                  1 person found this helpful
                  1 2 Previous Next