CODEWITHSUNDEEP
sqloracle-database
Rami Del Toro
Rami Del Toro

Reputation: 1150

Query database table in Oracle on columns that are not indexed

There is a database table in Oracle with a large amount of rows. When querying for a row two non-indexed columns are used. The query may take about 30 seconds to return.

Users send in a List of data-pairs so the query will use the first pair of data, plug it in the query and execute. To go through all data, the time to complete is quite long.

Example : if there are 20 data-pairs the time to complete will be 20 data-pairs*30 seconds = 600 seconds.

Can anyone recommend an approach that can reduce the amount of time to complete querying? If possible please post an example SQL.

Thank you

Upvotes: 0

Views: 1416

Answers (2)

Jon Heller
Jon Heller

Reputation: 36808

Use an expression list:

select *
from table1
where (a, b) in ((1, 2), (3, 4) /*add more pairs here if necessary*/)

This is faster than the temporary table method, since no data needs to be inserted. But it may require a little more code on the front end, to build a different query depending on the number of expressions.

Upvotes: 1

eugenioy
eugenioy

Reputation: 12383

You can substantially reduce the time by issuing just one query in which you get all rows matching any of the 20 pairs.

In that way, you'll have to scan the table just once instead of 20 times.

I would guess the query combining the 20 conditions will take 30 seconds, same as each individual query.

For example, you could create a temporary table:

CREATE GLOBAL TEMPORARY TABLE temp_pairs
(
field1 int,
field2 int
)
ON COMMIT DELETE ROWS;

Then insert the pairs you receive:

insert into temp_pairs values (1,2);
insert into temp_pairs values (5,6);

Then query the original table with an inner join with the temp_pairs:

select t1.* from the_table t1
inner join temp_pairs t2
on t1.field1 = t2.field1
and t1.field2 = t2.field2;

Upvotes: 2

Related Questions