CODEWITHSUNDEEP
sqloracle-database
wggbullet
wggbullet

Reputation: 31

join taking too long

I have a query that takes too long to run and I think is not normal. I am guessing some hints/optimizer would help, but I do not know much about it. So any help would be appreciated.

Basically the code is:

with datasetA as (),
     datasetB as (),
     datasetC as ()
select a.*
from datasetA a
     join datasetB b on b.key = a.key
     join datasetC c on c.key = a.key;

Each of the subquery (datasetA, B, C) takes only 1 second to run, but the join takes 30 minutes.... I am 100% sure it is a 1 to 1 join, and the datasets are small (like 1000 rows) and final output is small too.

Upvotes: 1

Views: 9934

Answers (3)

Jon Heller
Jon Heller

Reputation: 36912

Oracle is probably applying a bad optimizer transformation, turning three queries that run fast independently into one query that runs slow. The simplest way to disable those transformations, and ensure that each query runs by itself the fast way, is to add a seemingly worthless ROWNUM condition, like this:

with datasetA as (... where rownum >= 1),
     datasetB as (... where rownum >= 1),
     datasetC as (... where rownum >= 1)
select a.*
from datasetA a
     join datasetB b on b.key = a.key
     join datasetC c on c.key = a.key;

The ROWNUM pseudo-column was intended for top-N reporting, which wouldn't work if Oracle rewrote each query. So the optimizer leaves those subqueries alone, even though the condition is logically redundant.

Above is the easy way to solve the immediate problem, but not necessarily the best way. You might want to investigate why Oracle is choosing bad transformations; bad optimizer statistics are often the culprit. Finding the root cause may help fix other problems, but can be a difficult process.

If you're curious why Oracle is doing this, optimizer transformations are often a good thing. Transformations like view merging and predicate pushing allow Oracle to apply knowledge from one part of the query to another, which often significantly improves the run time. For example, imagine a query like select * from (select * from huge_table) where primary_key = 1;. We'd certainly want that predicate to be pushed into the subquery, so Oracle doesn't have to read the whole table just to get one row.

Upvotes: 1

Standin.Wolf
Standin.Wolf

Reputation: 1234

You are likely missing indexes

Follow the below link and it should help you. It should reduce the total execution time of the query by at least 100x

https://www.w3schools.com/sql/sql_create_index.asp

Also, if you could post results from SHOW INDEX FROM table1, SHOW INDEX from table2 we could identify missing indexes

Upvotes: 0

Megh
Megh

Reputation: 160

you need to add index on join foreign key column on all tables which will improve performance.

Upvotes: 0

Related Questions