Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: Query Optimization

    Running some simple queries in Oracle trying to learn how to make them most efficient so hopefully I can apply the same techniques to more complex queries.

    TABLE 1
    760,000 rows of member data
    149,000 are active which is indicated by an enddate of 31-Dec-2009

    TABLE 2
    48 million rows of claim data
    looking for claims with a servicedate > 01-Jan-2009

    Using a simple count(*) query on either table returns results within a second or two, but joining the tables and applying the criteria mentioned above takes about 10 minutes. I'm using SQL Plus. Queries I've tried are:

    QUERY 1
    select count(*) from pni.members m inner join pni.claims c on m.membernumber = c.membernumber where m.enddate = '31-Dec-2009' and c.servicedate > '01-jan-2008';

    QUERY 2
    select count(*) from pni.claims where pni.claims.servicedate > '01-jan-2008' and pni.claims.membernumber in (select membernumber from pni.members where enddate = '31-Dec-2009');

    First time I've worked with recordsets this large but I haven't worked much with Oracle either, so is this the performance you would expect or is there optimation I could do? Just occurred to me I could try populating temp tables with the subsets of data from each table and run my query on those. Thoughts?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With Oracle character strings a delimited by single quote marks; i.e
    '31-Dec-2009' or 'This is a string 31-Dec-2009 not a date'

    You should use TO_CHAR() function to convert strings to DATE datatype for comparisons.

    Is there an index on enddate & servicedate columns & are statistics current?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2006
    Posts
    2
    I'll to TO_CHAR() and check with the DBA on the indexing and statistics. Thanks!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Just occurred to me I could try populating temp tables with the subsets of data from each table and run my query on those. Thoughts?
    Do NOT make "TEMP" tables; sub-optimal option.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent
    You should use TO_CHAR() function to convert strings to DATE datatype for comparisons.
    It's actually to_date() to convert a string to a DATE

    So it should be:
    Code:
    where m_enddate = to_date('31-DEC-2009', 'DD-MON-YYYY')
    But beware that this might fail if the client has a different NLS_LANG setting because DEC is not a valid month e.g. in German or French.

    To be on the safe side you should use:
    Code:
    where m.enddate = to_date('31-12-2009', 'DD-MM-YYYY')
    But you will probably still not get any results, because the DATE datatype also stores the time and to_date('31-12-2009', 'DD-MM-YYYY') will return the date with the time set to 00:00:00. To get rid of the time part, you'll need:
    Code:
    where trunc(m.enddate) = to_date('31-12-2009', 'DD-MM-YYYY')
    Note that you will need a function based index on enddate (and the other columns) in order to make this efficient:
    Code:
    CREATE INDEX idx_trunc_date ON claims (trunc(enddate), trunc(servicedate) )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •