SELECT xxx FROM view runs much slower than SELECT xxx from underlying query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SELECT xxx FROM view runs much slower than SELECT xxx from underlying query

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Question

    I've a query that I've used to create a view.

    e.g. CREATE VIEW name_view as
    (SELECT x, y, z, 'STRING'
    FROM tablea@remore_db, tableb@remote_db
    WHERE ... etc)

    When I run the SELECT query in SQL*Plus it runs very quickly.

    When I select from the view it runs slowly.

    IF I remove the 'STRING' from the query and recreate the View the select from the view runs quite quickly.

    NB- the base tables are in a remote database and I create the view in my local database.

    I'm running 8i on NT

    Confused

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    More details ... >

    I have a local and a remote database.

    I create a VIEW locally that SELECTs from the remote DB.
    The VIEW statement is 2 selects with a UNION. Depending on the records returned a STRING value is used as an identifier so that when I SELECT * FROM VIEW I know where the records came from (i.e.)

    CREATE VIEW test_view (fred, john, tom, qty, type)
    AS (
    SELECT col_a, col_b, col_c, SUM(col_d), 'Type A'
    FROM table_1@remote_db
    UNION
    SELECT col_1, col_2, col_3, SUM(col_4), 'Type B'
    FROM table_2@remote_db
    GROUP BY
    fred,
    john,
    tom,
    type;

    When I run a SELECT * FROM test_view it takes 15-20 seconds before anything is returned. If I remove the string identifier ('Type A') it runs instantaneously. Is there a problem sorting and grouping the remote rows with a local string value?

    How can I see what's going on?



  3. #3
    Join Date
    Mar 2002
    Posts
    534
    Have a look at the explain plan of your 2 queries and you will see why one is slower as the other.
    To see the explain plan execute SET AUTOTRACE ON in SQL*Plus.

    HTH
    Mike

  4. #4
    Join Date
    Apr 2002
    Posts
    11

    Two independent queries, concatenated

    Since the "type" column makes the parts of the UNION
    mutually exclusive, and since you are grouping on the "type" column, then the GROUP BY of the UNION
    is the same as a UNION ALL of each part with a GROUP BY.

    So this query could be:

    CREATE VIEW test_view (fred, john, tom, qty, type)
    AS (
    SELECT col_a, col_b, col_c, SUM(col_d), 'Type A'
    FROM table_1@remote_db
    GROUP BY col_a, col_b, col_c
    UNION ALL
    SELECT col_1, col_2, col_3, SUM(col_4), 'Type B'
    FROM table_2@remote_db
    GROUP BY col_1, col_2, col_3;


    Notice that each part of the UNION (including the GROUP BY)
    can be processed by the remote database,
    instead of performing the GROUP BY on the local side.

Posting Permissions

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



Click Here to Expand Forum to Full Width