Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: optimizing a view

    This may have been answered somewhere previously but I'm having a hard time finding it.

    I'm trying to determine what impacts the performance of a view. Is it the underlying table indexes, the joins used in the view, or the where clause. Im guessing to some degree it is all of the above but trying to determine what changes I need to make to hopefully create a quicker response time.

    In the example below:
    If I added indexes for the fields in the join on both tables would this improve performance?

    If I added indexes for the fields in the where clause would this improve performance?

    Are there other suggestons or ways to improve performance for a view?
    What is the best place to start looking to improve performance?

    I apoogize if this question is too generic or too basic - I'm obviously not a DBA - but have been tasked with trying to improve the reponse time for this application.

    Any and all assistance is appreciated.

    As it sits now the view looks like this:
    CREATE OR REPLACE FORCE VIEW "C0212CRPD"."VW_F41021_F4108" ("LIMCU", "LIITM", "IOLITM", "IOMMEJ", "LILOTN", "LILOCN", "LIPQOH") AS
    SELECT C0212CRPD.F41021.LIMCU,C0212CRPD.F41021.LIITM, C0212CRPD.F4108.IOLITM, C0212CRPD.F4108.IOMMEJ, C0212CRPD.F41021.LILOTN,C0212CRPD.F41021.LILOCN, C0212CRPD.F41021.LIPQOH
    FROM C0212CRPD.F41021
    INNER JOIN C0212CRPD.F4108
    ON C0212CRPD.F41021.LIMCU = C0212CRPD.F4108.IOMCU
    AND C0212CRPD.F41021.LILOTN = C0212CRPD.F4108.IOLOTN
    AND C0212CRPD.F41021.LIITM = C0212CRPD.F4108.IOITM
    WHERE C0212CRPD.F4108.IOMMEJ >= 109100
    AND C0212CRPD.F41021.LIPQOH >0;
    Last edited by jfm617; 07-08-09 at 15:16.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR REPLACE FORCE VIEW "C0212CRPD"."VW_F41021_F4108"
    ("LIMCU",
     "LIITM",
     "IOLITM",
     "IOMMEJ",
     "LILOTN",
     "LILOCN",
     "LIPQOH")
    AS
      SELECT c0212crpd.f41021.limcu,
             c0212crpd.f41021.liitm,
             c0212crpd.f4108.iolitm,
             c0212crpd.f4108.iommej,
             c0212crpd.f41021.lilotn,
             c0212crpd.f41021.lilocn,
             c0212crpd.f41021.lipqoh
      FROM   c0212crpd.f41021
             INNER JOIN c0212crpd.f4108
               ON c0212crpd.f41021.limcu = c0212crpd.f4108.iomcu
                  AND c0212crpd.f41021.lilotn = c0212crpd.f4108.iolotn
                  AND c0212crpd.f41021.liitm = c0212crpd.f4108.ioitm
      WHERE  c0212crpd.f4108.iommej >= 109100
             AND c0212crpd.f41021.lipqoh > 0;
    >If I added indexes for the fields in the join on both tables would this improve performance?
    It might but the only way to know for sure is to add the indexes.

    >If I added indexes for the fields in the where clause would this improve performance?
    It might but the only way to know for sure is to add the indexes.
    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
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Explanation?

    You could obtain an "explain plan" for the statement in the view and it will give you a clue what to address:

    One way is like this:
    Code:
    SET AUTOT ON
    SELECT c0212crpd.f41021.limcu,
             c0212crpd.f41021.liitm,
             c0212crpd.f4108.iolitm,
             c0212crpd.f4108.iommej,
             c0212crpd.f41021.lilotn,
             c0212crpd.f41021.lilocn,
             c0212crpd.f41021.lipqoh
      FROM   c0212crpd.f41021
             INNER JOIN c0212crpd.f4108
               ON c0212crpd.f41021.limcu = c0212crpd.f4108.iomcu
                  AND c0212crpd.f41021.lilotn = c0212crpd.f4108.iolotn
                  AND c0212crpd.f41021.liitm = c0212crpd.f4108.ioitm
      WHERE  c0212crpd.f4108.iommej >= 109100
             AND c0212crpd.f41021.lipqoh > 0;
    -- Another way is this:
    Code:
    EXPLAIN PLAN FOR
      SELECT c0212crpd.f41021.limcu,
             c0212crpd.f41021.liitm,
             c0212crpd.f4108.iolitm,
             c0212crpd.f4108.iommej,
             c0212crpd.f41021.lilotn,
             c0212crpd.f41021.lilocn,
             c0212crpd.f41021.lipqoh
      FROM   c0212crpd.f41021
             INNER JOIN c0212crpd.f4108
               ON c0212crpd.f41021.limcu = c0212crpd.f4108.iomcu
                  AND c0212crpd.f41021.lilotn = c0212crpd.f4108.iolotn
                  AND c0212crpd.f41021.liitm = c0212crpd.f4108.ioitm
      WHERE  c0212crpd.f4108.iommej >= 109100
             AND c0212crpd.f41021.lipqoh > 0
    /
    @?/rdbms/admin/utlxpls
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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