Hi,
I am currently working on a view and have been tasked with improving its performance. The execution time of the view is around 38 seconds, but when I remove the ORDER BY clause, the time decreases to 0.7 seconds. The challenge is that I still need to keep the ORDER BY clause in place. No matter which column I use in the ORDER BY clause, the execution time doesn't drop below 35 seconds. The output of the view is nearly 150,000 rows.
The view is written as follows:
CREATE OR REPLACE VIEW view_name AS
SELECT COLUMNS_NAME
FROM
(
SELECT COLUMNS_NAME
FROM TABLE_NAME
WHERE CONDITION
UNION ALL
SELECT COLUMNS_NAME
FROM TABLE_NAME
WHERE CONDITION
)
ORDER BY COLUMN_NAME;