Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    16

    Unanswered: Query Performance Tuning - Need inputs

    Hi,
    I'm using one of the select query in a inline view & it uses a table which has more than 15 lakh records.

    Following is the query am using :

    SELECT a.*, first_value(to_date(effdate, 'MMDDYYYY'))
    OVER(PARTITION BY to_date(effdate, 'MMDDYYYY') order by to_date(effdate, 'MMDDYYYY') DESC) effdate1
    FROM SCHEMA.EX_TBL@LINK_YSCHEMA a
    WHERE TRUNC(SYSDATE) > TO_DATE(effdate, 'MMDDYYYY')
    AND TRUNC(SYSDATE) < TO_DATE(expdate, 'MMDDYYYY')
    AND Col3 = 'D'

    The table has 2 indexes on the effdate & expdate columns. Still the query is taking around 250+ seconds to fetch the results. Can someone help me to get this tuned ?

    The select is used to insert a table. Like Insert into X_TBL select x,y,z.. from tbl a, (SELECT a.*, first_value(to_date(effdate, 'MMDDYYYY'))
    OVER(PARTITION BY to_date(effdate, 'MMDDYYYY') order by to_date(effdate, 'MMDDYYYY') DESC) effdate1
    FROM SCHEMA.EX_TBL@LINK_YSCHEMA a
    WHERE TRUNC(SYSDATE) > TO_DATE(effdate, 'MMDDYYYY')
    AND TRUNC(SYSDATE) < TO_DATE(expdate, 'MMDDYYYY')
    AND Col3 = 'D') b
    where a.x=b.x ... ;

    Rather than partitioning the table is there a way to get this query tuned.. ?

    Please provide your inputs.

    Thanks,
    Vijay

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why do you store date values into a character column? (At least, that's what "to_date(effdate, 'mmddyyyy')" & expdate suggests).

    Query fetches data over a database link. Would it be possible to create a materialized view, instead, and fetch data from your own database / schema? (Set its refresh rate accordingly to your needs). It is easy to test so ... try it and see whether there's any improvement.

  3. #3
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    O.K., let's resume:

    you have 2 Indexes:

    One on EFFDATE and one on EXPDATE.

    Your WHERE clause is:

    Code:
    WHERE TRUNC(SYSDATE) > TO_DATE(effdate, 'MMDDYYYY')
    AND   TRUNC(SYSDATE) < TO_DATE(expdate, 'MMDDYYYY')
    AND   Col3 = 'D'
    And - surprisingly - Oracle is doing a full table scan. What did you expect ?

    Or, the other way 'round: If you where the optimizer, what would you do ?

    (and, of course, as Littlefoot already mentioned:
    there's a price you have to pay for DATE operators on VARCHAR columns:
    either a full table scan, or a function based index ...)
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) What did you expected by
    first_value(to_date(effdate, 'MMDDYYYY'))
    OVER(PARTITION BY to_date(effdate, 'MMDDYYYY') order by to_date(effdate, 'MMDDYYYY') DESC) effdate1

    "to_date(effdate, 'MMDDYYYY')" was used three times(in PARTITION BY ..., first_value(...) and order by ...),
    so FRIST_VALUE(to_date(effdate, 'MMDDYYYY')) is the to_date(effdate, 'MMDDYYYY') itself and order by to_date(effdate, 'MMDDYYYY') may be no meaning.

    As a consequence,
    the result of the whole expression(first_value(...) OVER(...) effdate1) must be equal to simple "to_date(effdate, 'MMDDYYYY') effdate1".


    (2) I thought that an index having multiple columns might be useful.
    For example, an index for three columns(col3 , effdate , expdate) might be worth to try.

    And, you want to use the query in INSERT statement with "where a.x=b.x ... ",
    so another index for four(or more) columns(x , ... , col3 , effdate , expdate) or (col3 , effdate , expdate , x , ...) might be also worth to try.

  5. #5
    Join Date
    Nov 2008
    Posts
    16

    Thank you all..

    Thank you all for guiding me with your ideas. I tried creating indexes & reduced the usage of to_date function. It all helped me to gain performance. Thanks once again.


    Vijay..

Posting Permissions

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