The fastest way to run this query is to remove the inline view and use parallelism.
Here are the average number of seconds to run the test cases at the bottom of this answer.
No Parallel Parallel
Inline View 221 206
No Inline View 167 154
Details
Parallel execution can significantly improve performance of many long-running queries.
But it requires Enterprise Edition, adequate resources, a sane configuration, etc. On 11gR2, it may be as simple as SELECT /*+ parallel */ * from ....
If you don't have Enterprise Edition, then something like @pkuderov's "poor-man's parallelism" may help.
It is surprising that @AndyDan's suggestion to move the order by inside the inline view works so well. Normally Oracle is smart enough to make those simple query transformations for you. Oracle has obviously put a lot of thought into the performance of UNION ALL statements. They are supported in materialized views, there is the USE_CONCAT hint to enable switching between OR and UNION plans, 12c can concurrently execute branches, etc. It's odd that they missed such a simple performance fix.
An important detail might be missing from this problem. If the query takes an hour to run, the results are not being displayed on a screen. If the data is being stored, much of the execution time may be used to write to disk, log the changes, update indexes, etc. You should include more information about how the results are stored.
The question asks for efficiency, not performance. Answered literally, the most efficient solution is "No Inline View, No Parallel", because parallelism can consume
a lot of extra resources for a small benefit. Especially in my test, since I ran it on a desktop with a single hard-drive. Using 2 parallel threads will almost never
exactly double performance, but on most servers it should do much better than my test case.
Test setup
--Create sample tables with 67 million rows, gather stats, create table to hold results.
create table tablea nologging as
select 'Sam' name, 'Imp01' impfile, timestamp '2012-05-16 09:54:02.477' imptime, 'blah' fieldX, 'abcde' fieldY from dual union all
select 'Ann' name, 'Imp01' impfile, timestamp '2012-05-16 09:54:02.478' imptime, 'blah' fieldX, 'ldkse' fieldY from dual union all
select 'Bart' name, 'Import12' impfile, timestamp '2012-05-16 09:55:37.387' imptime, 'blah' fieldX, 'dkcke' fieldY from dual union all
select 'Sasha' name, 'Import12' impfile, timestamp '2012-05-16 09:55:37.385' imptime, 'blah' fieldX, 'leele' fieldY from dual;
begin
for i in 1 .. 24 loop
insert /*+ append */ into tablea select * from tablea;
commit;
end loop;
end;
/
create table tableb nologging as
select 'Mark' name, 'Imp01' impfile, timestamp '2012-05-16 09:54:02.477' imptime, 'blah' fieldX, 'lslsk' fieldY from dual union all
select 'John' name, 'Import12' impfile, timestamp '2012-05-16 09:55:37.384' imptime, 'blah' fieldX, 'lmwqd' fieldY from dual;
begin
for i in 1 .. 25 loop
insert /*+ append */ into tableb select * from tableb;
commit;
end loop;
end;
/
begin
dbms_stats.gather_table_stats(user, 'TABLEA');
dbms_stats.gather_table_stats(user, 'TABLEB');
end;
/
create table results nologging as select name, impfile, imptime from tablea;
Test queries
--#1: Inline view, no parallel.
insert /*+ append */ into results
SELECT * from
(
SELECT a.Name as field1, a.ImpFile as field2, a.ImpTime
FROM tablea a WHERE a.fieldX = 'blah' AND length(a.fieldY) = 5
UNION ALL
SELECT b.Name as field1, b.ImpFile as field2, b.ImpTime
FROM tableb b WHERE b.fieldX = 'blah' AND length(b.fieldY) = 5
) foo
ORDER BY field1, field2;
--#2: No inline view, no parallel.
insert /*+ append */ into results
SELECT a.Name as field1, a.ImpFile as field2, a.ImpTime
FROM tablea a WHERE a.fieldX = 'blah' AND length(a.fieldY) = 5
UNION ALL
SELECT b.Name as field1, b.ImpFile as field2, b.ImpTime
FROM tableb b WHERE b.fieldX = 'blah' AND length(b.fieldY) = 5
ORDER BY 1, 2;
--#3: Inline view, parallel.
insert /*+ append */ into results
SELECT /*+ parallel(2) */ * from
(
SELECT a.Name as field1, a.ImpFile as field2, a.ImpTime
FROM tablea a WHERE a.fieldX = 'blah' AND length(a.fieldY) = 5
UNION ALL
SELECT b.Name as field1, b.ImpFile as field2, b.ImpTime
FROM tableb b WHERE b.fieldX = 'blah' AND length(b.fieldY) = 5
) foo
ORDER BY field1, field2;
--#4: No inline view, parallel.
insert /*+ append */ into results
SELECT /*+ parallel(2) */ a.Name as field1, a.ImpFile as field2, a.ImpTime
FROM tablea a WHERE a.fieldX = 'blah' AND length(a.fieldY) = 5
UNION ALL
SELECT b.Name as field1, b.ImpFile as field2, b.ImpTime
FROM tableb b WHERE b.fieldX = 'blah' AND length(b.fieldY) = 5
ORDER BY 1, 2;
Test Notes
The test was run on 12c. For simplicity, I removed the truncate table results; after each query, and I don't show how the queries were ran 5 times with the
high and low value thrown out. In a real scenario, where you're not just trying to measure select performance, the parallel hint should be moved into the insert statement.
