Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    71

    Unanswered: Index on view- improve query performance from view

    There is a view with following definition


    SELECT t1.LOCATION, t1.applicable_time, t1.published_time, t1.design_capacity,
    (t1.scheduled_quantity * -1) AS scheduled_quantity,
    t1.available_quantity, t1.CYCLE, t1.loc_prop, t1.ZONE, t2.TYPE,
    t2.state, t2.county, t2.offshore, t2.flow_direction,naics
    FROM ref_gs_meter t2, gulfsouth_meter_cap t1
    WHERE t2.loc_prop = t1.loc_prop AND t2.flow_direction in ('D','I')
    UNION ALL
    SELECT t1.LOCATION, t1.applicable_time, t1.published_time, t1.design_capacity,
    t1.scheduled_quantity, t1.available_quantity, t1.CYCLE, t1.loc_prop,
    t1.ZONE, t2.TYPE, t2.state, t2.county, t2.offshore, t2.flow_direction,naics
    FROM ref_gs_meter t2, gulfsouth_meter_cap t1
    WHERE t2.loc_prop = t1.loc_prop
    AND (t2.flow_direction not in ('D','I') OR t2.flow_direction IS NULL)

    The table gulfsouth_meter_cap has indexes on (loc_prop ,LOCATION and applicable_time)

    The preformance of view is good if we do

    Select * from view

    But if we do query below it is extremely slow:-
    Select * from view where APPLICABLE_TIME BETWEEN <Date1> AND <Date2>

    How can we improve performance of above query?

    Any help is appreciated.

    Thanks
    Reema

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ><Date1> AND <Date2>
    ENSURE you use TO_DATE on the two values above.
    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
    Oct 2003
    Posts
    71

    There is a view with following definition

    APPLICABLE_TIME is a date datatype only.

    I am looking for query performance suggestions.

    Thanks
    Reema

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    look at that query for the view. you don't even NEED a UNION ALL
    (and maybe don't need the view).
    you can rewrite that to exclude the UNION ALL which will speed things up.

    Anyways, if your driving column is APPLICABLE_TIME then index it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK I might be wrong but shouldnt you remove the union all and just do
    Code:
    SELECT t1.LOCATION, t1.applicable_time, t1.published_time, t1.design_capacity, 
    (t1.scheduled_quantity * -1) AS scheduled_quantity, 
    t1.available_quantity, t1.CYCLE, t1.loc_prop, t1.ZONE, t2.TYPE, 
    t2.state, t2.county, t2.offshore, t2.flow_direction,naics 
    FROM ref_gs_meter t2, gulfsouth_meter_cap t1 
    WHERE t2.loc_prop = t1.loc_prop
    since your basically saying flow_direction can be anything including null.

    This should speed things up but you should also have an index on (applicable_time, loc_prop) in that order.

    Alan

    since your

  6. #6
    Join Date
    Oct 2003
    Posts
    71
    The Duck,

    Should I index the APplicable_Time as a seperate column as it is part of PK index on the table as well. Or do I need to create a index on the view

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Have two indexes, the original one on (loc_prop ,LOCATION and applicable_time) and a second index on just (applicable_time). The database will not be able to use an index unless the preceding elements are in the where.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Oct 2003
    Posts
    71
    THanks for all your replies

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by reemagupta
    The Duck,

    Should I index the APplicable_Time as a seperate column as it is part of PK index on the table as well. Or do I need to create a index on the view
    you cannot index a view.
    the view will use the underlying indexes on the tables.
    (be sure to anayze table and indexes after new one is created)

    I would correct that union-all as well.
    just use a DECODE statement for the scheduled_quantity column if you need
    to manipulate the data in that column.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Oct 2003
    Posts
    71
    Thanks duck,

    Thats what I did and query runs much faster now.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    sweet! I love that feeling.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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