How can I make this query perform faster within Oracle 10g limitations?

Question

I have an Oracle query that is performing horrendously and could do with some suggestions as to what could be the cause and/or suggestions on how to improve it. I have detailed below a simplified version of my original query and what I have tried.

Original Query

Select * From
(
SELECT * FROM table1
Union All
SELECT * FROM table2
Union All
SELECT * FROM table3
Union All
SELECT * FROM table4
) GroupedData
LEFT JOIN
(
SELECT * FROM RecursiveCte
) ON GroupedData.id = RecursiveCte.id

I have simplified the queries to generic "select all" statements just for ease of this question.

A couple of points on some of the queries...

The GroupedData subquery is actually more than 4 unions, each one varies in the volume of data it is looking at but is limited in the data returned by date filters. The total data returned from this query is usually 1500 records, although the volume of data being processed could be hundreds of thousands of records. If I run this query on its own, it takes less than a second to return those 1500 rows.

The RecursiveCte subquery makes use of the CONNECT BY functionality as Oracle 10g doesn't have the recursive CTE (which would be so much easier). If I run this query on its own, it also takes less than a second.

The problem comes when I try and join the two together via a LEFT JOIN. When I do this, the query takes over 8 minutes to run for the same date range parameters.

I have tried setting these up in the following CTE formats but they all perform worse!

Method #1

WITH GroupedData AS
    (
    SELECT * FROM table1
    Union All
    SELECT * FROM table2
    Union All
    SELECT * FROM table3
    Union All
    SELECT * FROM table4
    ) GroupedData,

    RecursiveCte AS
    (
    SELECT * FROM RecursiveCte
    )

Select * From
    GroupedData
    LEFT JOIN RecursiveCte ON GroupedData.id = RecursiveCte.id

Method #2

WITH Query1 AS
    (SELECT * FROM table1),

    Query2 AS
    (SELECT * FROM table2),

    Query3 AS
    (SELECT * FROM table3),

    Query4 AS
    (SELECT * FROM table4),

    RecursiveCte AS
    (
    SELECT * FROM RecursiveCte
    )

Select * From
    (
    Select * From Query1
    Union All
    Select * From Query2
    Union All
    Select * From Query3
    Union All
    Select * From Query4
    ) GroupedData
    LEFT JOIN RecursiveCte ON GroupedData.id = RecursiveCte.id

On top of the limitations of Oracle 10g, I am also running with a database user with readonly permission which limits what I can do within the database.

Any help is very much appreciated, and sorry in advance if I have not provided enough context!

Thanks

Answer 1

When you have two queries that run fast separately, but run slowly together, the easiest solution is usually to add a ROWNUM like below:

Select * From
(
  ...
  --Prevent optimizer transformations to improve performance:
  WHERE ROWNUM >= 1
) GroupedData
LEFT JOIN
(
  SELECT * FROM RecursiveCte
  --Prevent optimizer transformations to improve performance:
  WHERE ROWNUM >= 1
) ON GroupedData.id = RecursiveCte.id

See my answer here for a more detailed explanation of why this trick works.

While the above trick is often the easiest solution, it's usually not the best solution. There's always a reason why Oracle is re-writing your query poorly; maybe table statistics are missing, or the conditions are too complicated for Oracle to estimate the number of rows returned, etc. But if you don't want to spend hours investigating SQL Monitoring reports right now, it's OK to take a shortcut.