Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Query Optimization in oracle 9i

    Hi,
    Please find the attach file and help me to optimize the queries.
    Regards,
    sdey.
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Don't put functions on column-names in your where clauses.
    Also, I don't see the logic in specifying a wildcard for EVERY ROW in your where clause: where x like '%' means nothing.
    Also, you start with a date, change it to a character, then change it to a date again. There is no reason for this.
    Is SOURCE_CUSTOMER_ID numeric or a character field?

    PHP Code:
      cust.SALES_CHANNEL_CD LIKE '%' AND
      (
    UPPER(ord.INSTALL_FIRST_NMLIKE '%') AND
      (
    UPPER(ord.INSTALL_LAST_NMLIKE '%') AND
      (
    UPPER(ord.INSTALL_DAY_PHONE_NBRLIKE '%') AND
      (
    UPPER(ord.INSTALL_ZIP_CDLIKE '%') AND
      (
    UPPER(ord.SOURCE_CUSTOMER_IDIN ('','91075')) AND
      (
    UPPER(ord.SOURCE_CUSTOMER_IDLIKE '%') AND
      (
    TO_DATE(TO_CHAR(ord.CREATE_DT'MM/DD/YYYY'), 'MM/DD/YYYY') >= TO_DATE('06/01/2008''MM/DD/YYYY')) AND
      (
    TO_DATE(TO_CHAR(ord.CREATE_DT'MM/DD/YYYY'), 'MM/DD/YYYY') <= TO_DATE('03/13/2009''MM/DD/YYYY')) AND 
    could be changed to:
    PHP Code:
    ord.SOURCE_CUSTOMER_ID IN ('','91075') AND
    ord.CREATE_DT >= TO_DATE('06/01/2008''MM/DD/YYYY')) AND
    ord.CREATE_DT TO_DATE('03/14/2009''MM/DD/YYYY')) AND
    ... 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am surprised that Oracle does not spew error(s) because you have RESERVED WORDS as column names; i.e. PACKAGE, PLAN & possibly others

    Only tables which contribute columns to SELECT clause should exist within FROM clause.
    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.

  4. #4
    Join Date
    May 2009
    Posts
    5
    Quote Originally Posted by The_Duck
    PHP Code:
    ord.SOURCE_CUSTOMER_ID IN ('','91075') AND 
    This won't work. '' is null and null is not in (null). It needs to be
    PHP Code:
    NVL(ord.SOURCE_CUSTOMER_ID,'91075') = '91075' 

  5. #5
    Join Date
    May 2009
    Posts
    2

    better approach in case of date

    Hi,
    I think this is better approach in case of date -

    CREATE_DT BETWEEN TO_DATE('01/23/2008', 'MM/DD/YYYY') AND TO_DATE('01/24/2009', 'MM/DD/YYYY')

Posting Permissions

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