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
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?
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
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.