Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Interesting ! Any optimization ?

    The following query found to be very slow during execution. Please any optimization for the same ?

    UPDATE t_car SET t_car.id_stat = ( SELECT id_stat FROM (SELECT t_statuts_car.id_statuts, t_statuts_car.date_fin FROM t_statuts_car ORDER BY date_fin DESC ) WHERE id_car = ? AND date_fin < > '1/01/2004' AND ROWNUM < 2 ) WHERE t_car.id_car = ?

    Thanks
    Sam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You seem to have some of the predicates in the wrong place - it would not parse correctly as given.

    I think it should be:
    Code:
    UPDATE t_car 
       SET t_car.id_stat = ( SELECT id_stat
                               FROM (SELECT t_statuts_car.id_statuts
                                       FROM t_statuts_car
                                      WHERE id_car = ?
                                        AND date_fin <> TO_DATE('01/01/2004','DD/MM/YYYY')
                                      ORDER BY date_fin DESC
                                    )
                              WHERE ROWNUM < 2
                           )
     WHERE t_car.id_car = ?

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Things which might help are

    1) Have the tables/indexes been analyzed recently
    2) Adding more indexes may help if you havent got it already on
    a)t_car.id_car
    b)t_statuts_car.date_fin,t_statuts_car.id_statuts

    Alan

Posting Permissions

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