10 Replies Latest reply: Aug 8, 2012 8:19 AM by 952768 RSS

    query and performance

    user522961
      Hi,

      from the performance point of view, which of the following is better and why :

      (1)
      select FILE_NAME, TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 from dba_data_files
      (2)
      select FILE_NAME, TABLESPACE_NAME, SUM(BYTES)/(1024*1024*1024 ) from dba_data_files
      Thank you.
        • 1. Re: query and performance
          Helios-GunesEROL
          Hi;

          Why you dont try to get explan plan and compare it yourself :)?

          Regard
          Helios
          • 2. Re: query and performance
            CSM.DBA
            As far as I know, there's no difference.

            Oracle interprets both in the same way.

            Is it correct Helios?


            Thanks,
            CSM
            • 3. Re: query and performance
              div
              Maybe this will help your understanding.

              http://docs.oracle.com/html/A85397_01/operator.htm
              • 4. Re: query and performance
                952768
                None of them will perform, you should use a group by statement and choose if you want to sum on tablespace because on filename is not a clever thing to do ;-)
                • 5. Re: query and performance
                  jgarry
                  Both would likely be optimized away if you compiled.
                  • 6. Re: query and performance
                    Jonathan Lewis
                    user522961 wrote:
                    select FILE_NAME, TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 from dba_data_files
                    
                    select FILE_NAME, TABLESPACE_NAME, SUM(BYTES)/(1024*1024*1024 ) from dba_data_files
                    Adjusting to allow for the error highlighted by specdev, it's not obvious whether or not there should be a difference. We might hope the optimizer would get very clever and realise that both examples were dividing by the same constant. I don't think there's an easy way to be certain - and any difference is probably marginal given the work involved in getting the penultimate intermediate data set.

                    I was curious, however, so I tested it, and there probably is a very small difference in implementation, which would probably lead to a small difference in performance: http://jonathanlewis.wordpress.com/2012/08/06/arithmetic/

                    Regards
                    Jonathan Lewis
                    • 7. Re: query and performance
                      rp0428
                      >
                      I was curious, however, so I tested it, and there probably is a very small difference in implementation, which would probably lead to a small difference in performance
                      >
                      In between books? Or just waiting for your technical reviewers to get back to you. Seems you can't be tied up on Oracle 12 prerelease just yet!
                      • 8. Re: query and performance
                        952768
                        It seems that when your start with Multiplication or Division it depends on the order of Multiplication/Division, Addition/Subtraction.

                        Below the example if you start with Addition:

                        explain plan for
                        select
                        dummy
                        from
                        dual
                        group by
                        dummy
                        having
                        to_number(dummy)+100/100+100/100 > 0
                        ;

                        Predicate Information:
                        1 - filter(TO_NUMBER("DUMMY")+1+1>0)
                        • 9. Re: query and performance
                          Jonathan Lewis
                          specdev wrote:
                          explain plan for
                          select
                          dummy
                          from
                          dual
                          group by
                          dummy
                          having
                          to_number(dummy)+100/100+100/100 > 0
                          ;
                          
                          Predicate Information:
                          1 - filter(TO_NUMBER("DUMMY")+1+1>0)
                          Slightly more entertaining - under Oracle, addition is not associative, i.e. a + b + c = (a + b) + c, but not a + (b + c)
                          Change your predicate to: 100/100 + 100/100 + to_number(dummy) and the optimizer WILL turn it into 2 + to_number(dummy)

                          Regards
                          Jonathan Lewis
                          • 10. Re: query and performance
                            952768
                            so we could better write it down as:

                            select ... 1/1024/1024/1024*SUM(BYTES) from dba_data_files

                            Predicate:
                            filter(.000000000931322574615478515625*SUM("BYTES")

                            :-)