EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

Oracle SQL select within select

Database type:

So I have reason identified as some kind of ID in table1. I need to write query which writes out the belonging meaning of a reason from table 2. I hope my bad english was enough to explain my problem.

table1

    id  reason
  --------------
    1     1
    2     2
    3     1

table2

domain         value  meaning
------------------------------
table1.reason    1    example1
table1.reason    2    example2

I tried this select but i got error "ORA-01427: single-row subquery returns more than one row" but if I add max() on t2.meaning it only returns me meaning example2 because max value = 2

select 
    t1.id
    (select t2.meaning
    from table2 t2, table1 t1
    where t2.value = t1.reason
    and t2.domain = 'table1.reason') as reason
from table1;

How can i solve this problem?

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX table1_idx_reason ON table1 (reason);
CREATE INDEX table2_idx_domain_value ON table2 (domain,value);

The optimized query:

SELECT
        t1.id(SELECT
            t2.meaning 
        FROM
            table2 t2,
            table1 t1 
        WHERE
            t2.value = t1.reason 
            AND t2.domain = 'table1.reason') AS reason 
    FROM
        table1

Question 2

EverSQL Database Performance Knowledge Base

Oracle SQL select within select

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles