9 Replies Latest reply: Feb 5, 2013 7:49 AM by Nicosa-Oracle RSS

    Any ideas on making this SQL run faster?

    989011
      This query takes a long time (couple of hours). Can anyone notice a way to improve it?

      create TABLE Temp_A
      nologging as
      select /*+ parallel(8)*/ a.field1,
      b.field2
      from (select field1 from tableA where date between '01DEC2012' and '31DEC2012'
      group by field1 ) a
      left join tableB b on a.field1 = b.field1;

      I actually have another peice that I stripped out to make it faster because tableA has multiple entries for the single entry in tableB.
      I was thinking it might speed it up to add the seperate TableA records later since tableA is smaller than tableB (400million).

      Any help would be appreciated...

      Thanks,
      J
        • 1. Re: Any ideas on making this SQL run faster?
          Paul  Horth
          Welcome to the forum.

          Please read {message:id=9360002} and {message:id=9360003} and follow the advice there.

          In the meantime, this looks wrong:
          where date between '01DEC2012' and '31DEC2012' 
          is the 'date' column (bad name for a column BTW) actually of type date? If so, your comparsion with strings is all wrong.
          You should use to_date to convert them to dates.

          If it's not of type date: it's worse, you won't get the results you expect.
          • 2. Re: Any ideas on making this SQL run faster?
            989011
            I just changed the field names for the forum. The fieldname is not "date".

            I get the proper information back, it just takes forever and was just curious if I was missing something. It might just be that it takes a long time to go through the 400 million records..

            Edited by: 986008 on Feb 4, 2013 9:27 AM
            • 3. Re: Any ideas on making this SQL run faster?
              Nicosa-Oracle
              Hi,
              986008 wrote:
              I just changed the field names for the forum. The field is not "date".
              Well, if it is varchar2 then guess where the Days of January 3042 will be sorted between :
              [11.2] Scott @ My11g > l
                1  with wannabedates(val) as (
                2       select '01DEC2012' from dual union all
                3       select '31DEC2012' from dual union all
                4       select '14JAN3042' from dual
                5  )
                6  select val
                7  from wannabedates
                8* order by val
              [11.2] Scott @ My11g > /
              
              VAL
              ---------
              01DEC2012
              14JAN3042
              31DEC2012
              You where clause basically says to take every rows whose first digit is between 0 and 3..... +(I guess that is quite a lot in you case....)+

              Take a look at that, and provide the relevant information : {message:id=9360003}
              • 4. Re: Any ideas on making this SQL run faster?
                riedelme
                986008 wrote:
                This query takes a long time (couple of hours). Can anyone notice a way to improve it?

                create TABLE Temp_A
                nologging as
                select /*+ parallel(8)*/ a.field1,
                b.field2
                from (select field1 from tableA where date between '01DEC2012' and '31DEC2012'
                group by field1 ) a
                left join tableB b on a.field1 = b.field1;

                I actually have another peice that I stripped out to make it faster because tableA has multiple entries for the single entry in tableB.
                I was thinking it might speed it up to add the seperate TableA records later since tableA is smaller than tableB (400million).

                Any help would be appreciated...

                Thanks,
                J
                Parallel query does not always help. How long does it take when run in serial? Does an execution plan indicate that the query really is being run in parallel?

                Post the execution plan. Change the table names if you have to.

                Things to look at:
                1. Make sure "date" in "tableA" is indexed
                2. Does adding a parallel hint to the inline query do anything (Help, hurt, or do nothing)?
                3. Is field1 in "tableA" indexed?

                Edited by: riedelme on Feb 4, 2013 9:29 AM
                • 5. Re: Any ideas on making this SQL run faster?
                  989011
                  Someone else wrote the query. I usually use to_date but this seemed to work other than speed.

                  Is it faster to compare using the to_date vs string? Im changing it now and will see.


                  Im still running the explain plan.I am not the DBA so I dont have full access to all the features.
                  • 6. Re: Any ideas on making this SQL run faster?
                    BobLilly
                    Without seeing the actual query and explain plan it's only a guess but, is there any chance tableB is actually a remote table or view? If this is the case, then a DRIVING_SITE hint might help.

                    Regards,
                    Bob
                    • 7. Re: Any ideas on making this SQL run faster?
                      Paul  Horth
                      986008 wrote:
                      Someone else wrote the query. I usually use to_date but this seemed to work other than speed.

                      Is it faster to compare using the to_date vs string? Im changing it now and will see.


                      Im still running the explain plan.I am not the DBA so I dont have full access to all the features.
                      Always compare dates with dates. Problems that arise if you don't include:
                      1. The comparison is wrong and you get dates you weren't expecting (as shown by a previous poster).
                      2. Any index on the column is disabled because of the implicit conversion.
                      3. A change in the environment causes the string to date conversion to fail.

                      I also notice you haven't posted the required information as mentioned in the FAQs. Unless you do, you
                      won't be getting much help.
                      • 8. Re: Any ideas on making this SQL run faster?
                        989011
                        Yeah. The explain plan isnt working on my PL/SQL developer. It just hangs and sometimes I get a write error. (Which is very frustrating)

                        I believe we have some features disabled because I am not the DBA for the system.
                        I am also trying to locate which fields are indexed.

                        Edited by: 986008 on Feb 5, 2013 5:33 AM
                        • 9. Re: Any ideas on making this SQL run faster?
                          Nicosa-Oracle
                          It's my opinion, but i feel like anyone's working on Oracle Database should have SQL*Plus as one of its main tools : Its power in analyzing issues lies in its apparent simplicity.
                          +"An elegant weapon, for a more civilized age"+
                          :-)

                          What does an explain plan gives on SQL*Plus ?
                          What does the "describe your_table" gives on SQL*Plus ?