Question: I have a query which takes around 2 seconds to load:

```
SELECT OUTPUT_VAL.NEXTVAL VAR1_R_ID,A.R_ID,A.VAR1,A.SEQU,A.OUTPUT,B.VAR1 DATATYPE_VAR1 
FROM
( 
    SELECT A.R_ID,A.VAR1,A.SEQU,A.OUTPUT,B.D_TYPE
    FROM
    (
        select A.R_ID, 2484 VAR1,1 SEQU, A.USER OUTPUT 
        from R_TB_1 A 
        WHERE A.R_ID BETWEEN 2457854437 AND 2458854437
        union all
        select A.R_ID, A.MEM_VAR1 VAR1,1 SEQU, MEM_OUTPUT OUTPUT 
        from R_TB_1 A 
        WHERE A.R_ID BETWEEN 2457854437 AND 2458854437
    ) A 
    LEFT JOIN VAR1_TABLE B 
        ON A.VAR1=B.VAR1
) A 
LEFT JOIN VAR1_TABLE B 
    ON A.D_TYPE=B.VAR1_NAME;

```

How can I rewrite it to improve performance?

Answer: 
So, you have a table `VAR1_TABLE` with at least three columns, `VAR1`, `D_TYPE` and `VAR1_NAME`... you select rows that have a `VAR1` column from another table, then you join with `VAR1_TABLE` on `MEM_VAR1 = VAR1`, and then you join again on `D_TYPE = VAR1_NAME`? Can you explain this part, since it makes no sense to me... why are you comparing `D_TYPE` to `VAR1_NAME`? Just because you can do it and the query runs without errors doesn't mean it makes sense.

Assuming the table R\_TB\_1 has many rows (you seem to be selecting 100,000 rows which is a small portion of the whole table), the `UNION ALL` means the table is scanned twice. You may be better off selecting just once, in a CTE, and then doing the union based on the CTE... if your version is at least Oracle 11.1. (By the way, state your Oracle version whenever you ask a question!) If you are on Oracle 10 or below, you will need subqueries like you have now.

Something like this:

```
with 
     Z ( R_ID, MEM_VAR1, USER, MEM_OUTPUT ) as (
       select R_ID, MEM_VAR1, USER, MEM_OUTPUT
       from   R_TB_1
       where  R_ID between 2457854437 and 2458854437
     ),
     A ( R_ID, VAR1, SEQU, OUTPUT ) as (
       select R_ID, 2484    , 1, USER
         from Z
       union all
       select R_ID, MEM_VAR1, 1, MEM_OUTPUT
         from Z 
     )
select  -- your joins from A to the other table here; A is defined in the WITH clause

```
```
WITH temp AS (
SELECT OUTPUT_VAL.NEXTVAL VAR1_R_ID,A.R_ID,A.VAR1,A.SEQU,A.OUTPUT,B.VAR1 DATATYPE_VAR1 FROM

( 
SELECT A.R_ID,A.VAR1,A.SEQU,A.OUTPUT,B.D_TYPE FROM
(
SELECT A.R_ID, 2484 VAR1,1 SEQU, A.USER OUTPUT from R_TB_1 A WHERE A.R_ID BETWEEN 2457854437 AND 2458854437

union ALL

select A.R_ID, A.MEM_VAR1 VAR1,1 SEQU, MEM_OUTPUT OUTPUT from R_TB_1 A WHERE A.R_ID BETWEEN 2457854437 AND 2458854437
)
A LEFT JOIN VAR1_TABLE B ON A.VAR1=B.VAR1 ) 
A LEFT JOIN VAR1_TABLE B ON A.D_TYPE=B.VAR1_NAME
)
SELECT * FROM temp

```