This discussion is archived
9 Replies Latest reply: Feb 5, 2013 5:49 AM by Nicosa RSS

Any ideas on making this SQL run faster?

989011 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points