Slow UNION in a VIEW

[cpaige]

I have constructed a UNION that I place in a VIEW. The UNION involves 13 tables. This seems to slow it down quite a bit. To make matters worse I'm running the UNION for a view.

To speed it up I have removed all the JOINS from the selects and added a 'WITH READ ONLY' clause at the end of the VIEW.

I'm wondering if there is anything else I can do?

The statment is pretty simple:

CREATE OR REPLACE VIEW unionquery AS
SELECT H.ONEKEY AS ONEKEY, A.TWOKEY AS TWOKEY, 'TYPE A' AS SOURCE
FROM TYPEA A
UNION
SELECT H.ONEKEY AS ONEKEY, A.TWOKEY AS TWOKEY, 'TYPE B' AS SOURCE
FROM TYPEB A
UNION

... same thing repeated 13 times
WITH READ ONLY

Is there any I can do?
Clayton T. Paige
claytonpaige@yahoo.ca

Programmer Extraordinaire

========================================================

"Who is General Failure? and Why is he reading my disk drive?"

 

[did02]

If the 13 subqueries get distinct rows, replace UNION by UNION ALL. It avoids to oracle to search dupplicate rows that don't exist.

Rgds,
Didier

 

[Turkbear]

Hi,
If this is data you need to access frequently and it does not change too often, why not create a table with those unions instead of a view?

If the data is very dynamic, perhaps a cron job ( or scheduler if in Windows) to rebuild the 'unioned' table
on a regular basis -

Just my 2c,

 

[cpaige]

Hi,

I agree that creating a table is the best solution. The reason I have to create the view in the first place is because of a poor schema, but I don't have a choice. I am just producing reports and I'm not allowed to modify or add to the database without going through a lot of red tape. The view and union is the best possible solution in the shortest amount of time.

Thanks for the feedback. Any comments are always appreciated.
Clayton T. Paige
claytonpaige@yahoo.ca

Programmer Extraordinaire

========================================================

"Who is General Failure? and Why is he reading my disk drive?"