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")

                          :-)