Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003

    Thumbs up Unanswered: Views performance hit

    I have a query which looks at 9 different tables/alias's but I require outer joins on several of them. I know that it is not possible to do 2 outer joins on the same table so thought I could put some tables into a view and only have 1 outer join on the main tables.

    Now the query takes about 55 secs to run which is no use as this is a web application

    1. Does using a view have performance implications ?
    2. If so can I doing anything to improve the performance or will I have to split it up into 2 queries.


  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    1) enable SQL_TRACE
    2) Run the query
    3) Run the trace file thru TKPROF w/EXPLAIN=username/password
    4) Post the results from TKPROF here
    You can't solve a mystery without any clues.
    The lack of specific facts in your post makes speculation useless.
    TKPROF provides detailed evidence regarding where time is being spent.

  3. #3
    Join Date
    Jul 2003
    rule #1: do not let developers use outer-joins

    There is always a better way like NOT EXISTS or something.

    rule #2: do not expect good performance results with queries that join 5-10 tables

    Do exactly what antecedent said above. Do this ALL THE TIME. TKPROF is the only way to see how oracle is actually processing the query.
    example: Sometimes explain-plan will say the query is uning the PK-index when in actuality it is not.

    ps: Billm really helped me with his document regarding performance/query analyzation. I give him credit for helping me see the light.
    - 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