CODEWITHSUNDEEP
sqloracle-database
Meng
Meng

Reputation: 1188

Oracle SQL in clause - slow in speed

Query 1 below is really slow in speed. 

select * from my_table
where col1 in (
    ... multiple complicated joins ...
);

Suppose the (multiple complicated joins) returns 1,2,...,50 and it runs fast.

  ... multiple complicated joins ...

By replacing the join by its output, Query2 below also runs much faster.

select * from my_table
where col1 in (
    1,2,...,50
);

Is there a way to insert the output of the (multiple complicated joins) into a list-like variable?

select * from my_table
where col1 in &col1_list;

my_table: 2G table and col1 is not indexed

multiple complicated joins: returns around 50 values within 1 seconds

Query 1: running time = 5 mins

Query 2: running time = 2 seconds

Note: I only have read permission to the database, so i cannot create any temporary tables.

Upvotes: 2

Views: 2528

Answers (1)

D.J.
D.J.

Reputation: 386

How about to join your complex joins.

SELECT * FROM MY_TABLE mt JOIN (... multiple complicated joins ...)temp on temp.col = mt.col1

Upvotes: 1

Related Questions